Consistency between application and database.
WHAT GOES WRONG
The application, the database and the server disagree.
WHAT THAT PRODUCES
Timestamps shifted by hours Reports bucketed into the wrong day Scheduled work running at unexpected times
WHAT TO ESTABLISH
One standard, applied everywhere, converted only at presentation.
WHAT THE SERVER SHOULD BE SET TO
A single universal standard.
WHAT THE DATABASE SESSION SHOULD BE SET TO
The same, explicitly, rather than inherited.
WHY EXPLICITLY
Inherited settings differ between environments.
WHAT THE APPLICATION SHOULD DO
Work internally in that standard Convert for display, per user
WHAT TO STORE FOR A USER
Their zone, so display can be correct.
WHAT TO BE CAREFUL WITH
Date-only values, which have no zone and should not be converted Recurring schedules, which people expect in local time
WHY RECURRING SCHEDULES ARE DIFFERENT
A daily task at a local time shifts in universal terms across daylight saving.
WHAT TO STORE FOR THOSE
The local time and the zone, computing occurrences.
WHAT TO TEST
Behaviour across a daylight saving transition.
WHY
It is where these faults surface, twice a year.
WHAT TO CHECK IN REPORTS
Which zone the day boundaries use.
WHAT TO STATE ON THE REPORT
That zone, explicitly.
