SQLite Date Functions: Dates, Months and Unix Timestamps

Convert Unix seconds explicitly

The unixepoch modifier tells SQLite that the input is seconds since the Unix epoch. The example returns 1970-01-01 00:00:00. Milliseconds need conversion to seconds first.

SELECT datetime(0,'unixepoch') AS utc_time;

Find the beginning of the next month

Starting at the first of the month avoids ambiguous month-end arithmetic. This example returns 2026-02-01. Modifiers apply in order.

SELECT date('2026-01-31','start of month','+1 month') AS next_month;

Group by calendar month

Use a consistent timezone convention for stored timestamps. The example yields 2026-01 with count 2 and 2026-02 with count 1.

WITH visits(ts) AS (VALUES('2026-01-01 10:00:00'),('2026-01-31 23:00:00'),('2026-02-01 00:00:00')) SELECT strftime('%Y-%m',ts) AS month,COUNT(*) FROM visits GROUP BY month ORDER BY month;

Choose boundaries deliberately

For timestamp filtering, prefer a start-inclusive and end-exclusive interval such as ts >= start AND ts < next_month, using a consistent sortable representation. Decide whether reporting follows UTC or a business timezone; localtime follows the host environment and is not a named-timezone reporting system.