[Django] #31638: On Mysql/Sqlite, db.models.functions.Now uses wrong timezone
Django
-------------------------------------+-------------------------------------
Reporter: Matthijs | Owner: nobody
Kooijman |
Type: Bug | Status: new
Component: Database | Version: master
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
=== 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).
=== 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`).
See the pullrequest for detailed test output, including some debug prints
that help confirm all of the above observations.
=== 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).
--
Ticket URL: <https://code.djangoproject.com/ticket/31638>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
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
=== 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).
--
--
Ticket URL: <https://code.djangoproject.com/ticket/31638#comment:1>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Adam (Chainz) Johnson (added)
* status: new => closed
* resolution: => needsinfo
Comment:
HI Matthijs. Thanks for the report. Interesting.
I'm going to close this as needsinfo for now. It's not at all clear to me
what the correct path is. As such it probably needs some discussion on the
DevelopersMailingList.
I'll cc Adam, who know MySQL/MariaDB well enough to speak sensibly.
A few observations:
> With sqlite and USE_TZ=True: works, since Now() is in UTC and Django
defaults to UTC.
This seems to usual case. That's the one I'd hope would work. (And with
USE_TZ=True I'm not expecting too much...)
So contrast with MySQL:
> With Mysql and USE_TZ=True: fails, since Mysql uses the system timezone
(UTC+0200) and Django uses UTC.
Is this not bad behaviour from MySQL, or lacking some config setting? This
is your "For MySQL, you can change the session timezone (e.g. connection
timezone)." yes?
This would seem the most likely avenue to me at the moment.
Then, I'm not too keen on making `Now` cleverer. The whole point of the
functions in db.models that they proxy down transparently to what the DB
provides.
> For now, I'm going to switch to using timezone.now(),
I see the intention in using the DB function, but my initial thought
looking at the test case in the PR was "why not timezone.now() there?".
--
Ticket URL: <https://code.djangoproject.com/ticket/31638#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Adam (Chainz) Johnson):
> On May 17, 2018, the official AWS Cloud Twitter account tweeted out an
article with the following caption, “Change the timezone of your Amazon
RDS instance to local time.” I hit the roof immediately and began ranting
about it and railing against that tweet in particular.
>
> I believe this is the first time that me yelling at AWS in public hit
semi-viral status. My comment, to be precise, was absolutely do not do
this. UTC is the proper server timezone unless you want an incredibly
complex problem after you scale. Fixing this specific problem has bought
consultants entire houses in San Francisco. Now, I stand by that criticism
and I maintain that your databases should be in UTC at all times, as
should the rest of your servers.
Corey Quinn - https://www.lastweekinaws.com/podcast/aws-morning-brief
/whiteboard-confessional-you-down-with-utc-yeah-you-know-me/
Like Corey, I strongly believe in only using UTC for all servers and data
at rest. The rest of that podcast (with transcript) shows why.
I think even adding system checks to Django to warn if you're using UTC
would be justified.
I'm not hopeful of any approach to trying to use the same timezone in both
the database and Django. Even if you get the same name, it can mean a
different offset. The timezone database changes whenever any government
feels like an adjustment and there's no good way of guaranteeing the
database (normally from the OS) has the same one as Django (pytz). See the
release history on pytz https://pypi.org/project/pytz/#history .
> Then, I'm not too keen on making `Now` cleverer. The whole point of the
functions in db.models that they proxy down transparently to what the DB
provides.
I agree Carlton. But perhaps we should have a warning note in the `Now`
docs about timezones, and a `UtcNow()` database function?
--
Ticket URL: <https://code.djangoproject.com/ticket/31638#comment:3>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Matthijs Kooijman):
> Is this not bad behaviour from MySQL, or lacking some config setting?
This is your "For MySQL, you can change the session timezone (e.g.
connection timezone)." yes?
MySQL behaves as documented, which does not seem unreasonable to me (given
their lack of timezone-aware datetimes, they just assume the session
timezone for all datetimes).
So I would think that settting the session timzone to UTC (or whatever
timezone is configured for this DB connection if any) would indeed be a
proper solution here, but that is something django must do on creating the
connection.
An alternative could be to change the Mysql global timezone to e.g. UTC,
but IMHO that would needlessly burden the administrator with additional
work and maybe cause problems with other users of the same server (if
changing global config is an option at all, e.g. on shared hosting).
> I see the intention in using the DB function, but my initial thought
looking at the test case in the PR was "why not timezone.now() there?".
It seemed like an elegant way to do the timestamping, but indeed,
`timezone.now()` could be a good alternative in most cases.
> Like Corey, I strongly believe in only using UTC for all servers and
data at rest. The rest of that podcast (with transcript) shows why.
Well, this is what I was doing, at least for the data. The only thing that
is not UTC here is the Mysql server timezone, which defaults to local
time. And Django transparently handles that for the most part, except for
`now()`, `CURTIME()` and `TIMESTAMP` fields.
> I agree Carlton. But perhaps we should have a warning note in the Now
docs about timezones, and a UtcNow() database function?
That could use `UTC_TIMESTAMP()` on Mysql. Downside is that this still
assumes knowledge about the database timezone (which defaults to UTC, but
could be modified). Especially in reusable django apps, this knowledge
might not be available.
> I'm not hopeful of any approach to trying to use the same timezone in
both the database and Django. Even if you get the same name, it can mean a
different offset.
Looks like you can also just set a numerical offset as the timezone
(https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html#time-zone-
installation):
> timezone values can be given in several formats, none of which are case
sensitive:
> ...
> - As a string indicating an offset from UTC of the form [H]H:MM,
prefixed with a + or -, such as '+10:00', '-6:00', or '+05:30'. A leading
zero can optionally be used for hours values less than 10; MySQL prepends
a leading zero when storing and retriving the value in such cases. MySQL
converts '-00:00' or '-0:00' to '+00:00'. Prior to MySQL 8.0.19, this
value had to be in the range '-12:59' to '+13:00', inclusive; beginning
with MySQL 8.0.19, the permitted range is '-14:00' to '+14:00', inclusive.
So I think that would remove that complication?
--
Ticket URL: <https://code.djangoproject.com/ticket/31638#comment:4>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Carlton Gibson):
> But perhaps we should have a warning note in the Now docs about
timezones...
Happy to review such.
I think other options need a fuller. (Fine if you two want to discuss here
but the mailing list is probably more suitable for extended discussion.)
--
Ticket URL: <https://code.djangoproject.com/ticket/31638#comment:5>