4 ms·

> and I don't record the timezone information on the date field Very few databases actually make it possible to preserve timezone in a timestamp column. Typica

> and I don't record the timezone information on the date field Very few databases actually make it possible to preserve timezone in a timestamp column. Typically the db either has no concept of time zone for stored timestamps (e.g. SQL server) or has “time zone aware” timestamp column types where the input is converted to UTC and the original zone discarded (MySQL, Postgres) Oracle is the only DB I’m aware of that can actually round-trip nonlocal zones in its “with time zone” type.

mulmen 2y ago
As always the Postgres docs give an excellent explanation of why this is the case: https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES https://www.postgresql.org/docs/current/datatype-datetime.ht...
- Scarblac 2y ago
  I read it but I only see an explanation about what it does, not the why. It could have stored the original timezone.
  - bvrmn 2y ago
    What's "original timezone"? Most libraries implement timezone aware dates as an offset from UTC internally. What tzinfo uses oracle? Is it updated? Is it similar to tzinfo used in your service? It's highly complicated topic and it's amazing PostgreSQL decided to use instant time for 'datetime with timezone' type instead of Oracle mess.
    - NoInkling 2y ago
      > Most libraries implement timezone aware dates as an offset from UTC internally. For what it's worth, the libraries that are generally considered "good" (e.g. java.time, Nodatime, Temporal) all offer a "zoned datetime" type which stores an IANA identifier (and maybe an offset, but it's only meant for disambiguation w.r.t. transitions). Postgres already ships tzinfo and works with those identifiers, it just expects you to manage them more manually (e.g. in a separate column or composite type). Also let's not pretend that "timestamp with time zone" isn't a huge misnomer that causes confusion when it refers to a simple instant. I suspect you might be part of the contingent that considers such a combined type a fundamentally bad idea, however: https://errorprone.info/docs/time#zoned_datetime https://errorprone.info/docs/time#zoned_datetime
      - bvrmn 2y ago
        I agree naming is kinda awful. But you need geo timezone only for rare cases and handling it in a separate column is not that hard. Instant time is the right thing for almost all cases beginners want to use `datetime with timezone` for.
    - Scarblac 2y ago
      The discussion was about storing a timestamp as UTC, plus the timezone the time was in originally as a second field. Postgres has timezone aware datetime fields, that translate incoming times to UTC, and outgoing to a configured timezone. So it doesnt store what timezone the time was in originally. The claim was that the docs explain why not, but they don't.

mulmen 2y ago

As always the Postgres docs give an excellent explanation of why this is the case: https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES https://www.postgresql.org/docs/current/datatype-datetime.ht...

Scarblac 2y ago
I read it but I only see an explanation about what it does, not the why. It could have stored the original timezone.
- bvrmn 2y ago
  What's "original timezone"? Most libraries implement timezone aware dates as an offset from UTC internally. What tzinfo uses oracle? Is it updated? Is it similar to tzinfo used in your service? It's highly complicated topic and it's amazing PostgreSQL decided to use instant time for 'datetime with timezone' type instead of Oracle mess.
  - NoInkling 2y ago
    > Most libraries implement timezone aware dates as an offset from UTC internally. For what it's worth, the libraries that are generally considered "good" (e.g. java.time, Nodatime, Temporal) all offer a "zoned datetime" type which stores an IANA identifier (and maybe an offset, but it's only meant for disambiguation w.r.t. transitions). Postgres already ships tzinfo and works with those identifiers, it just expects you to manage them more manually (e.g. in a separate column or composite type). Also let's not pretend that "timestamp with time zone" isn't a huge misnomer that causes confusion when it refers to a simple instant. I suspect you might be part of the contingent that considers such a combined type a fundamentally bad idea, however: https://errorprone.info/docs/time#zoned_datetime https://errorprone.info/docs/time#zoned_datetime
    - bvrmn 2y ago
      I agree naming is kinda awful. But you need geo timezone only for rare cases and handling it in a separate column is not that hard. Instant time is the right thing for almost all cases beginners want to use `datetime with timezone` for.
  - Scarblac 2y ago
    The discussion was about storing a timestamp as UTC, plus the timezone the time was in originally as a second field. Postgres has timezone aware datetime fields, that translate incoming times to UTC, and outgoing to a configured timezone. So it doesnt store what timezone the time was in originally. The claim was that the docs explain why not, but they don't.