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, Mar 19 '20 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, Mar 20 '20 at 15:58

score 2 · Accepted Answer · answered Mar 20 '20 at 16:23

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);

Upload dates in unix timestamp format . Mysql DB

1 Answers1