The commonest source of subtle errors.
WHAT TO ESTABLISH FIRST
What time zone data is stored in, and whether it is consistent.
WHAT THE TYPES DIFFER IN
Whether a time zone is recorded Whether conversion happens on storage or retrieval
WHY THAT MATTERS
The same value can mean different instants depending on the type.
WHAT TO PREFER FOR STORAGE
A type recording the instant unambiguously.
WHAT TO DO FOR REPORTING
Convert to the required time zone at query time.
WHAT TRUNCATION DOES
Reduces a timestamp to a day, month or hour.
WHAT TO BE CAREFUL WITH
Week definitions, which differ between systems and locales Month boundaries in time zones with offsets Daylight saving transitions, where they apply
WHAT DATE RANGES SHOULD USE
Greater than or equal to the start, and less than the next start.
WHY NOT BETWEEN
Between includes the endpoint, which with timestamps excludes most of the final day.
WHAT THAT CAUSES
Reports missing nearly a day of data, unnoticed.
WHAT A DATE DIMENSION PROVIDES
A table of dates with useful attributes: weekday, month, quarter, holiday.
WHY IT IS WORTH BUILDING
It removes date arithmetic from every query, and allows joining rather than computing.
