Date arithmetic in SQL

Dates are text in SQLite, and the modifiers do the calendar work for you.

SELECT
    placed_at,
    DATE(placed_at, 'start of month') AS month_start,
    DATE(placed_at, 'start of month', '+1 month', '-1 day') AS month_end,
    DATE(placed_at, '+30 days') AS due,
    CAST(JULIANDAY('now') - JULIANDAY(placed_at) AS INTEGER) AS days_old,
    STRFTIME('%w', placed_at) AS weekday
FROM orders
ORDER BY placed_at;

How it works

1. Modifiers chain: 'start of month' then '+1 month' then '-1 day'.
2. JULIANDAY differences give you a span in days, fractions included.
3. STRFTIME formats, and %w gives the day of the week as 0 to 6.

Keywords and builtins used here

• AS
• BY
• CAST
• DATE
• FROM
• INTEGER
• ORDER
• SELECT

The run, in numbers

Lines

9

Characters to type

312

Tokens

65

Three-star pace

95 tpm

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

Type this snippet

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

← Previous Next →