CODEWITHSUNDEEP
phpmysqldatetimetimezone
Ghanshyam Katriya
Ghanshyam Katriya

Reputation: 1081

Not able to convert GMT timezone to PDT using CONVERT_TZ function in mysql

I am not able to convert GMT timezone to PDT using CONVERT_TZ function in mysql while I am able to convert GMT/UTC timezone to MET/GMT timezone.

MySQL Query :

SELECT CONVERT_TZ('2004-01-01 12:00:00','UTC','Asia/Jakarta') AS time;

+---------------------+
| time                |
+---------------------+
| 2004-01-01 19:00:00 |
+---------------------+

SELECT CONVERT_TZ('2015-10-21 12:50:00','GMT','PDT') AS time;

+------+
| time |
+------+
| NULL |
+------+

Thanks in advance.

Upvotes: 1

Views: 1826

Answers (2)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241603

You should not use three-letter time zone abbreviations. While a few are supported, in general there are many ambiguities. For example, CST has five different interpretations. You can review the list on Wikipedia for more examples of conflicts.

Additionally, time zone abbreviations generally refer to only a portion of the time zone. For example, Pacific time uses PST in the winter, and PDT in the summer. Some of the entries in the time zone database attempt to account for this (using PDT rules for daylight time when PST is specified), but others are strictly fixed offsets and do not switch. Making sense of which ones are which is futile.

You should also not use time zones like PST8PDT, as these are primarily there for backwards compatibility with older POSIX standards. The problem with this style of time zones is that they don't account for any history of changes. Time zones change their rules over time, such as when the US changed its DST rules in 2007, or when Russia changed its standard offsets in 2014, etc. If you use the POSIX format, your system can only account for one set of rules.

You should only use time zones that are location based IANA tz identifiers. Pacific time is America/Los_Angeles. Central time is America/Chicago. etc. Refer to the list on Wikipedia for other examples.

Upvotes: 1

RiggsFolly
RiggsFolly

Reputation: 94672

I just noticed that there is a PST8PDT timezone name, is that what you are looking for?

SELECT CONVERT_TZ('2015-10-21 12:50:00','GMT','PST8PDT') AS time;

Upvotes: 2

Related Questions