> It is also a best practice to store naive datetimes representing UTC in databases.
It is most definitely NOT a best practice and you SHOULD NOT DO THIS.
If your database supports TIMESTAMP WITH TIME ZONE -- which the "aware" type per the article's terminology -- you should be using it. If your database doesn't, you should get a better one.
Happy to be corrected here but it's not intuitive to me that dealing with timezones as the 'base unit' makes anything easier.
I've been in each camp at one time or another, since being db-agnostic makes sense in some cases (libraries, products) and not in others (websites, services).
For example, imagine I schedule a dentist appointment for 9AM in my local timezone sometime next summer, and put it in my calendar. Now, if the government decides that we won't do daylight saving times next year, my dentist and I still expect that appointment to happen at 9AM local time. However, if my calendar application had converted it to UTC using the timezone definitions at the moment I created the appointment, it would show in my calendar at 8AM, and I'd be an hour early!
This is not an as hypothetical situation as it seems. To give just two recent examples: the Chilean government decided to postpone the start of DST with less than a month's notice last year, and Morocco shifts the clock an hour during Ramadan, the date of which only becomes certain on the day itself.
NO!!! IT IS NOT THE SAME AT ALL!
I've been burned by that exact misconception. TIMESTAMP WITH TIME ZONE does NOT store a timezone in the database!
It implicitly converts a timestamp you supply into UTC and DISCARDS the associated timezone information you provided. When you read it back, it converts it to LOCAL time unless you explicitly specify a different conversion. This is a recurring source of confusion and bugs. The timezone a timestamp was actually written in is sometimes VERY important -- and I've personally had to deal with data loss on a project caused by this misconception.
Let me repeat: There is NO WAY to read back what timezone a "TIMESTAMP WITH TIME ZONE" was written with later -- unlike an "aware" datetime in Python -- BECAUSE IT DOES NOT ACTUALLY STORE THE TIME ZONE YOU SUPPLIED! This is a data type that DESTROYS INFORMATION YOU PROVIDED TO THE DATABASE assuming it can be clever later.
I fucking hate that data type SO much.
An "aware" datetime in Python actually tracks a timestamp with a specific timezone explicitly attached to it. It is VERY different!
Documentation for Postgres:
> For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.
> When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.4).
https://www.postgresql.org/docs/current/datatype-datetime.ht...
Python:
> Date and time objects may be categorized as “aware” or “naive” depending on whether or not they include timezone information.
> With sufficient knowledge of applicable algorithmic and political time adjustments, such as time zone and daylight saving time information, an aware object can locate itself relative to other aware objects. An aware object represents a specific moment in time that is not open to interpretation.
[...]
> For applications requiring aware objects, datetime and time objects have an optional time zone information attribute, tzinfo, that can be set to an instance of a subclass of the abstract tzinfo class. These tzinfo objects capture information about the offset from UTC time, the time zone name, and whether daylight saving time is in effect.
TIMESTAMP WITH TIME ZONE just sets off my lingering issues from an old project.
All the other posts in this thread misunderstanding it really demonstrate the point about it being a source of recurring confusion though!
However, if you are writing something like a calendar app and storing timestamps for future events, you run into the issue that time zones have the annoying habit of changing.
Let's say you have a user in Berlin, and they want to schedule an event for 2025-06-01 08:00 local time. According to the current timezone definition Berlin will be on CEST at that moment, which is UTC+2, so you store it as 06:00 UTC. Then Europe abandons summer time, and on 2025-06-01 Berlin will now be on CET, or UTC+1. Your app server obviously gets this new definition via its regular OS upgrades.
Your user opens the app, 06:00 UTC gets converted back to local time, and is displayed as 07:00 local time. This is obviously wrong, as they entered it as 08:00 local time!
Storing it as TIMESTAMP WITH TIME ZONE miiiight save you, but if and only if the database stores the timezone as "Europe/Berlin". In practice there's a decent chance it is going to save it as a UTC offset anyways...
which is basically exactly how one is supposed to use timezone-naive datetimes. the datetimes are individually "naive" however are meant to always be used within a context that is implicitly non-naive across the full set of datetime objects.