[Django] #32513: SQLite3 Backend Falsly claims SQLite does not support Timezone aware DateTimes
Django
DateTimes
-------------------------------------+-------------------------------------
Reporter: Arthur | Owner: nobody
Moore |
Type: Bug | Status: new
Component: Database | Version: 3.1
layer (models, ORM) |
Severity: Normal | Keywords: datetime, timezone
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 1
UI/UX: 0 |
-------------------------------------+-------------------------------------
If settings.USE_TZ is False, and I am using an SQLite3 database, I expect
to be able to store a timezone aware DateTime Field.
Currently in
[https://github.com/django/django/blob/b19041927883123e0147d80b820ddabee9a716f6/django/db/backends/sqlite3/operations.py#L236
the code], this raises a ValueError.
The SQLite [https://sqlite.org/lang_datefunc.html documentation] indicates
that this is actually valid. In addition, if a database entry is modified
with "-05:00" appended, then Django will correctly read from the database.
Though attempting to immediately call save fails.
**Example:**
With a database where the first record has a timestamp with a timezone,
and "settings.USE_TZ=False":
{{{
class DataPoint(models.Model):
timestamp = models.DateTimeField(max_length=255)
d = DataPoint.objects.first()
print(d.timestamp) # Successfully prints a datetime.datetime object with
the correct tzinfo
d.save() # Raises "ValueError: SQLite backend does not support timezone-
aware datetimes when USE_TZ is False."
}}}
**Why this matters:**
We are storing scientific measurements where the customer needs to know
the local day in which the data was recorded. This does not work by
calculating their current timezone, as they care about the information as
it was recorded not as it is now.
Our current approach is to leave "USE_TZ=True", store the timezone as an
offset in minutes every time a data collection session takes place, and
then combine them later. However, this means that we are either working
around the ORM when selecting dates, or just not using it entirely.
Ideally, we would leave "USE_TZ=True", and only save the DataPoint with a
timestamp, but that is a separate feature request.
--
Ticket URL: <https://code.djangoproject.com/ticket/32513>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
DateTimes
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: datetime, timezone | Triage Stage:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
If settings.USE_TZ is False, and I am using an SQLite3 database, I expect
to be able to store a timezone aware DateTime Field.
Currently in
[https://github.com/django/django/blob/b19041927883123e0147d80b820ddabee9a716f6/django/db/backends/sqlite3/operations.py#L236
the code], this raises a ValueError.
The SQLite [https://sqlite.org/lang_datefunc.html documentation] indicates
that this is actually valid. In addition, if a database entry is modified
with "-05:00" appended, then Django will correctly read from the database.
Though attempting to immediately call save fails.
**Example:**
With a database where the first record has a timestamp with a timezone,
and "settings.USE_TZ=False":
{{{
class DataPoint(models.Model):
timestamp = models.DateTimeField()
d = DataPoint.objects.first()
print(d.timestamp) # Successfully prints a datetime.datetime object with
the correct tzinfo
d.save() # Raises "ValueError: SQLite backend does not support timezone-
aware datetimes when USE_TZ is False."
}}}
**Why this matters:**
We are storing scientific measurements where the customer needs to know
the local day in which the data was recorded. This does not work by
calculating their current timezone, as they care about the information as
it was recorded not as it is now.
Our current approach is to leave "USE_TZ=True", store the timezone as an
offset in minutes every time a data collection session takes place, and
then combine them later. However, this means that we are either working
around the ORM when selecting dates, or just not using it entirely.
Ideally, we would leave "USE_TZ=True", and only save the DataPoint with a
timestamp, but that is a separate feature request.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32513#comment:1>
Django
DateTimes
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Arthur Moore):
Note that this code has been in the current state since commit
[https://github.com/django/django/commit/28308078f397d1de36fd0da417ac7da2544ba12d
28308078f397d1de36fd0da417ac7da2544ba12d]. Which was to
[https://code.djangoproject.com/ticket/22603 reorganize] the database.
The [https://www.sqlite.org/datatype3.html standard] (3.1.1. Affinity Name
Examples) implies that DATETIME will be converted to a number, and that is
probably what led to the confusion. In fact, some database tools
(JetBrains) do just that.* However, Django currently does not do that.
It creates the column with type "datetime", and stores it as Text.
Incidentally, this also means database tools like JetBrains can read, but
not modify DateTime columns in SQLite.
So, I guess Django is already violating the standard/convention, and the
question is should that violation be fixed, or leaned into. Fixing it
will almost certainly break any raw SQL or tool which relies on Django to
output Text. However, leaning in to the issue means the only way to apply
the "fix" later would be to add an additional "_timezone_offset" column.
\* DB Browser for SQLite does not.
--
Ticket URL: <https://code.djangoproject.com/ticket/32513#comment:2>
Django
DateTimes
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):
* cc: Aymeric Augustin (added)
* easy: 1 => 0
Comment:
This code is in Django since we added support for timezones in
9b1cb755a28f020e27d4268c214b25315d4de42e.
--
Ticket URL: <https://code.djangoproject.com/ticket/32513#comment:3>
Django
DateTimes
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Comment (by Aymeric Augustin):
> Ideally, we would leave "USE_TZ=True", and only save the DataPoint with
a timestamp, but that is a separate feature request.
This is how Django is intended to be used and I think that's what you
should be doing.
--
Ticket URL: <https://code.djangoproject.com/ticket/32513#comment:4>
Django
DateTimes
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Comment (by Arthur Moore):
> This is how Django is intended to be used and I think that's what you
should be doing.
How? Current that does not appear to be possible. The code referenced
either explicitly strips the timestamp if "USE_TZ=True" or it raises a
ValueError, which is this Bug. I could go with a custom field that
bypasses `adapt_datetimefield_value`, but that seems overkill for simply
trying to store a timezone.
Regardless, this bug exists because the ValueError does not reflect the
reality of how this database connection works in practice.
--
Ticket URL: <https://code.djangoproject.com/ticket/32513#comment:5>
Django
DateTimes
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
* cc: udaykiran (added)
* owner: nobody => udaykiran
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/32513#comment:6>
Django
DateTimes
-------------------------------------+-------------------------------------
Component: Database layer | Version: 3.1
(models, ORM) |
Keywords: datetime, timezone | Triage Stage:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => invalid
Comment:
Hi Arthur — I'm going to close this as invalid, as I think it's a usage
question (appropriate for the [https://forum.djangoproject.com forum] or
django-users mailing list) rather than an issue with Django.
From the description it's working as expected. With `USE_TZ = True` aware
datetimes are converted to UTC before storage, usually from the current
user's timezone, because they get created with that set, but in your case,
you could create the datetime specifying the tz for the collection
location. If you need to recreate the offset aware datetime later then you
should store the location (to map to correct offsets) or store the offset
itself in a separate field. You'll receive a UTC datetime from the DB, but
can replace the tz as needed. TZ maths always needs thinking about but you
should be able to `filter()` on the combination of the datetime and offset
fields, if you're using that, as needed.
I hope that helps. If you need more please do forum on one of the support
channels.
--
Ticket URL: <https://code.djangoproject.com/ticket/32513#comment:7>
Django
DateTimes
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: invalid =>
Comment:
I am sorry the discussion drifted off topic. The system is **NOT**
working as intended. The ORM is explicitly blocking functionality
supported by the database, and lying about the reason. If the reason is
the Django default DateTime object refuses to ever store timezones in the
database, then it should say that at least.
This is further exacerbated by the part where PostgreSQL's documentation
says that it just silently converts all DateTimes to UTC prior to saving.
I have not confirmed, but that would also silently mean that any
PostgreSQL user with `USE_TZ = False` might be in for a surprise.
I can see two approaches to solving this bug.
* The SQLite backend's `adapt_datetimefield_value` is adjusted to support
timezone aware DateTimes when `USE_TZ = False`.
* Django as a whole should declare that when `USE_TZ = False`, a user may
never save a Timezone aware DateTime.
* That would involve adding checks to
`BaseDatabaseOperations.adapt_datetimefield_value` and the PostgreSQL
backend.
--
Ticket URL: <https://code.djangoproject.com/ticket/32513#comment:8>
Django
DateTimes
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Comment (by Aymeric Augustin):
I'm afraid there are some misunderstandings in this ticket.
**Misunderstanding 1**
The initial report says:
> I expect to be able to store a timezone aware DateTime Field. The SQLite
documentation indicates that this is actually valid.
It proceeds to the conclusion that Django should support that.
This reasoning relies on the implicit assumption that Django commits to
supporting all features of all databases. This assumption is incorrect.
Django doesn't make that promise. Rather, it's focusing on providing a
convenient Python API for the most common use cases and escape hatches for
the less common ones.
Django is able to serialize various types of objects, but not all of them.
For example, timezone-aware datetimes aren't supported when support for
timezone-aware datetimes is disabled (USE_TZ = False). I'm sorry for the
tautology, but I'm having a hard time making sense of your resistance to
USE_TZ = True.
(As you noticed, there are differences of behavior in the PostgreSQL
backend for legacy reasons -- it happened to have limited support for
timezone-aware datetimes before Django gained explicit support for
timezone-aware datetimes. That behavior was preserved for backwards-
compatibility reasons.)
If you'd like to store a timestamp with a particular timezone and later
retrieve the same timestamp and timezone, you have to use a custom field.
You can easily find third-party implementations of this functionality.
DateTimeField doesn't provide it; it doesn't remember the timezone. You
can store a timezone-aware datetime with any timezone attached, but when
you read it back, you always get a timezone-aware datetime in UTC
representing the same point in time. Then you can convert it back to any
timezone you like, and if you convert to the original timezone, of course
you get the original timestamp.
If you'd like to propose further changes in this area, #2626 and the
related thread on the django-developers mailing list would be good places
to start. After reviewing the design, you can make an argument about
improving some parts. There were a few features added since the original
proposal but the general principles still hold.
**Misunderstanding 2**
The lack of support in Django's SQLite database backend is plainly stated
in the message you're quoting: "SQLite backend does not support ..." I'm
not seeing how Django is lying there; to me it looks like your requirement
("it should say that at least") is met. Did you confuse "SQLite backend"
(the component of Django) with "SQLite database" (the database itself)?
--
Ticket URL: <https://code.djangoproject.com/ticket/32513#comment:9>
Django
DateTimes
-------------------------------------+-------------------------------------
Component: Database layer | Version: 3.1
(models, ORM) |
Keywords: datetime, timezone | Triage Stage:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => wontfix
Comment:
Thanks for the clarification/follow-up both.
I'll close as wontfix on the change in the designed behaviour.
As per Aymeric's comments, there would need to be discussion to amend
that.
Please see TicketClosingReasons/DontReopenTickets for details on the
project triage flow.
--
Ticket URL: <https://code.djangoproject.com/ticket/32513#comment:10>