Convert MM/DD/YYYY HH:MM:SS to Unix Timestamp (SQL)

Question

I'm looking for a solution to convert MM/DD/YYYY HH:MM:SS stored dates, in a MySQL table, to Unix timestamps.

Your first problem is that the datetimes are stored as strings. — JJJ, Aug 14 '13 at 12:20
@ÁlvaroG.Vicario Unfortunately, I have no control of the data until it's imported - it's all generated from "ancient" technology. — , Aug 14 '13 at 12:54

score 1 · Accepted Answer · answered Aug 14 '13 at 12:26

1

Since your date is in an odd format you can convert it using STR_TO_DATE then use UNIX_TIMESTAMP.

SELECT UNIX_TIMESTAMP(STR_TO_DATE('08/14/2013 22:12:19','%m/%e/%Y %H:%i:%s'));

You may want to consider switching the stored dates to true dates as this will make using the MySQL date functions simpler.

answered Aug 14 '13 at 12:26

Jim

Unfortunately I have no control over the date format until it's imported into the database. What would you suggest as the most efficient solution to converting all of the dates in the table (several million), putting the valid dates into a neighbouring column? (or worst case over-writing the existing). – Aug 14 '13 at 12:44
@eversonjw I would put the dates into a new column and move over the existing, that way if anything goes wrong the old column will still be there. As for efficiency it's perhaps better to ask that as a new question so you can get the best answer. – Jim Aug 14 '13 at 12:53

1 Answers1