how to convert datetime from any zone to a specific zone in mysql

Question

I have a mySQL table with a date recorded from various different Time Zones. How can I convert the DateTime value to the viewing user TimeZone while doing a SELECT statement in mySQL ?

    SELECT ClientID,
        convert_tz(MessageDate,???,???)
        MeetingType,
        MeetingDate,
        Comments,
        CompanyName
    FROM clientCompNotes
    WHERE ClID = 970392281 AND CompanyID=411972145

The problem is to accept any timezone from the date recorded in the "MessageDate" field, which looks like this

 Wed May 30 2012 12:51:02 GMT-0400 (Eastern Daylight Time)

Many thanks

Dennis

score 1 · Answer 1 · answered May 30 '12 at 17:21

1

CONVERT_TZ(dt,from_tz,to_tz)

The from_tz is the timezone in which the time currently is in and to_tz is the one in which you want to convert it to.

answered May 30 '12 at 17:21

JHS

7,761
2
29
53

That's not what I asked. The timezone is already recorded in the DatTime and I don't want to have to manipulate it. I want to do a direct convert from ANY zone recorded in the field itself... Look at the GMT-0400 in the example – DKean May 30 '12 at 17:25
But I have to estimate which "from_tz" to enter into the second parameter. And I want to let the function guess which tz the datetime is without my help, @Juniad. – DKean May 30 '12 at 17:40
I get a NULL back as result with CONVERT_TZ(MessageDate,'UTC','GMT') and any other incarnation! And this is the way it is stored: Wed May 30 2012 12:51:02 GMT-0400 (Eastern Daylight Time) – DKean May 30 '12 at 17:57

score 1 · Accepted Answer · answered May 30 '12 at 18:03

You shouldn't need to do this. MySQL will perform timezone conversion for you, provided that you store your times in TIMESTAMP type columns and that you set the session time_zone variable appropriately. As stated in the manual:

The MySQL server maintains several time zone settings:

[...]
Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:
mysql> SET time_zone = timezone;
The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values. If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.

how to convert datetime from any zone to a specific zone in mysql

2 Answers2