Python SQL Date & Time Formats: Timestamp Manipulation
Handling temporal data requires mastery of ISO 8601 formatting (`YYYY-MM-DD HH:MM:SS`), date extraction (`strftime`, `DATEPART`, `EXTRACT`), and interval arithmetic. Storing UTC timestamps and using database built-in temporal functions ensures robust analytics across global timezones.
"Date formatting is like international clock converters: keeping a standard UTC internal pulse prevents confusion when users in Tokyo, London, and San Francisco view event logs."
Deep Dive: How It Works
ISO 8601 Standard: `YYYY-MM-DD` guarantees lexical string sorting matches chronological time sorting.
Date Functions: `DATE()`, `DATETIME()`, `strftime()`, `NOW()`, `CURRENT_TIMESTAMP`.
Interval Calculations: Filtering by relative time windows (`WHERE created_at >= DATE('now', '-7 days')`).
Syntax Blueprint
-- SQLite
DATE('now', '-30 days')
strftime('%Y-%m', created_at)
-- PostgreSQL / MySQL
NOW() - INTERVAL '30 days'
DATE_TRUNC('month', created_at)Built-in functions for calculating relative dates and extracting time components.
Core Rules to Remember
Common Beginner Traps & How to Fix Them
Storing dates as non-standard localized strings (e.g. `MM/DD/YYYY` or `DD-MM-YYYY`).Why it happens: Matches local human habits, but breaks SQL sorting and range comparisons completely.
How to fix: Always use ISO 8601 `YYYY-MM-DD` standard format.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Extract Year with strftime
- Create table `events (id INTEGER PRIMARY KEY, name TEXT, event_date TEXT)`.
- Insert an event ("Hackathon", "2026-10-31").
- Query the event name and use `strftime('%Y', event_date) AS yr` to extract the year.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.