0

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 :)

Ivan
  • 34,531
  • 8
  • 55
  • 100
Mohamed Ebrahim
  • 232
  • 1
  • 3
  • 13
  • Are these all 2016 (current year) dates? – Mark Baker Aug 11 '16 at 12:31
  • @Festo, sorry about that, removed. – Mohamed Ebrahim Aug 11 '16 at 12:31
  • @MarkBaker Yes, all 2016 dates. – Mohamed Ebrahim Aug 11 '16 at 12:32
  • 1
    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 Aug 11 '16 at 12:37
  • @Fred-ii- Yeah, converting to timestamp is important at the moment and much more reliable than dates without the year. I'll have to push a future update that will require some calculations with UNIX TIMESTAMP. – Mohamed Ebrahim Aug 11 '16 at 12:47
  • You do know that you can always convert to UNIX time via PHP later on, or maybe even through MySQL. The important thing here, is to use MySQL's built-in date/time functions which make it much easier to query all around. – Funk Forty Niner Aug 11 '16 at 12:49
  • 1
    `UPDATE table_name SET new_timestamp = UNIX_TIMESTAMP(STR_TO_DATE(CONCAT('2016-', timestamp), '%Y-%d %b %h:%i%p'));` – Mark Baker Aug 11 '16 at 13:31
  • 1
    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 Aug 11 '16 at 13:40
  • @MarkBaker This works perfectly! Thank you! Put it in an answer and I'll choose it as the correct answer :) – Mohamed Ebrahim Aug 12 '16 at 12:13

1 Answers1

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'));
Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • 1
    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. – Funk Forty Niner Aug 12 '16 at 15:08
  • @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 – Mark Baker Aug 12 '16 at 17:51
  • 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?* ;-) – Funk Forty Niner Aug 12 '16 at 17:53