How do I convert a non-standardized string datetime to MySQL datetime?

Question

Help?

When using Amazon Seller Central's API, each order returns a date in this format:

2018-09-01 06:40 PM PDT

I'd like to convert it to a MySQL-friendly format like:

2018-09-01 18:40:00

How can I update these values in my staging table with a MySQL query?

Thanks in advance!

Do you want to preserve the timezone, or convert it to UTC? – fubar Oct 15 '18 at 22:19 — fubar, Oct 15 '18 at 22:19

score 1 · Accepted Answer · answered Oct 15 '18 at 22:33

You can use STR_TO_DATE to convert the string to a valid MySQL date format.

SELECT STR_TO_DATE(SUBSTRING('2018-09-01 06:40 PM PDT', 1, 19), '%Y-%m-%d %l:%i %p') AS date;

If you want to convert the timezone too, you can use CONVERT_TZ.

SELECT 
CONVERT_TZ(
    STR_TO_DATE(SUBSTRING('2018-09-01 06:40 PM PDT', 1, 19), '%Y-%m-%d %l:%i %p'),
    'PST8PDT', -- Note: PDT is not a valid MySQL timezone
    'UTC'
) AS date

How do I convert a non-standardized string datetime to MySQL datetime?

1 Answers1