7 ms·

> There is no reason to include UTC or time zones in your data model. That is a bold claim. Not everything is about the entity viewing the data, sometimes it i

> There is no reason to include UTC or time zones in your data model. That is a bold claim. Not everything is about the entity viewing the data, sometimes it is about the entity producing the data. Not storing the timezone is losing some information. A few examples: - The "Date" header in emails include the timezone (or UTC offset) of the sender. It allows the recipient to know whether the email was written in the morning or the evening. - A SQL query that builds a report of restaurant orders per hour needs to normalize for time of the day in local time otherwise it is not possible to know whether people order lunch at 11 am, midday or 1 pm.

foxhill 4y ago
> [...] sometimes it is about the entity producing the data so should we not store that data, then? > The "Date" header in emails include the timezone (or UTC offset) of the sender [...] i don't think that's strictly true - at best it may contain the timezone configured on the local machine when the e-mail was sent (what happens when you fly?), at worst it always contains UTC anyway for privacy concerns. in either case i don't think most people know this is a field that is transmitted, and it's presumably something the sending/recieving party likely already know each other (for personal communication) > A SQL query that build a report of restaurant orders per hour needs to normalize for time of the day in local time [...] i'd posit that that data should be stored principally then, surely? fundamentally, why should the timestamp contain two data points? i wouldn't expect the database to have a single column for, say, my user id and registering user-agent string, so why would i expect it to combine a point in time with a vague location?
- nicolaslem 4y ago
  Ultimately my comment was about entropy: a date with a timezone contains more information than a date alone. The technicalities about database columns, inaccuracies or privacy implications do not change this fact. I argued that keeping this information instead of discarding it is often useful and gave a few examples. I understand that sometimes systems can get by with only storing local dates or UTC dates but sometimes a system needs to be able to deal with both and keeping these extra bits of information is the only way.
  - foxhill 4y ago
    of course, if you need it then you need it. i think the original commenters point only talks about how bad TSTZ is as a storage format, and that TAI is not only better than timestamp with time zone, but also UTC in almost every measure - something i agree with - not that time zones are useless. if you need the time zone to be recorded, i think the most sensible thing would be to record it independently.
    - lowercased 4y ago
      recently did a project where some items were "future dates" - scheduled appointments for days/weeks/months in future, and were for entities in different time zones, and people making the appointments were occasionally in different time zones. The latter part was rare, but needed to accomodate that. Storing the appointment date/time as ... a date/time without timezone (timestamp without tz in postgres IIRC), then storing the timezone as a separate string - that gave enough flexibility to accomodate any situation that came up. "Wall time" - the date/time and tz separated - for future dates worked. Someone else wanted to store the date/time as 'utc timestamp' after the date had passed. "Wall time for future, UTC for past" was the motto, but... there wasn't a clear way of looking at the data to know which one you should be using immediately. I argued for consistency in the original data - if you want another view of it... make a db view that has the 'utc timestamp' conversion in it.
  - mgkimsal 4y ago
    I've had pushback on items like this sometimes with the standard "YAGNI" excuse. A few months ago I came across some blog post describing the reverse: "YAGRI" - You Ain't Gonna Regret It. Just... store the extra data.
- iso1631 4y ago
  > it may contain the timezone configured on the local machine when the e-mail was sent (what happens when you fly?), at Most people have their system clock update automatically
  - foxhill 4y ago
    indeed, but i don’t think they are able to retroactively update the time stamp of an already sent email :)
    - iso1631 4y ago
      Why would it? "It allows the recipient to know whether the email was written in the morning or the evening." I guess sure, you could write it in the morning then send it in the evening. Most people write and send at the same time, and thus most headers have the stamp of the time it was written.
- alphazard 4y ago
  This guy gets it. Thank you for explaining better than I did. If someone wants to reveal their location, why wouldn't they include latitude as well? Location is a separate piece of information. If the idea is to give the reader an idea of where the sun was in the sky when the writer crafted the timestamp, isn't latitude also important?
