SQLite emulation of db_default involving Extract/Trunc might unexpectedly apply overridden timezone for inserted values

[Jacob Walls]

As developed in this ​forum post, if

• USE_TZ = True
• a model field uses Extract or Trunc in a db_default expression
• a test case uses override_settings() to test timezone behavior

... then in scenarios where Django must emulate the database default for SQLite in Python, the value will be transformed according to the overridden timezone setting, but this is an improper emulation, since the database's default cannot be overridden by merely flipping a Django setting.

The scenarios where Django must emulate the db default for SQLite vary depending on edges like:

• whether any field's value has been set
• whether the field with Extract/Trunc is last
• possibly: whether create() vs. bulk_create() is called

---

There are two ways to fix this as described on forum:

• introduce a heuristic (Simon: if ddl_compiler_heuristic():) to switch on whether to use the project default timezone or the test's overridden one
• implement supports_default_keyword_in_insert on SQLite by emulating it in its insert compiler

Having only thought about it today, the second approach seems like it would be simpler to implement and potentially pay broader dividends for other use cases.

Related ticket: #37013

[Simon Charette]

I also think the second solution is the preferable one as there are likely other expressions out there that rely on global state expected to be captured at field definition time and might suffer from the same problem. It also more aligned with how we've been doing things historically when running into these kind of problems.