MySQL – Convert string with timezone into Date

November 18, 2022

I would like to convert strings such as "Tue, 15 May 2012 17:26:44 EST" into UTC dates, so that I can then convert them into UNIX timestamps.

I tried the following but can’t see a parameter for timezones in the MySQL documentation

SELECT STR_TO_DATE("Tue, 15 May 2012 17:26:44 EST", "%a, %d-%b-%Y %T");

I think I can also use COVERT_TZ() but the records could be any timezone so I’m not sure how to to determine the parameters.

>Solution :

Using CONVERT_TZ you can get the last word from your string (which hopefully is always the time zone) and convert it to UTC:

SELECT 
  CONVERT_TZ(
    /*date:*/
    STR_TO_DATE('Tue, 15 May 2012 17:26:44 EST', '%a, %d %b %Y %T'),
    /*from_tz: select the last word, EST in this case:*/
    SUBSTRING_INDEX(TRIM('Tue, 15 May 2012 17:26:44 EST'), ' ', -1),
    /*to_tz:*/
    'UTC')