pstadler 4y ago
This is not only a bold claim, but utterly wrong. Every single datetime you’re storing should absolutely contain a timezone, preferably UTC. I‘ve been bitten by dates lacking timezones in various situations during my career and it has always been a pain in the ass to debug and resolve all those random problems they‘re causing. Do. Save. Dates. Containing. Timezones.
- tluyben2 4y ago
  I am that donkey banging it's head on the same post; when I see dates stored without TZ, I assume it's UTC and have done so for decades. It's turned out to almost never be true.
- Eddy_Viscosity2 4y ago
  > preferably UTC Yes! I have spent so much time detangling timestamps in data which used non-UTC times and non-standard formats. Like 06-10-01 WTF is that? Use UTC and then let the reader translate to local time on display.
- dpcx 4y ago
  My understanding is that you don't even want to store them as UTC and always store them in the timezone of the user/system that generated the data since you lose some information in the translation from local timezone to UTC - given that the timezone database changes, you may actually want to know that the record created in Fiji before 2022-10-28[1] was stored in DST so that it can be properly converted to the new Fiji time. [1]: https://data.iana.org/time-zones/tzdb/NEWS https://data.iana.org/time-zones/tzdb/NEWS
  - bouke 4y ago
    This! People mistake the offset for the timezone, which is incorrect. The offset isn't very useful other than a guestimate of the originating timezone.
  - cratermoon 4y ago
    Ideally store both, UTC and original local time, or at least the original TZ information. Time and timezone are a huge mess and one of the worst things to try deal with in programming.
csnweb 4y ago
Your sql query example doesn’t seem to actually need timezones. If all orders are stored in the correct UTC regardless where they come from. An order is always made at a specific point in time that is the same globally.
marcosdumay 4y ago
On your second example, you should be able to do that without storing the timezone of each event. In fact, odds are the timezone of each event will mislead you and break your analysis. On the email example, the timezone is there for debugging purposes. It's really not for knowing if the email was sent in the morning, and if you use it for that, you are almost guaranteed to get broken data. Of course, there are reasons to include timezones on your data. That's what you get right. But if you want the timezone of the data producer, you should store it for the producer, not for the data. (And then there is the issue that the SQL standard dictates that dates without a timezone are broken. So you shouldn't ever store them on a SQL database. But that one timezone is useless, if you need that information, you should store it elsewhere.)

foxhill 4y ago

> [...] sometimes it is about the entity producing the data so should we not store that data, then? > The "Date" header in emails include the timezone (or UTC offset) of the sender [...] i don't think that's strictly true - at best it may contain the timezone configured on the local machine when the e-mail was sent (what happens when you fly?), at worst it always contains UTC anyway for privacy concerns. in either case i don't think most people know this is a field that is transmitted, and it's presumably something the sending/recieving party likely already know each other (for personal communication) > A SQL query that build a report of restaurant orders per hour needs to normalize for time of the day in local time [...] i'd posit that that data should be stored principally then, surely? fundamentally, why should the timestamp contain two data points? i wouldn't expect the database to have a single column for, say, my user id and registering user-agent string, so why would i expect it to combine a point in time with a vague location?

nicolaslem 4y ago
Ultimately my comment was about entropy: a date with a timezone contains more information than a date alone. The technicalities about database columns, inaccuracies or privacy implications do not change this fact. I argued that keeping this information instead of discarding it is often useful and gave a few examples. I understand that sometimes systems can get by with only storing local dates or UTC dates but sometimes a system needs to be able to deal with both and keeping these extra bits of information is the only way.
- foxhill 4y ago
  of course, if you need it then you need it. i think the original commenters point only talks about how bad TSTZ is as a storage format, and that TAI is not only better than timestamp with time zone, but also UTC in almost every measure - something i agree with - not that time zones are useless. if you need the time zone to be recorded, i think the most sensible thing would be to record it independently.
  - lowercased 4y ago
    recently did a project where some items were "future dates" - scheduled appointments for days/weeks/months in future, and were for entities in different time zones, and people making the appointments were occasionally in different time zones. The latter part was rare, but needed to accomodate that. Storing the appointment date/time as ... a date/time without timezone (timestamp without tz in postgres IIRC), then storing the timezone as a separate string - that gave enough flexibility to accomodate any situation that came up. "Wall time" - the date/time and tz separated - for future dates worked. Someone else wanted to store the date/time as 'utc timestamp' after the date had passed. "Wall time for future, UTC for past" was the motto, but... there wasn't a clear way of looking at the data to know which one you should be using immediately. I argued for consistency in the original data - if you want another view of it... make a db view that has the 'utc timestamp' conversion in it.
- mgkimsal 4y ago
  I've had pushback on items like this sometimes with the standard "YAGNI" excuse. A few months ago I came across some blog post describing the reverse: "YAGRI" - You Ain't Gonna Regret It. Just... store the extra data.
iso1631 4y ago
> it may contain the timezone configured on the local machine when the e-mail was sent (what happens when you fly?), at Most people have their system clock update automatically
- foxhill 4y ago
  indeed, but i don’t think they are able to retroactively update the time stamp of an already sent email :)
  - iso1631 4y ago
    Why would it? "It allows the recipient to know whether the email was written in the morning or the evening." I guess sure, you could write it in the morning then send it in the evening. Most people write and send at the same time, and thus most headers have the stamp of the time it was written.
