5 ms·

When storing dates in a database I always store them in Unix Epoch time and I don't record the timezone information on the date field (it is stored separately i

When storing dates in a database I always store them in Unix Epoch time and I don't record the timezone information on the date field (it is stored separately if there was a requirement to know the timezone). Should we instead be storing time stamps in TAI format, and then use functions to convert time to UTC as required, ensuring that any adjustments for planetary tweaks can be performed as required? I know that timezones are a field of landmines, but again, that is a human construct where timezone boundaries are adjusted over time. It seems we need to anchor on absolute time, and then render that out to whatever local time format we need, when required.

hx8 2y ago
Maybe, it really depends on what your systems are storing. Most systems really won't care if you are one second off every few years. For some calculations being a second off is a big deal. I think you should tread carefully when adopting any format that isn't the most popular and have valid reasons for deviating from the norm. The simple act of being different can be expensive.
semiquaver 2y ago
> 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.
christina97 2y ago
No, almost often no. Most software is written to paper over leap seconds: it really only happens at the clock synchronization level (chrony for example implements leap second smearing). All your cocks are therefore synchronized to UTC anyway: it would mean you’d have to translate from UTC to TAI when you store things, then undo when you retrieve. It would be a mess.
- growse 2y ago
  Smearing is alluring as a concept right up until you try and implement it in the real world. If you control all the computers that all your other computers talk to (and also their time sync sources), then smearing works great. You're effectively investing your own standard to make Unix time monatomic. If, however, your computers need to talk to someone else's computers and have some sort of consensus about what time it is, then the chances are your smearing policy won't match theirs, and you'll disagree on _what time it is_. Sometimes these effects are harmless. Sometimes they're unforseen. If mysterious, infrequent buggy behaviour is your kink, then go for it!
  - ratorx 2y ago
    Using time to sync between computers is one of the classic distributed systems problems. It is explicitly recommended against. The amount of errors in the regular time stack mean that you can’t really rely on time being accurate, regardless of leap seconds. Computer clock speeds are not really that consistent, so “dead reckoning” style approaches don’t work. NTP can only really sync to ~millisecond precision at best. I’m not aware of the state-of-the-art, but NTP errors and smearing errors in the worst case are probably quite similar. If you need more precise synchronisation, you need to implement it differently. If you want 2 different computers to have the same time, you either have to solve it at a higher layer up by introducing an ordering to events (or equivalent) or use something like atomic clocks.
    - growse 2y ago
      Fair, it's often one of those hidden, implicit design assumptions. Google explicitly built spanner (?) around the idea that you can get distributed consistency and availability iff you control All The Clocks. Smearing is fine, as long as it's interaction with other systems is thought about (and tested!). Nobody wants a surprise (yet actually inevitable) outage at midnight on New year's day.
lmm 2y ago
> Should we instead be storing time stamps in TAI format, and then use functions to convert time to UTC as required, ensuring that any adjustments for planetary tweaks can be performed as required? Yes. TAI or similar is the only sensible way to track "system" time, and a higher-level system should be responsible for converting it to human-facing times; leap second adjustment should happen there, in the same place as time zone conversion. Unfortunately Unix standardised the wrong thing and migration is hard.
- beng-nl 2y ago
  I wish there were a TAI timezone: just unmodified, unleaped, untimezoned seconds, forever, in both directions. I was surprised it doesn’t exist.
  - maxnoe 2y ago
    TAI is not a time zone. Timezones are a concept of civil time keeping, that is tied to the UTC time scale. TAI is a separate time scale and it is used to define UTC. There is now CLOCK_TAI in Linux [1], tai_clock [2] in c++ and of course several high level libraries in many languages (e.g. astropy.time in Python [3]) There are three things you want in a time scale: * Monotonically Increasing * Ticking with a fixed frequency, i.e. an integer multiple of the SI second * Aligned with the solar day Unfortunately, as always, you can only chose 2 out of the 3. TAI is 1 + 2, atomic clocks using the caesiun standard ticking at the frequency that is the definition of the SI second forever Increasing. Then there is UT1, which is 1 + 3 (at least as long as no major disaster happens...). It is purely the orientation of the Earth, measured with radio telescopes. UTC is 2 + 3, defined with the help of both. It ticks the SI seconds of TAI, but leap seconds are inserted at two possible time slots per year to keep it within 1 second of UT1. The last part is under discussion to be changed to a much longer time, practically eliminating future leap seconds. The issue then is that POSIX chose the wrong standard for numerical system clocks. And now it is pretty hard to change and it can also be argued that for performance reasons, it shouldn't be changed, as you more often need the civil time than the monotonic time. The remaining issues are: * On many systems, it's simple to get TAI * Many software systems do not accept the complexity of this topic and instead just return the wrong answer using simplified assumptions, e.g. of no leap seconds in UTC * There is no standardized way to handle the leap seconds in the Unix time stamp, so on days around the introduction of leap second, the relationship between the Unix timestamp and the actual UTC or TAI time is not clear, several versions exist and that results in uncertainty up to two seconds. * There might be a negative leap second one day, and nothing is ready for it [1] https://www.man7.org/linux/man-pages/man7/vdso.7.html https://www.man7.org/linux/man-pages/man7/vdso.7.html [2] https://en.cppreference.com/w/cpp/chrono/tai_clock https://en.cppreference.com/w/cpp/chrono/tai_clock [3] https://docs.astropy.org/en/stable/time/index.html https://docs.astropy.org/en/stable/time/index.html
