Dates and times in SQL

Truncating, formatting and shifting timestamps to group by period.

SELECT
    DATE(occurred_at) AS day,
    STRFTIME('%Y-%W', occurred_at) AS week,
    COUNT(*) AS events
FROM events
WHERE occurred_at >= DATE('now', '-30 days')
GROUP BY day, week
ORDER BY day DESC;

How it works

DATE drops the time part so rows group by day.
STRFTIME formats a timestamp into a month or week key.
Date arithmetic uses modifiers like -7 days.

Keywords and builtins used here

AS
BY
COUNT
DATE
DESC
FROM
GROUP
ORDER
SELECT
WHERE
day

The run, in numbers

Lines: 8
Characters to type: 186
Tokens: 45
Three-star pace: 95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 28 seconds.

Type this snippet

Step 6 of 7 in Expressions, step 21 of 24 in Analytics & reporting.

← Previous Next →

Dates and times in other languages