MySQL - Convert string with timezone into Date-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

An alternative to VvdL answer:

select
  convert_tz(str_to_date(left(my_date, length(my_date) - 3),
             "%a, %d %b %Y %T"), right(my_date, 3), 'UTC')
             as new_date
from dates;

All you need is to do string manipulation, which you can do in many ways. The freaking " took my time to notice.

Fiddle: https://www.db-fiddle.com/f/2fH64kfq78D2f1aBekqRKQ/0