3 ms·
Corner cases involving time are endless (you can use this joke for free): - Can your code handle a datetime if the logic expects midnight times? - What happen
by lordnacho 11y ago
Corner cases involving time are endless (you can use this joke for free):
- Can your code handle a datetime if the logic expects midnight times?
- What happens if you have a duration that starts on November 1 and ends on November 2, and the user decides to change the end time by an hour? Daylight savings?
- If I fly from Denver to Arizona in December, how does it deal with the time zones? Oh great, you need a table.
- What's the next business day? Another table, this time with holidays.
- When does an all day event start? Midnight? Midnight where?
- Pre-gregorian time? Warhammer 40K time?
Then there's platform based problems:
- Is there an easy way to do time arithmetic?
- Is there an easy way to extract time parts? (iOS I'm looking at you!)
- Are time zone aware times different to UTC times?
- greggyb 11y agoBecause of questions like these, the widest table in every dimensional model I've ever built has been the date dimension.
- rjbwork 11y agoYeeeep. Can be tens to hundreds of columns depending on the requirements. Fiscal years, UTC+14, holidays, etc, etc, etc.
- greggyb 11y agoWork days, index field for every granularity of time (for fiscal calendars that don't align to calendar granularities), day number of every time span, display fields stored in text ("I don't care about the intricacies of date formatting in locales, just make it always look like this "), abbreviations of display fields. How about the retail requirement of capturing store open/close status for year on year comparisons? Date dimension becomes Store_Date dimension, with indicators for what to include/exclude in comparison totals? I give trainings on dimensional modeling and query design/optimization. The vast majority of my examples are for dates, for two reasons. 1. Everyone needs some form of date dimension. 2. If you can solve date problems, you can apply any other logic you want trivially. 2 is not 100% true, but sometimes it seems that way.