If a Ubuntu machine was in 'PST' timezone and on 10 march 2019 at 2:00 AM its timezone gets changed to 'PDT' timezone to accommodate DST changes then does this change is immediately reflected in SYSTEM_TIME_ZONE of mysql 5.7 server running on it i.e. will it also update the value from 'PST' to 'PDT'?

As per my observation it seems the answer is 'NO' but then how does mysql accommodates for DST changes because I can see it taking effect on 'timestamp' value being inserted.

Let me share example:

My Ubuntu machine was in 'PST' timezone and its current timezone is 'PDT' but mysql 5.7 server running on it still shows @@global.system_time_zone as 'PST'.

However, I also see that none of records having timestamp column have value b/w 2019-03-10 02:00:00 to 2019-03-10 02:59:59 i.e. a time shift of 1hr takes place as per DST.

To summarize, 1) are my observations correct? 2) If yes then is SYSTEM_TIME_ZONE not getting updated is a bug that should be fixed?

  • Not sure about mysql, but for Linux distributions you cannot use the short time zones to get automatic switching, you typically link to the zones named after the region to inherit its actual time switch schedule (and short names for the timezone) – eckes Mar 30 '19 at 16:10
  • My question is not about how to enable DST on underlying OS, that part works for me so ignore short TZ names used in question. The question is about how MySQL handles this TZ transition, as what I am observing is that though MySQL does sense TZ change from PST to PDT and acts as per new TZ (i.e. PDT) but its SYSTEM_TIME_ZONE value still remains same i.e. PST and if that is how it is then isn't it a bug and needs to be fixed? – sactiw Apr 1 '19 at 14:03
  • If your System timezone uses a region name and not a specific Timezone abrev and if your MySQL is configured to use the system timezone (SET GLOBAL time_zone=“SYSTEM“) or if you specify a region name as the MySQL timezone the DST change is detected automatically (however there might still be work you have to do to actually store timestamps with actual timezone. The easiest for sure is to only store UTC timestamps and remember the target timezone with each entry. (Also you might need to load the zoneinfo into MySQL) – eckes Apr 1 '19 at 18:32
  • 1
    I just came across this too. I'm not sure if there are any implications but a percona blog post seems to suggest there shouldn't be - percona.com/blog/2020/04/09/… – James May 19 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.