alphazard 4y ago
This guy gets it. Thank you for explaining better than I did. If someone wants to reveal their location, why wouldn't they include latitude as well? Location is a separate piece of information. If the idea is to give the reader an idea of where the sun was in the sky when the writer crafted the timestamp, isn't latitude also important?

pstadler 4y ago

This is not only a bold claim, but utterly wrong. Every single datetime you’re storing should absolutely contain a timezone, preferably UTC. I‘ve been bitten by dates lacking timezones in various situations during my career and it has always been a pain in the ass to debug and resolve all those random problems they‘re causing. Do. Save. Dates. Containing. Timezones.

tluyben2 4y ago
I am that donkey banging it's head on the same post; when I see dates stored without TZ, I assume it's UTC and have done so for decades. It's turned out to almost never be true.
Eddy_Viscosity2 4y ago
> preferably UTC Yes! I have spent so much time detangling timestamps in data which used non-UTC times and non-standard formats. Like 06-10-01 WTF is that? Use UTC and then let the reader translate to local time on display.
dpcx 4y ago
My understanding is that you don't even want to store them as UTC and always store them in the timezone of the user/system that generated the data since you lose some information in the translation from local timezone to UTC - given that the timezone database changes, you may actually want to know that the record created in Fiji before 2022-10-28[1] was stored in DST so that it can be properly converted to the new Fiji time. [1]: https://data.iana.org/time-zones/tzdb/NEWS https://data.iana.org/time-zones/tzdb/NEWS
- bouke 4y ago
  This! People mistake the offset for the timezone, which is incorrect. The offset isn't very useful other than a guestimate of the originating timezone.
- cratermoon 4y ago
  Ideally store both, UTC and original local time, or at least the original TZ information. Time and timezone are a huge mess and one of the worst things to try deal with in programming.

csnweb 4y ago

Your sql query example doesn’t seem to actually need timezones. If all orders are stored in the correct UTC regardless where they come from. An order is always made at a specific point in time that is the same globally.

marcosdumay 4y ago

On your second example, you should be able to do that without storing the timezone of each event. In fact, odds are the timezone of each event will mislead you and break your analysis. On the email example, the timezone is there for debugging purposes. It's really not for knowing if the email was sent in the morning, and if you use it for that, you are almost guaranteed to get broken data. Of course, there are reasons to include timezones on your data. That's what you get right. But if you want the timezone of the data producer, you should store it for the producer, not for the data. (And then there is the issue that the SQL standard dictates that dates without a timezone are broken. So you shouldn't ever store them on a SQL database. But that one timezone is useless, if you need that information, you should store it elsewhere.)