4 ms·

This was an aside and not the point of the article but I can't let it go: > It is also a best practice to store naive datetimes representing UTC in databases.

This was an aside and not the point of the article but I can't let it go: > 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.

klysm 3y ago
This is a good idea for Postgres but only because of its specific semantics
spsesk117 3y ago
Would you care to elaborate? I'm not an expert in this area so I'm probably missing something, but my thought has always been the 'portability' of storing everything in the backend as UTC is more flexible/less complex in the long run, dealing with conversation at the 'last mile' so to speak. Happy to be corrected here but it's not intuitive to me that dealing with timezones as the 'base unit' makes anything easier.
- toyg 3y ago
  It's the usual divide between people who actually know how databases work, and hence want to do more stuff there (because it will likely be faster and more robust); and people who don't know (and don't want to know) anything about databases, and hence would rather use them as dumb stores of basic data. 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).
- int_19h 3y ago
  It's not an either-or. You can store everything in UTC while also using TIMESTAMP WITH TIME ZONE. The timezone will always be UTC, but that way at least anyone who will be dealing with this data later knows that to be the case, instead of assuming.
- Denvercoder9 3y ago
  Converting from a local date/time with an associated timezone to an UTC time is not a lossless nor unchanging operation. By converting everything to UTC, you're throwing away information that you might need to do the conversion at the 'last mile' later on. 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.
ntonozzi 3y ago
Can you explain why? I write web apps, and I typically want to know an instant in time when something occurred and then localize the time for display. Why would I need to store a time zone for that?
- crote 3y ago
  Storing UTC for past events is totally fine because it is unambiguous. 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...
seanp2k2 3y ago
If you’re storing time zone info for a transaction in the database instead of storing it as epoch time, you’re probably doing it wrong. Time zone is a user-level presentation layer issue. Keep that junk out of the backend. Imagine writing code to reconcile financial transactions that also had to deal with time zone offsets to figure out if one transaction happened before another. So much extra complexity for no reason. I don’t know where you’ve been doing this or where you got this idea, but it’s most definitely not common or advisable to store TZ info on transactions anywhere I’ve worked in my couple of decades at some very large software companies. TZ info is usually stored with a user account as a preference that they can update, or it’s detected from their browser during presentation time, because that’s when it matters — when you’re showing it to a human. Having machines calculate TZ offsets between database records is asinine, inefficient, and a great way to introduce an entire domain of bugs for absolutely no reason.
Figs 3y ago
> 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). https://www.postgresql.org/docs/current/datatype-datetime.html 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 https://docs.python.org/3/library/datetime.html
zzzeek 3y ago
TIMESTAMP WITH TIME ZONE stores the timestamp naively. The type defines a convention for how data is transmitted to and from the datatype. 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.

klysm 3y ago

This is a good idea for Postgres but only because of its specific semantics

spsesk117 3y ago

Would you care to elaborate? I'm not an expert in this area so I'm probably missing something, but my thought has always been the 'portability' of storing everything in the backend as UTC is more flexible/less complex in the long run, dealing with conversation at the 'last mile' so to speak. Happy to be corrected here but it's not intuitive to me that dealing with timezones as the 'base unit' makes anything easier.

toyg 3y ago
It's the usual divide between people who actually know how databases work, and hence want to do more stuff there (because it will likely be faster and more robust); and people who don't know (and don't want to know) anything about databases, and hence would rather use them as dumb stores of basic data. 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).
int_19h 3y ago
It's not an either-or. You can store everything in UTC while also using TIMESTAMP WITH TIME ZONE. The timezone will always be UTC, but that way at least anyone who will be dealing with this data later knows that to be the case, instead of assuming.
Denvercoder9 3y ago
Converting from a local date/time with an associated timezone to an UTC time is not a lossless nor unchanging operation. By converting everything to UTC, you're throwing away information that you might need to do the conversion at the 'last mile' later on. 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.

ntonozzi 3y ago

Can you explain why? I write web apps, and I typically want to know an instant in time when something occurred and then localize the time for display. Why would I need to store a time zone for that?

crote 3y ago
Storing UTC for past events is totally fine because it is unambiguous. 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...

seanp2k2 3y ago

If you’re storing time zone info for a transaction in the database instead of storing it as epoch time, you’re probably doing it wrong. Time zone is a user-level presentation layer issue. Keep that junk out of the backend. Imagine writing code to reconcile financial transactions that also had to deal with time zone offsets to figure out if one transaction happened before another. So much extra complexity for no reason. I don’t know where you’ve been doing this or where you got this idea, but it’s most definitely not common or advisable to store TZ info on transactions anywhere I’ve worked in my couple of decades at some very large software companies. TZ info is usually stored with a user account as a preference that they can update, or it’s detected from their browser during presentation time, because that’s when it matters — when you’re showing it to a human. Having machines calculate TZ offsets between database records is asinine, inefficient, and a great way to introduce an entire domain of bugs for absolutely no reason.

Figs 3y ago

> 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). https://www.postgresql.org/docs/current/datatype-datetime.html 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 https://docs.python.org/3/library/datetime.html

zzzeek 3y ago

TIMESTAMP WITH TIME ZONE stores the timestamp naively. The type defines a convention for how data is transmitted to and from the datatype. 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.