mysqlsqldatetimeamazonsellercentral

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

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!

Solution

  • 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