SQL dates and times cheat sheet

Truncate, extract, add intervals and bucket by month — SQLite syntax with Postgres equivalents.

Extract and truncate

Month bucket

SELECT strftime('%Y-%m', order_date) AS month, COUNT(*)
FROM orders GROUP BY month;

Postgres: DATE_TRUNC('month', order_date)

Day of week

SELECT order_date, strftime('%w', order_date) AS dow FROM orders;

Postgres: EXTRACT(DOW FROM order_date)

Add days

SELECT order_date, date(order_date, '+30 days') AS due FROM orders;

Postgres: order_date + INTERVAL '30 days'

Days between

SELECT julianday('2023-09-28') - julianday('2023-06-01') AS days;

Postgres: date2 - date1

sql · editable

loading editor…

Ready for interview-level questions? Pro unlocks the full company problem set.