Search code examples
mysqlunix-timestamp

Upload dates in unix timestamp format . Mysql DB

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?

Solution

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