Upload dates in unix timestamp format . Mysql DB

Question

I have data with date format 1577234966837.

I uploaded this data in table via command :

load data infile 'C:/file.tsv' 
into table table_1 
fields terminated by '\t' 
lines terminated by'\n' 
ignore 1 lines  (value, @timestamp)  
set timestamp = FROM_UNIXTIME(@timestamp);

Command successful, but value in column timestamp is null. Ho to upload this format?

Please provide your table structure, desired and exist result — Slava Rozhnev
– Slava Rozhnev, Commented Mar 19, 2020 at 21:06
Table: create table table_1 (value varchar (10), timestamp datetime). Desired result value ; 2019-12-25 05:49:26.837. Exist result value; null. — Riot
– Riot, Commented Mar 20, 2020 at 15:58

Slava Rozhnev · Accepted Answer · 2020-03-20 16:23:22Z

2

Your code looks right, but have problem with data type convertation.

In MySQL unixtime is number of second from 1970-01-01 00:00:00.

In your case number looks as JavaScript time in milliseconds, so for right convertion you should to divide the number by 1000

select from_unixtime(1577234966837); -- result is NULL
select from_unixtime(1577234966837/1000); -- result 2019-12-25 00:49:26.8370

DB fiddle link

So right import command should be like:

load data infile 'C:/file.tsv' 
into table table_1 
fields terminated by '\t' 
lines terminated by'\n' 
ignore 1 lines  (value, @timestamp)  
set timestamp = FROM_UNIXTIME(@timestamp/1000);

answered Mar 20, 2020 at 16:23

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Upload dates in unix timestamp format . Mysql DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related