Clarification on usage of sqlalchemy-utc

[raph90]

Hi, I just wanted to check that I understand the use of this library so that I can use it correctly in my application.
From what I understand:

• DateTime(timeZone=False) in SqlAlchemy will convert datetimes to UTC when storing, but will convert back (adjusting to the current server timezone) when retrieving data
• Sqlalchemy-utc ensures that we store UTC time and get back UTC time (without the conversion to current server timezone). This allows us to manipulate the UTC timezone how we want.

Is that correct?

[dahlia]

DateTime from SQLAlchemy is one of vendor-neutral “generic types,” which means its behavioral details depend on the engine (e.g., SQLite or PostgreSQL) and vary at runtime. In general, DateTime(timezone=True) assumes a value is an aware datetime, whereas DateTime(timezone=False) assumes it's a naive datetime.¹ However, it's undefined if DateTime(timezone=True) takes a naive one or DateTime(timezone=False) takes an aware one, and it actually behaves quite differently on various engines. SQLite engine even ignores timezone offsets whether it's DateTime(timezone=True) or DateTime(timezone=False).

On the other hand, UtcDateTime does not take any naive datetime values at all. It explicitly rejects them.

UtcDateTime's another function is that it normalizes offsets of aware datetimes into zero (i.e., UTC). It does not mean their offsets are merely dropped, but their offset timestamps are shifted to the equivalent UTC timestamps. For example, datetime(2022, 3, 27, 3, 5, 50, tzinfo=timezone(timedelta(hours=9))) is normalized to datetime(2022, 3, 26, 18, 5, 50, tzinfo=timezone.utc).

Footnotes

1. Although Python datetime's such dualism is generally considered an architectural flaw, every Python programmer anyway has to deal with it. ↩