SQL Dates
Every database has a small zoo of date/time types and a long list of functions for working with them. Pick the right type first; the queries get simpler.
The four common types
| Type | Stores |
|---|---|
DATE | Year-month-day. No time, no zone. |
TIME | Hour-minute-second. |
DATETIME / TIMESTAMP | Date + time. TIMESTAMP often stores UTC + zone info. |
INTERVAL (PG/Oracle) | A duration, e.g. 3 days 4 hours. |
Reading the current time
| DB | Now | Today |
|---|---|---|
| MySQL | NOW() | CURDATE() |
| PostgreSQL | NOW() / CURRENT_TIMESTAMP | CURRENT_DATE |
| SQL Server | GETDATE() / SYSDATETIME() | CAST(GETDATE() AS DATE) |
| SQLite | datetime('now') | date('now') |
Filtering by date range
Use half-open ranges to handle datetime precision safely:
SQL
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';
Time zones
Best practice: store everything in UTC and convert at the edges. PostgreSQL's TIMESTAMP WITH TIME ZONE auto-converts; MySQL's TIMESTAMP stores UTC internally; DATETIME in MySQL ignores zones entirely.
Tip: Never store dates as strings. The day you need to "find rows from last 7 days" will arrive, and string comparison will quietly break.
Example
Example
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';Try it Yourself »
Exercise
Best-practice storage zone for timestamps.
Store everything as
.
Three letters; the universal coordinated time.
Discussion
Loading…