Convert MySQL datetime to timestamp

Question

I am trying to convert datetime into timestamp but mysql is giving me warnings and also converted values are wrong. Here is SQL query

UPDATE table1 A, table2 B SET B.date_added=UNIX_TIMESTAMP(STR_TO_DATE(A.date_added, '%M %d %Y %h:%i%p')) WHERE A.id=B.id;

Warnings

+---------+------+--------------------------------------------------------------------------+
| Level   | Code | Message                                                                  |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '2011-06-11 20:29:02' for function str_to_date |
+---------+------+--------------------------------------------------------------------------+

Result

+---------------------+---------------------+
| date_added          | date_added          |
+---------------------+---------------------+
| 2012-02-23 06:12:45 | 2012-12-23 19:08:33 |
+---------------------+---------------------+

I also tried following query but it shows 0000-00-00 00:00:00 in timestamp field.

UPDATE table1 A, table2 B SET B.date_added=UNIX_TIMESTAMP(A.date_added) WHERE A.id=B.id;

score 42 · Answer 1 · answered Jan 15 '15 at 15:05

42

UPDATE table1 A, table2 B SET B.date_added=UNIX_TIMESTAMP(A.date_added) WHERE A.id=B.id;

UNIX_TIMESTAMP('2015-01-15 12:00:00'); is sufficient to convert a mysql datetime to a Timestamp.

answered Jan 15 '15 at 15:05

Omar

8,374
8
39
50

score 21 · Accepted Answer · edited May 23 '17 at 11:54

21

Try this please:

UPDATE table1 A, table2 B 
SET B.date_added = FROM_UNIXTIME(A.date_added) 
WHERE A.id=B.id

Reference. It seems like you have an issue with the way you format date stammp. Also please look into this post: Should I use field 'datetime' or 'timestamp'?

edited May 23 '17 at 11:54

Community

1
1

answered Dec 23 '12 at 21:00

bonCodigo

14,268
1
48
91

I tried that as well, but why there is time diff table A `2012-02-23 06:12:45` table B `2012-12-23 21:01:57` – Maximus Dec 23 '12 at 21:11
There is some weird stuff with time zones and FROM_UNIXTIME, see http://stackoverflow.com/a/17128732/20774 for an explanation – James McMahon Jun 18 '13 at 13:10

Andrea · Answer 3 · 2016-02-17T08:43:15.493

7

On MySQL 5.5 this works fine:

UPDATE `some_table`
SET `timestamp_col` = `datetime_col`

with datetime_col of type DATETIME and timestamp_col of type TIMESTAMP.

That is: no needs of explicit type conversion.

edited Feb 17 '16 at 08:43

answered Feb 13 '16 at 18:37

Andrea

15,900
18
65
84

oddly, sometimes this gives an error. Might be timezone dependent. – Garr Godfrey May 14 '22 at 01:30
Set timezone to UTC and this runs without error, otherwise you may have datetimes that don't convert to a timestamp in the current time zone. – Garr Godfrey May 14 '22 at 01:31

score 2 · Answer 4 · answered Dec 23 '12 at 21:06

You're using '%M %d %Y %h:%i%p' as the date format, but the warning shows a date string '2011-06-11 20:29:02', which doesn't match that format. I.e., the year is first, and the time is in 24-hour format.

You could use '%Y-%M-%d %T' instead, that format would match the data.

See more on the date format codes here: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Convert MySQL datetime to timestamp

4 Answers4

Linked