Storing when things happened.
WHAT TO STORE
A proper temporal type, never text.
WHAT THE COMMON TYPES DIFFER IN
Whether a time zone is applied The range supported Storage size
WHAT A TIMESTAMP TYPE FREQUENTLY DOES IN MYSQL
Converts to and from the session time zone.
WHY THAT CAUSES CONFUSION
The same value reads differently depending on the connection.
WHAT A DATETIME TYPE DOES
Stores what you gave it, without conversion.
WHAT TO CHOOSE
One approach, applied consistently, and documented.
WHAT MOST APPLICATIONS SHOULD DO
Store a single universal standard, and convert at presentation.
WHY
It removes ambiguity and survives daylight saving entirely.
WHAT TO SET EXPLICITLY
The session time zone, rather than relying on the server's.
WHAT TO BE CAREFUL WITH
Comparing values stored under different assumptions Date arithmetic across daylight saving boundaries Dates near midnight, where a zone shift changes the day
WHY THAT LAST ONE MATTERS COMMERCIALLY
Reports bucketed by day disagree with what people expect.
WHAT TO INDEX
Date columns used in range conditions.
WHAT TO AVOID
Applying a function to a date column in a condition.
WHY
It prevents the index being used.
WHAT TO DO INSTEAD
Compare against a range of raw values.
