#31638: On Mysql/Sqlite, db.models.functions.Now uses wrong timezone
-------------------------------------+-------------------------------------
Reporter: Matthijs Kooijman | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/074.b2db6ac33f2114b0b9770a2b7eb72e96%40djangoproject.com.
