On Mysql/Sqlite, db.models.functions.Now uses wrong timezone

[Matthijs Kooijman]

On Mysql and Sqlite (maybe also Oracle, cannot test that), the db.models.functions.Now() seems to get the current timezone wrong.

• On Mysql, Now() / CURRENT_TIMESTAMP produces a datetime in the Mysql session timezone (which defaults to the system timezone).
• On Sqlite, Now() / CURRENT_TIMESTAMP produces a datetime in UTC.

However, if USE_TZ is set, all datetimes stored in the database use the per-database configured TIME_ZONE (defaulting to UTC), resulting in a potential timezone mismatch. In particular, this means that timezone.now() and django.db.models.functions.Now() are not equal.

I realize that Mysql, Sqlite and Oracle are documented to not support timezones, but that should only mean that they do not explicitly *store* timezone information along with datetime fields and that keeping timezones consistent relies on external configuration instead. Given that, I think that Now() should still behave as expected on all databases.

Testcase showing the problem

I've created a draft pullrequest with a testcase to show this problem here: ​https://github.com/django/django/pull/12991

With that testcase, on my system (mariadb 5.5.5-10.3.22-MariaDB-0ubuntu0.19.10.1, CEST/UTC+0200 timezone), I see that:

• With sqlite and USE_TZ=False: fails because Now() is in UTC and Django uses its configured TIME_ZONE (America/Chicago).
• With sqlite and USE_TZ=True: works, since Now() is in UTC and Django defaults to UTC.
• With sqlite and USE_TZ=True and database TIME_ZONE=Europe/Amsterdam: fails, since Now() is in UTC and Django now uses Europe/Amsterdam (for this, I set TIME_ZONE in the DATABASES of my settings manually, not sure if I could do that inside a testcase).
• With Mysql and USE_TZ=True: fails, since Mysql uses the system timezone (UTC+0200) and Django uses UTC.
• With Mysql and USE_TZ=False: fails, since Mysql uses the system timezone (UTC+0200) and Django uses its configured TIME_ZONE (America/Chicago).
• With Postgresql, all tests succeed.

See the pullrequest for detailed test output, including some debug prints that help confirm all of the above observations.

Potential fix: Setting the session timezone

For MySQL, you can change the session timezone (e.g. connection timezone). Setting this to the configured database TIME_ZONE would make sense and solve this problem neatly, by letting MySQL know about the timezone used for data. This should not affect datetime fields themselves (which are still just read and written as-is), but does affect functions like CURRENT_TIMESTAMP(). It also changes the way timestamp fields are interpreted (which are stored as UTC but read and written in the session timezone), which actually seems like a good thing (this would fix #19312).

This seems like the cleanest approach, but maybe there is a compelling reason for not setting the session timezone (otherwise, I might think it would have been set already to fix #19312?).

For details, see ​https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html. In particular:

The 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 session time zone to UTC for storage, and from UTC to the session time zone for retrieval.

The session 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.

This fix does not work for Sqlite, which does not seem to have any concept of session timezone (there is the concept of 'localtime', but I'm not sure how configurable that is).

Also see ​https://dev.mysql.com/doc/refman/8.0/en/datetime.html that has some examples on how datetime and timestamp are interpreted differently, and also documents that Mysql 8.0.19 and above allow adding a timezone offset to datetime literals (which is not stored, but used to convert the literal to the session timezone before storing).

Potential fix: converting in Now()

Alternatively, this could be fixed just for Now(), by converting the result of CURRENT_TIMESTAMP() to the expected timezone (e.g. per-database TIME_ZONE). For Sqlite, this is probably the only option, but it is also easier, since you can just convert from UTC to whatever timezone needed (I *think*, I could not find any documentation for CURRENT_TIMESTAMP() for Sqlite). For Mysql, you would need to figure out the session timezone (unless there is a way to convert timezones *from* the session timezone implicitly somehow).

The downside of this option is that any other functions (added later or in raw queries) will still be wrong.

Potential fix: Document as limitation

Not really satisfactory, but I guess django.db.models.functions.Now could document this limitation and recommend to use timezone.now() instead (which I think could be used everywhere Now() could be used, except maybe as a default value, and produce nearly identical results).

Conclusion

For now, I'm going to switch to using timezone.now(), which I think should fix my immediate problem, but it would be nice if this could work as expected out of the box. As for applying a fix, I do not think I am familiar enough with the db code to really tell what would be the best solution or implement it (nor will I have the time for it, I'm afraid).