3 ms·

> Oh, you want to know the naive UTC datetime in Python, to interface with something like PostgreSQL that recommends naive times? Postgres never recommended na

> Oh, you want to know the naive UTC datetime in Python, to interface with something like PostgreSQL that recommends naive times? Postgres never recommended naive datetimes. A TZ-aware datetime is semantiacally the same as a tuple of (<location/agreed offset>, <time in the moment since unix epoch defined in terms of UTC>). Those who recommended dropping the knowledge of the first part from that pair did it because they didn't know better.

codr7 2y ago
It's a perfectly fine strategy in some situations to only store UTC in the database and handle time zones on display. It's your database, you know what's in there. As an added bonus it allows easily flagging non-UTC timestamps as errors on some level to make sure you don't get tangled up in time zones.
- instig007 2y ago
  > It's your database, you know what's in there. As an added bonus it allows easily flagging non-UTC timestamps as errors on some level to make sure you don't get tangled up in time zones. I think you're mixing rendering (and for that matter - time ordering) with the initial information providing, that is a precise data input that doesn't rely on anything external. Postgres ALWAYS stores timestamps in UTC [1]. When you submit data for persistence from your application, you have a choice of either: - informing the DB about the recorded location/offset at the point of persistence (even if it's the zero offset) so that the input conversion happens without any reliance on the underlying OS-level or configuration setting [2] - or omitting that information and therefore 1) losing precision at the point of data input and 2) delegating the location/offset inference for the internal purpose of Postgres to the external global configuration: both side-effects are bad from consistency/reliability perspectives. I've never seen a single application that would win anything from (2) compared to explicitly providing the offset of the observed moment, even if the entire business domain is in UTC at all times. In fact, I've seen many business applications that explicitly record the offset in a separate column next to the provided timestamps for any future analysis and retrieval. And it's partly by design of the underlying abstractions: the unix epoch is defined in terms of UTC too, so when you design your program around implicit UTC you are not gaining much - the offset information is still there, it's just your data types don't make it clearly visible to everyone. But the moment you start integrating your data with the real world that cares about global time ordering of your recorded data events, you get a whole bunch of silent mistakes and issues that you won't have enough data to fix reliably until you switch to explicit offsets in all timestamp evaluations. [1] https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT-TIME-STAMPS https://www.postgresql.org/docs/current/datatype-datetime.ht... [2] https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TIMEZONE https://www.postgresql.org/docs/current/runtime-config-clien...
  - codr7 2y ago
    You're describing a world that doesn't exist from my experience writing booking systems. Getting time zone conversions right all the way through the stack is far from trivial, which is why I prefer to do it in one place and keep the rest of the system out of it.
afiori 2y ago
Isn't the problem that almost no database stores the timezone (either offset or location) in the datetime datatype?

codr7 2y ago

It's a perfectly fine strategy in some situations to only store UTC in the database and handle time zones on display. It's your database, you know what's in there. As an added bonus it allows easily flagging non-UTC timestamps as errors on some level to make sure you don't get tangled up in time zones.

instig007 2y ago
> It's your database, you know what's in there. As an added bonus it allows easily flagging non-UTC timestamps as errors on some level to make sure you don't get tangled up in time zones. I think you're mixing rendering (and for that matter - time ordering) with the initial information providing, that is a precise data input that doesn't rely on anything external. Postgres ALWAYS stores timestamps in UTC [1]. When you submit data for persistence from your application, you have a choice of either: - informing the DB about the recorded location/offset at the point of persistence (even if it's the zero offset) so that the input conversion happens without any reliance on the underlying OS-level or configuration setting [2] - or omitting that information and therefore 1) losing precision at the point of data input and 2) delegating the location/offset inference for the internal purpose of Postgres to the external global configuration: both side-effects are bad from consistency/reliability perspectives. I've never seen a single application that would win anything from (2) compared to explicitly providing the offset of the observed moment, even if the entire business domain is in UTC at all times. In fact, I've seen many business applications that explicitly record the offset in a separate column next to the provided timestamps for any future analysis and retrieval. And it's partly by design of the underlying abstractions: the unix epoch is defined in terms of UTC too, so when you design your program around implicit UTC you are not gaining much - the offset information is still there, it's just your data types don't make it clearly visible to everyone. But the moment you start integrating your data with the real world that cares about global time ordering of your recorded data events, you get a whole bunch of silent mistakes and issues that you won't have enough data to fix reliably until you switch to explicit offsets in all timestamp evaluations. [1] https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT-TIME-STAMPS https://www.postgresql.org/docs/current/datatype-datetime.ht... [2] https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TIMEZONE https://www.postgresql.org/docs/current/runtime-config-clien...
- codr7 2y ago
  You're describing a world that doesn't exist from my experience writing booking systems. Getting time zone conversions right all the way through the stack is far from trivial, which is why I prefer to do it in one place and keep the rest of the system out of it.

afiori 2y ago

Isn't the problem that almost no database stores the timezone (either offset or location) in the datetime datatype?