> If your database supports TIMESTAMP WITH TIME ZONE -- which the "aware" type per the article's terminology -- you should be using it.
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).
> 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.
I agree (though less indignantly). TIMESTAMP WITH TIME ZONE basically makes me confused as it is trying to run around guessing how to actively solve a problem I dont actually have, which is, "I don't know how to correctly represent datetimes in my application".
what people usually want, when they are confused about that datatype, is a datatype that stores the timestamp *and* a specific timezone to go along with it, so you get back a timestamp with that same timezone if you did not ask to convert it to another. that would be a bit less efficient from a storage perspective but would likely be more intuitive.
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.
https://docs.python.org/3/library/datetime.html