mysql - 如何将日期时间从任何区域转换为mysql中的特定区域

Question

我有一个 mySQL 表，其中记录了各个不同时区的日期。在 mySQL 中执行 SELECT 语句时，如何将 DateTime 值转换为查看用户 TimeZone？

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

问题是接受“MessageDate”字段中记录的日期中的任何时区，如下所示

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

非常感谢

丹尼斯

Answer 1

您不需要这样做。 MySQL 将为您执行时区转换，前提是您将时间存储在 TIMESTAMP 类型列中，并且正确设置了 session time_zone 变量。如 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.