wodenokoto 2y ago
Use your database native date-time field.
- SoftTalker 2y ago
  Seconded. Don't mess around with raw timestamps. If you're using a database, use its date-time data type and functions. They will be much more likely to handle numerous edge cases you've never even thought about.

hx8 2y ago

Maybe, it really depends on what your systems are storing. Most systems really won't care if you are one second off every few years. For some calculations being a second off is a big deal. I think you should tread carefully when adopting any format that isn't the most popular and have valid reasons for deviating from the norm. The simple act of being different can be expensive.

semiquaver 2y ago

> 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.

christina97 2y ago

No, almost often no. Most software is written to paper over leap seconds: it really only happens at the clock synchronization level (chrony for example implements leap second smearing). All your cocks are therefore synchronized to UTC anyway: it would mean you’d have to translate from UTC to TAI when you store things, then undo when you retrieve. It would be a mess.

growse 2y ago
Smearing is alluring as a concept right up until you try and implement it in the real world. If you control all the computers that all your other computers talk to (and also their time sync sources), then smearing works great. You're effectively investing your own standard to make Unix time monatomic. If, however, your computers need to talk to someone else's computers and have some sort of consensus about what time it is, then the chances are your smearing policy won't match theirs, and you'll disagree on _what time it is_. Sometimes these effects are harmless. Sometimes they're unforseen. If mysterious, infrequent buggy behaviour is your kink, then go for it!
- ratorx 2y ago
  Using time to sync between computers is one of the classic distributed systems problems. It is explicitly recommended against. The amount of errors in the regular time stack mean that you can’t really rely on time being accurate, regardless of leap seconds. Computer clock speeds are not really that consistent, so “dead reckoning” style approaches don’t work. NTP can only really sync to ~millisecond precision at best. I’m not aware of the state-of-the-art, but NTP errors and smearing errors in the worst case are probably quite similar. If you need more precise synchronisation, you need to implement it differently. If you want 2 different computers to have the same time, you either have to solve it at a higher layer up by introducing an ordering to events (or equivalent) or use something like atomic clocks.
  - growse 2y ago
    Fair, it's often one of those hidden, implicit design assumptions. Google explicitly built spanner (?) around the idea that you can get distributed consistency and availability iff you control All The Clocks. Smearing is fine, as long as it's interaction with other systems is thought about (and tested!). Nobody wants a surprise (yet actually inevitable) outage at midnight on New year's day.

lmm 2y ago

> Should we instead be storing time stamps in TAI format, and then use functions to convert time to UTC as required, ensuring that any adjustments for planetary tweaks can be performed as required? Yes. TAI or similar is the only sensible way to track "system" time, and a higher-level system should be responsible for converting it to human-facing times; leap second adjustment should happen there, in the same place as time zone conversion. Unfortunately Unix standardised the wrong thing and migration is hard.

beng-nl 2y ago
I wish there were a TAI timezone: just unmodified, unleaped, untimezoned seconds, forever, in both directions. I was surprised it doesn’t exist.
- maxnoe 2y ago
  TAI is not a time zone. Timezones are a concept of civil time keeping, that is tied to the UTC time scale. TAI is a separate time scale and it is used to define UTC. There is now CLOCK_TAI in Linux [1], tai_clock [2] in c++ and of course several high level libraries in many languages (e.g. astropy.time in Python [3]) There are three things you want in a time scale: * Monotonically Increasing * Ticking with a fixed frequency, i.e. an integer multiple of the SI second * Aligned with the solar day Unfortunately, as always, you can only chose 2 out of the 3. TAI is 1 + 2, atomic clocks using the caesiun standard ticking at the frequency that is the definition of the SI second forever Increasing. Then there is UT1, which is 1 + 3 (at least as long as no major disaster happens...). It is purely the orientation of the Earth, measured with radio telescopes. UTC is 2 + 3, defined with the help of both. It ticks the SI seconds of TAI, but leap seconds are inserted at two possible time slots per year to keep it within 1 second of UT1. The last part is under discussion to be changed to a much longer time, practically eliminating future leap seconds. The issue then is that POSIX chose the wrong standard for numerical system clocks. And now it is pretty hard to change and it can also be argued that for performance reasons, it shouldn't be changed, as you more often need the civil time than the monotonic time. The remaining issues are: * On many systems, it's simple to get TAI * Many software systems do not accept the complexity of this topic and instead just return the wrong answer using simplified assumptions, e.g. of no leap seconds in UTC * There is no standardized way to handle the leap seconds in the Unix time stamp, so on days around the introduction of leap second, the relationship between the Unix timestamp and the actual UTC or TAI time is not clear, several versions exist and that results in uncertainty up to two seconds. * There might be a negative leap second one day, and nothing is ready for it [1] https://www.man7.org/linux/man-pages/man7/vdso.7.html https://www.man7.org/linux/man-pages/man7/vdso.7.html [2] https://en.cppreference.com/w/cpp/chrono/tai_clock https://en.cppreference.com/w/cpp/chrono/tai_clock [3] https://docs.astropy.org/en/stable/time/index.html https://docs.astropy.org/en/stable/time/index.html

wodenokoto 2y ago

Use your database native date-time field.

SoftTalker 2y ago
Seconded. Don't mess around with raw timestamps. If you're using a database, use its date-time data type and functions. They will be much more likely to handle numerous edge cases you've never even thought about.