Converting date/time string to unix timestamp in MySQL

Question

We have a database with all the dates and times stored in one column called timestamp. The format of the date/time in the column "timestamp" is as: 03 Aug 08:10am.

I would like to convert this (03 Aug 08:10am) to UNIX TIMESTAMP in MySQL and not PHP because we already have over 500 rows with this format: 03 Aug 08:10am.

I tried create a new INT column called new_timestamp and ran this query:

UPDATE table_name SET new_timestamp = UNIX_TIMESTAMP(timestamp);

However, it shows that 0 rows were affected.

This is not a duplicate, don't redirect me to how to convert in PHP. Read the question first :)

Possible alternate solution. TBH, I don't know how it can be done (time stuff baffles me a bit), yet once you have converted those, you could copy that table and use the right/proper type for the timestamp column (adding a column and deleting the other after), therefore avoiding this problem entirely and for the future. Is a unix timestamp that important for future querying? — Funk Forty Niner
UPDATE table_name SET new_timestamp = UNIX_TIMESTAMP(STR_TO_DATE(CONCAT('2016-', timestamp), '%Y-%d %b %h:%i%p')); — Mark Baker
Mohamed, make sure you make a copy of your table first, in order to test what @MarkBaker posted in a comment for you to try/use. If it works, then apply it to your working table. If it does work, you could ask Mark to post it as an answer in order to close the question as solved. It looks promising. — Funk Forty Niner

Mark Baker Mark Baker · Accepted Answer · 2016-08-12T12:30:51

The UNIX_TIMESTAMP() function requires a valid date/time format to convert correctly, so you need to convert your existing date/time format to a valid/recognised format (including the year) first. You can do this using MySQL's STR_TO_DATE() function, telling it what format you are passing in, and concatenating in a hard-coded year value as it's always 2016 in your case.

STR_TO_DATE(CONCAT('2016-', <your date/time value>), '%Y-%d %b %h:%i%p')

You can then use the UNIX_TIMESTAMP() function to convert that valid date to your unix timestamp and update all those records in a single step:

UPDATE table_name
   SET new_timestamp = 
       UNIX_TIMESTAMP(STR_TO_DATE(CONCAT('2016-', timestamp), '%Y-%d %b %h:%i%p'));

Converting date/time string to unix timestamp in MySQL

1 Answers