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
– Funk Forty Niner, Commented Aug 11, 2016 at 12:37
UPDATE table_name SET new_timestamp = UNIX_TIMESTAMP(STR_TO_DATE(CONCAT('2016-', timestamp), '%Y-%d %b %h:%i%p')); — Mark Baker
– Mark Baker, Commented Aug 11, 2016 at 13:31
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
– Funk Forty Niner, Commented Aug 11, 2016 at 13:40

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

3

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

answered Aug 12, 2016 at 12:30

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

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

3 Comments

Funk Forty Niner Over a year ago

Glad to see you put one in. I revisited the question to see you've put it in. I for one have learned something here. Cheers Mark.

Mark Baker Over a year ago

@Fred-ii- As my rep gets closer to 150k, I'm starting to think of chasing that score.... not so good for my answers, seeing the rep as a game

Funk Forty Niner Over a year ago

hehe, yeah well rep's always nice when it's nearing a nice rounded number. I honestly didn't see my rep raise so fast... once it hit 50k I said to myself "50, already?" TIme does fly when you're having fun, eh? ;-)

Collectives™ on Stack Overflow

Converting date/time string to unix timestamp in MySQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related