| Bug #101561 | @@session.time_zone cannot be used as as source tz in CONVERT_TZ | ||
|---|---|---|---|
| Submitted: | 11 Nov 2020 9:58 | Modified: | 11 Nov 2020 13:13 |
| Reporter: | Bernhard Wendel | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 8.0.21-12 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[11 Nov 2020 13:13]
MySQL Verification Team
Hi Mr. Wendel, Thank you for your bug report. We have analysed all changes between 8.0.19 and 8.0.22 and found a bug #83852. It has been fixed to improve the conversion in the function that you are mentioning. Hence, the behaviour has changed, but current calculations are the correct ones. Not a bug.
Description: We use the following query to convert a timestamp to UTC: SELECT CONVERT_TZ(started, @@session.time_zone, 'UTC') AS started FROM <my_table> WHERE <condition> This worked well with 8.0.19-10. Out setup that has the time_zone variables set to the following: @@session.time_zone = SYSTEM @@global.time_zone = SYSTEM And the underlying system (ubuntu) has the timezone set to Europe/Berlin. Starting with 8.0.21-12 the timezone conversion returns a different result: There is no longer a timezone conversion. The value from the database is returned as being UTC. This seems to be a bug in how @@session.time_zone is handled. When using "SYSTEM" or @@global.time_zone, then the conversion happens as expected. Since @@session.time_zone is used for reading the timestamp field, one cannot simply switch to one of the mentioned alternatives that work. We now have to check that @@session.time_zone is set to the correct value before attemting to read these values How to repeat: CREATE TABLE my_table ( started timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ); Insert some values and execute the query from the description. As long as you are not using UTC as your timezone, the result should be different from the data in the table.