2 ms·

My favourite TZ bug was where thanks to a timezone bug some (but not all) events were showing up a day early after our etl. It turned out that a combination of

My favourite TZ bug was where thanks to a timezone bug some (but not all) events were showing up a day early after our etl. It turned out that a combination of several seemingly logical steps we had managed to end up in an insane place. If you just want the tldr, dates are not times, don't store dates as times or you can be bitten by timezone bugs. Also dates don't have a timezone, don't store them as something that has a timezone or you can make mistakes. So: 1)We decided to store all timestamps as UTC. A bunch of analysis we were doing was temporal so it was really important to be able to compare timestamps coming from different source systems (with different native timestamps) accurately. 2)Some events were things occurring on specific dates rather than at a particular time. No problem, dates don't have a timezone. We can't possibly be bitten by a timezone bug on dates. 3)One of our serialization formats didn't have a date type so we decided to store dates as times with a timestamp of midnight (This is ancient Pig-era hadoop so you're probably talking an earlyish avro format but I can't remember). You can always just chop the time off to turn them back to a date and nothing can possibly go wrong. 4)One of the event source systems used Europe/London timezone (ie daylight savings in the summer, GMT in the winter) as the system tz rather than UTC. No big deal right, we're doing consistent UTC conversion so this couldn't possibly hurt anything. Bingo! Some of the date based events appeared one day earlier than they should. Do you see why? This was an absolute bastard to debug because it was in the middle of a somewhat flakey data pipeline taht used to take >9hrs to run but basically what was happening is during daylight savings months these events were being converted from 00:00 1 Aug BST (say) to 23:00 31 July UTC, so when serialised once you chopped off the time the event would end up one day early. It wasn't all dates because not all days are during daylight savings and events that had an actual time (not a date that had been turned into a time) were of course fine. Also date events that were recorded on a system that used UTC as its system timezone were also fine.

superjan 6y ago
Many people use datetime data types for dates, some unknowingly, some don’t care and some because they don’t see an alternative. Its quite strange there is rarely language/library support for true, not timezone dependent dates.
- michael1999 6y ago
  JodaTime (and use-310) does in right. Most do it wrong.
wongarsu 6y ago
> dates are not times, don't store dates as times They kind of are, or rather they are time intervals. When people say 25th September they most often mean the interval from 00:00:00 on the 25th until 24:00:00 on the 25th (or 23:59:(59|60) if you don't like one moment in time belonging to two days). This is obviously timezone dependend, at all times some timezones are on a different date than other timezones.
- michael1999 6y ago
  Often they don’t. Time-to-business-date is a business or legal rule, not a celestial one. The “day” often ends at 5pm. Trading systems may have eod at 2. If you deposit a cheque into a machine at 5:30pm on Thursday, that is posted Friday. A trade at 3pm is next-day. Heaven forbid you have a global operation.
  - seanhunter 6y ago
    Yeah this is my experience too. Often there is a time or times associated with a date but they can be difficult/impossible to deduce and many intuitive answers are wrong or riddled with important exceptions.

superjan 6y ago

Many people use datetime data types for dates, some unknowingly, some don’t care and some because they don’t see an alternative. Its quite strange there is rarely language/library support for true, not timezone dependent dates.

michael1999 6y ago
JodaTime (and use-310) does in right. Most do it wrong.

wongarsu 6y ago

> dates are not times, don't store dates as times They kind of are, or rather they are time intervals. When people say 25th September they most often mean the interval from 00:00:00 on the 25th until 24:00:00 on the 25th (or 23:59:(59|60) if you don't like one moment in time belonging to two days). This is obviously timezone dependend, at all times some timezones are on a different date than other timezones.

michael1999 6y ago
Often they don’t. Time-to-business-date is a business or legal rule, not a celestial one. The “day” often ends at 5pm. Trading systems may have eod at 2. If you deposit a cheque into a machine at 5:30pm on Thursday, that is posted Friday. A trade at 3pm is next-day. Heaven forbid you have a global operation.
- seanhunter 6y ago
  Yeah this is my experience too. Often there is a time or times associated with a date but they can be difficult/impossible to deduce and many intuitive answers are wrong or riddled with important exceptions.