Dates and Times in SQLite

Dates and Times in SQLite

In the warehouse this differs. SQLite has no dedicated DATE or TIMESTAMP type. Dates live as TEXT in ISO-8601 ('2026-03-01' or '2026-03-01T09:14:00Z'), and you manipulate them with the date(), datetime(), and strftime() functions. Real warehouses have native DATE/TIMESTAMP types and different function names: Postgres uses date_trunc('month', ts) and EXTRACT(YEAR FROM ts); BigQuery uses DATE_TRUNC/FORMAT_DATE; Snowflake uses DATE_TRUNC/TO_CHAR. The concepts below (truncate, extract a part, filter a window) transfer everywhere; the exact syntax does not. Because ISO-8601 text also sorts and compares chronologically as plain strings, a lot of date filtering needs no functions at all.

The core functions (SQLite)

• date(ts) truncates a timestamp to the day: date('2026-03-01T09:14:00Z') → '2026-03-01'.
• strftime(fmt, ts) formats or extracts. strftime('%Y-%m', ts) → '2026-03' (year-month); %Y year, %m month, %d day, %w day-of-week (0=Sunday).

Worked example (extract year-month):

SELECT
  order_id,
  strftime('%Y-%m', order_ts) AS order_year_month
FROM orders;

Anatomy.

strftime( '%Y-%m' , order_ts )
          └ format ┘  └ ISO text timestamp ┘
date( order_ts )              -> 'YYYY-MM-DD'  (day truncation)
order_ts >= '2026-01-01'      -> string compare = chronological filter

Filtering a window. Because ISO text sorts correctly, a rolling window is just a string range: WHERE order_ts >= '2026-01-01' AND order_ts < '2026-04-01'. Prefer half-open ranges (>= start AND < next_start) over BETWEEN for timestamps, so you don't accidentally include or exclude the boundary instant.

Pitfall. strftime returns text, so strftime('%m', ts) is '03' (string), not the number 3. Cast if you need arithmetic. And strftime/date only work on valid ISO-8601 strings; a malformed date like '03/01/2026' returns NULL silently. Validate/standardize date text before relying on these functions.

Recap. In SQLite dates are ISO text: date() truncates to day, strftime() extracts/formats, and plain string comparison filters windows, but the function names change in every real warehouse, so lean on the portable concepts.