How to upload timestamp data from a file into mysql

Question

shell> cat /data/mysql/tm.sql
1276609796
1276606325

mysql> CREATE TABLE `tm_table` ( f TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected

mysql> LOAD DATA INFILE '/data/mysql/tm.sql' INTO TABLE tm_table;
Query OK, 2 rows affected
Records: 2  Deleted: 0  Skipped: 0  Warnings: 2

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'f' at row 1 |
| Warning | 1265 | Data truncated for column 'f' at row 2 |
+---------+------+----------------------------------------+
2 rows in set

mysql> SELECT f FROM tm_table;
+---------------------+
| f                   |
+---------------------+
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
2 rows in set

I prefer to keep date in the timestamp format. How to upload timestamp data from a file into mysql?

Dmitry · Accepted Answer · 2010-06-20 21:41:19Z

4

Solution found. Thank the_void for the idea.

mysql> LOAD DATA INFILE '/data/mysql/tm.sql' INTO TABLE tm_table
    -> (@var1) SET f=FROM_UNIXTIME(@var1);
Query OK, 2 rows affected
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select f from tm_table;
+---------------------+
| f                   |
+---------------------+
| 2010-06-15 16:52:05 |
| 2010-06-15 17:49:56 |
+---------------------+
2 rows in set

edited Jun 20, 2010 at 21:41

answered Jun 20, 2010 at 21:36

Dmitry

611 silver badge8 bronze badges

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

Comments

the_void · Accepted Answer · 2010-06-19 17:52:47Z

0

You should use the FROM_UNIXTIME function, like this:

#!/bin/bash

FILENAME=$1
TEMP=temp.txt

for LINE in $(cat $FILENAME); do
    echo "INSERT INTO tm_table(f) VALUES(FROM_UNIXTIME(`echo $LINE | tr -d '\r\n'`));" >> $TEMP
done

mysql -u XXX --password=XXX < $TEMP

rm $TEMP

answered Jun 19, 2010 at 17:52

the_void

5,5602 gold badges31 silver badges35 bronze badges

1 Comment

Dmitry Over a year ago

Insert rows one after another does not fit .. trying to solve task with the help of 'LOAD DATA INFILE' function.

Collectives™ on Stack Overflow

How to upload timestamp data from a file into mysql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related