Question 22212 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL – Convert string with timezone into Date

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.

Tags:

2

Answers


  1. 0

    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')
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search