Lesson 45 of 61

5 mins read

SQL Date & Time Formats: Timestamp Manipulation

In Plain English

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.

The Mental Model

"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

Always Store UTC: Persist all timestamps in UTC and convert to user local time only at presentation layer.

ISO Format Precision: Standard `YYYY-MM-DD` formatting prevents ambiguous month-day parsing errors.

Date Arithmetic: Use native date modifiers rather than manual unix timestamp calculations.

Common Beginner Traps & How to Fix Them

Mistake: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.

Pro Tip: In SQLite, `strftime('%Y-%m', order_date)` allows grouping by year-month effortlessly for monthly revenue reports.

Live Interactive Example

Hit Run Code to see it live

Extracting Year and Month with strftime

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: strftime extracted the 4-digit year and 2-digit month strings.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

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.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.