SQLite3 Backend Falsly claims SQLite does not support Timezone aware DateTimes

[Arthur Moore]

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 ​the code, this raises a ValueError.

The SQLite ​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.

[Arthur Moore]

Note that this code has been in the current state since commit ​28308078f397d1de36fd0da417ac7da2544ba12d. Which was to reorganize the database.

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

[Mariusz Felisiak]

This code is in Django since we added support for timezones in 9b1cb755a28f020e27d4268c214b25315d4de42e.

[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.

[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.

[Carlton Gibson]

Hi Arthur — I'm going to close this as invalid, as I think it's a usage question (appropriate for the ​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.

[Arthur Moore]

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.
  • It will also require documentation changes making this point explicitly clear as a design decision instead of database limitations.

[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)?

[Carlton Gibson]

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.