Re: Timezone

From: Lew Pitcher <lew.pitcher_at_digitalfreehold.ca>
Date: Sun, 04 Nov 2018 12:10:53 -0500
Message-ID: <prn96u$sg1$1_at_dont-email.me>

bill wrote:

> On 11/4/2018 10:02 AM, Lew Pitcher wrote:

>> bill wrote:
>>

>>> My server is on the West Coast.  My client is in the Eastern
>>> timezone.  Is there any way to have a default timezone set to the
>>> Eastern timezone so that NOW() will return the client local time ?

>>
>> Sure - take a look at
>> https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html
>>
>> Your client can set their own timezone when they connect, and it will
>> only affect their own connection.
[snip]

>> I seem to have missed something

> When I try sql="set time_zone='EST5EDT'" I get "#1298 - Unknown
> or incorrect time zone: 'EST5EDT'
> same with EST, EASTERN, AMERICA/NEW_YORK with or without single
> quotes surrounding

[Quoted] Have you populated the MySQL time zone tables? The documentation says   "Several tables in the mysql system database exist to maintain time zone    information (see Section 5.3, “The mysql System Database”). The MySQL    installation procedure creates the time zone tables, but does not load    them." (from the above-mentioned MySQL reference manual webpage).

Check that web page, specifically at the heading "Populating the Time Zone Tables", for the details on how to populate the MySQL system time zone tables. It's as simple as running a supplied script against your existing OS timezone table directory and using the resulting sql to populate the tables.

Apparently, Oracle doesn't ship MySQL timezone table updates, and expect that you'll follow this process to install the timezone data yourself. The given (Unix/Linux) example is:
  mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Although I've not done it recently, commercial sites probably should do this as frequently as the OS updates the OS timezone tables, otherwise there's a chance that the MySQL timezone tables will get 'out-of-sync' with the real world.

HTH

-- 

Received on Sun Nov 04 2018 - 18:10:53 CET