Dates, times and date arithmetic

Dates look simple until you compute with them. PostgreSQL and MySQL have real date types (date, timestamp, DATETIME); SQLite has none and stores dates as text in the ISO 8601 form YYYY-MM-DD, which its date functions read and write. Adding days is easy everywhere. Adding a month is not, because “one month after the 31st” has no single answer and the engines choose differently. Weekday numbers differ too: Sunday is 0 in one function, 1 in another and 7 in a third. This lesson stores, compares, shifts, measures and labels dates, including the Indian financial year, which starts in April.

All the date queries run in the browser’s SQLite 3.49.1 (sql.js 1.14.2), and the PostgreSQL 18 and MySQL 8.4 equivalents in the tables come from their manuals.

Storing dates

In SQLite the text form works because ISO 8601 writes the year, then the month, then the day, each with a fixed number of digits, so comparing two such strings character by character gives the same order as comparing the dates. That only holds for the exact form:

-- SQLite stores dates as text. Zero-padded year-month-day text sorts and compares in date order.
SELECT '2026-03-05' < '2026-11-01' AS padded_order,
       '2026-3-5'   < '2026-11-01' AS unpadded_order,
       date('2026-03-05')          AS padded,
       date('2026-3-5')            AS unpadded,
       date('05/03/2026')          AS day_first;

-- Build the ISO form from its parts when the data arrives in pieces.
SELECT printf('%04d-%02d-%02d', 2026, 3, 5) AS built;

┌──────────────┬────────────────┬────────────┬──────────┬───────────┐
│ padded_order │ unpadded_order │   padded   │ unpadded │ day_first │
├──────────────┼────────────────┼────────────┼──────────┼───────────┤
│ 1            │ 0              │ 2026-03-05 │          │           │
└──────────────┴────────────────┴────────────┴──────────┴───────────┘
┌────────────┐
│   built    │
├────────────┤
│ 2026-03-05 │
└────────────┘

'2026-3-5' sorts after '2026-11-01', because its sixth character, 3, is larger than 1, and date() does not recognise it at all (NULL). A day-first date such as '05/03/2026' is not understood either. Store every date as zero-padded YYYY-MM-DD text, convert other formats once when the data arrives, and printf('%04d-%02d-%02d', …) builds the form from separate numbers. SQLite’s date('now') gives today’s date in UTC; the next lesson explains why that is usually what you want.

Parts of a date

strftime(format, date) extracts parts, and a few of its codes matter for reports:

-- Parts of a date with strftime(). SQLite has no weekday names, so substr() picks one from a string.
CREATE TABLE days (d TEXT);
INSERT INTO days VALUES ('2026-03-29'), ('2026-03-30'), ('2026-12-31'), ('2027-01-01'), ('2027-01-04');

SELECT d,
       substr('SunMonTueWedThuFriSat', 1 + 3 * strftime('%w', d), 3) AS day,
       strftime('%w', d)     AS w,
       strftime('%u', d)     AS u,
       strftime('%j', d)     AS day_of_year,
       strftime('%G-W%V', d) AS iso_week
FROM days;

┌────────────┬─────┬───┬───┬─────────────┬──────────┐
│     d      │ day │ w │ u │ day_of_year │ iso_week │
├────────────┼─────┼───┼───┼─────────────┼──────────┤
│ 2026-03-29 │ Sun │ 0 │ 7 │ 088         │ 2026-W13 │
│ 2026-03-30 │ Mon │ 1 │ 1 │ 089         │ 2026-W14 │
│ 2026-12-31 │ Thu │ 4 │ 4 │ 365         │ 2026-W53 │
│ 2027-01-01 │ Fri │ 5 │ 5 │ 001         │ 2026-W53 │
│ 2027-01-04 │ Mon │ 1 │ 1 │ 004         │ 2027-W01 │
└────────────┴─────┴───┴───┴─────────────┴──────────┘

%w numbers the days from Sunday = 0 and %u from Monday = 1 to Sunday = 7, the ISO 8601 numbering. %j is the day of the year. %G-W%V is the ISO 8601 week: weeks start on Monday, and week 1 is the one that contains the year’s first Thursday, so the first days of a January can belong to week 53 of the year before, as the second-last row shows. SQLite has no weekday names, so the query picks three letters out of a string with substr().

Grouping on %u gives a weekly report that starts on Monday:

-- Orders per weekday, Monday first: group on the ISO day number (%u: Monday = 1, Sunday = 7).
CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_on TEXT);
INSERT INTO orders (placed_on) VALUES
  ('2026-03-02'), ('2026-03-02'), ('2026-03-04'), ('2026-03-06'), ('2026-03-07'),
  ('2026-03-07'), ('2026-03-08'), ('2026-03-08'), ('2026-03-08'), ('2026-03-09');

SELECT strftime('%u', placed_on)                                     AS iso_day,
       substr('MonTueWedThuFriSatSun', 3 * strftime('%u', placed_on) - 2, 3) AS day,
       count(*)                                                      AS orders
FROM orders
GROUP BY iso_day
ORDER BY iso_day;

┌─────────┬─────┬────────┐
│ iso_day │ day │ orders │
├─────────┼─────┼────────┤
│ 1       │ Mon │ 3      │
│ 3       │ Wed │ 1      │
│ 5       │ Fri │ 1      │
│ 6       │ Sat │ 2      │
│ 7       │ Sun │ 3      │
└─────────┴─────┴────────┘

Each engine numbers weekdays its own way, which is a classic source of off-by-one bugs when a query moves:

MySQL’s WEEKDAY() itself starts at Monday = 0, hence the + 1. One more trap hides in the last row. In SQLite, as in C, %V is the ISO week, but in MySQL’s DATE_FORMAT the same %V is a week that starts on Sunday; MySQL’s ISO week is %v with the year %x.

Adding days and months

SQLite shifts dates with modifiers such as '+10 days', '-1 month' or '+1 year', applied from left to right. Days are always clean. Months are where the engines part ways:

-- Adding a month. When the day does not exist in the new month, SQLite rolls over into the next one,
-- unless the 'floor' modifier asks for the last day of the new month instead.
CREATE TABLE starts (d TEXT);
INSERT INTO starts VALUES ('2026-01-15'), ('2026-01-31'), ('2026-03-31'), ('2026-05-31');

SELECT d,
       date(d, '+1 month')          AS plus_one_month,
       date(d, '+1 month', 'floor') AS with_floor
FROM starts;

-- A year after a leap day, and the first and last day of a month.
SELECT date('2024-02-29', '+1 year')                             AS after_leap_day,
       date('2024-02-29', '+1 year', 'floor')                    AS leap_floor,
       date('2026-02-17', 'start of month')                      AS first_day,
       date('2026-02-17', 'start of month', '+1 month', '-1 day') AS last_day;

┌────────────┬────────────────┬────────────┐
│     d      │ plus_one_month │ with_floor │
├────────────┼────────────────┼────────────┤
│ 2026-01-15 │ 2026-02-15     │ 2026-02-15 │
│ 2026-01-31 │ 2026-03-03     │ 2026-02-28 │
│ 2026-03-31 │ 2026-05-01     │ 2026-04-30 │
│ 2026-05-31 │ 2026-07-01     │ 2026-06-30 │
└────────────┴────────────────┴────────────┘
┌────────────────┬────────────┬────────────┬────────────┐
│ after_leap_day │ leap_floor │ first_day  │  last_day  │
├────────────────┼────────────┼────────────┼────────────┤
│ 2025-03-01     │ 2025-02-28 │ 2026-02-01 │ 2026-02-28 │
└────────────────┴────────────┴────────────┴────────────┘

One month after 2026-01-31 would need a 31st day in February, which does not exist, so SQLite counts on into March and returns 2026-03-03. The floor modifier, new in SQLite 3.46.0, resolves the same case to the last day of the new month instead, 2026-02-28; ceiling is the default. A year after a leap day behaves the same way. The second query shows the first and the last day of a month, built from start of month, +1 month and -1 day.

PostgreSQL and MySQL always choose the last day of the shorter month: in PostgreSQL date '2026-01-31' + interval '1 month' is 2026-02-28 00:00:00, and in MySQL DATE_ADD('2026-01-31', INTERVAL 1 MONTH) is 2026-02-28. So the same “due one month after the invoice” query can give a different date in SQLite. Decide the rule your business wants for the 29th to the 31st, write it down, and test it on those days. For the last day of a month, MySQL has LAST_DAY(d); in PostgreSQL write (date_trunc('month', d) + interval '1 month' - interval '1 day')::date.

Differences between dates

-- How long a delivery took: calendar days between the dates, and exact hours from Unix seconds.
CREATE TABLE deliveries (id INTEGER, placed_at TEXT, delivered_at TEXT);
INSERT INTO deliveries VALUES
  (1, '2026-03-30 09:15:00', '2026-03-31 18:45:00'),
  (2, '2026-03-31 22:10:00', '2026-04-02 08:40:00');

SELECT id,
       CAST(julianday(date(delivered_at)) - julianday(date(placed_at)) AS INTEGER) AS calendar_days,
       (unixepoch(delivered_at) - unixepoch(placed_at)) / 3600.0                    AS hours,
       timediff(delivered_at, placed_at)                                            AS as_text
FROM deliveries;

┌────┬───────────────┬───────┬──────────────────────────┐
│ id │ calendar_days │ hours │         as_text          │
├────┼───────────────┼───────┼──────────────────────────┤
│ 1  │ 1             │ 33.5  │ +0000-00-01 09:30:00.000 │
│ 2  │ 2             │ 34.5  │ +0000-00-01 10:30:00.000 │
└────┴───────────────┴───────┴──────────────────────────┘

julianday() turns a date into a day number, so the difference of two of them counts days; taking date() first counts calendar days, which is what “delivered the next day” means. unixepoch() gives whole seconds, so dividing the difference by 3600.0 gives exact hours, 33.5 and 34.5 here. timediff(), new in SQLite 3.43.0, writes the gap as years, months, days and a time, which reads well but is text you cannot add up.

In PostgreSQL, subtracting one date from another gives an integer number of days, subtracting timestamps gives an interval, and age() expresses a gap in years, months and days. MySQL has DATEDIFF(later, earlier) for days and TIMESTAMPDIFF(HOUR, earlier, later) for any unit, which counts whole units only. Mind the argument orders: MySQL’s DATEDIFF puts the later date first and TIMESTAMPDIFF puts it last.

Financial years and quarters

Moving a date back by three months turns April into January, so the year of the moved date is the year in which its financial year began:

-- A financial year that starts in April: move the date back 3 months, and its year is the start year.
CREATE TABLE orders (id INTEGER, ordered_on TEXT);
INSERT INTO orders VALUES (1, '2026-03-31'), (2, '2026-04-01'), (3, '2026-12-31'), (4, '2027-01-01');

SELECT id,
       ordered_on,
       CAST(strftime('%Y', ordered_on, '-3 months') AS INTEGER) AS fy_start,
       strftime('%m', ordered_on, '-3 months')                  AS shifted_month,
       strftime('%m', ordered_on, '-3 months', 'floor')         AS with_floor
FROM orders;

┌────┬────────────┬──────────┬───────────────┬────────────┐
│ id │ ordered_on │ fy_start │ shifted_month │ with_floor │
├────┼────────────┼──────────┼───────────────┼────────────┤
│ 1  │ 2026-03-31 │ 2025     │ 12            │ 12         │
│ 2  │ 2026-04-01 │ 2026     │ 01            │ 01         │
│ 3  │ 2026-12-31 │ 2026     │ 10            │ 09         │
│ 4  │ 2027-01-01 │ 2026     │ 10            │ 10         │
└────┴────────────┴──────────┴───────────────┴────────────┘

fy_start is right for all four orders: the last day of March belongs to the year that began a year earlier, and the first of April starts a new one. The shifted_month column shows the catch. 2026-12-31 moved back three months would need a 31st day in September, which rolls over to the first of October, so the month says 10 where 9 was meant. The year is never affected, because December has 31 days, but anything computed from the shifted month, such as a quarter, would be wrong for that day. Adding 'floor' after the shift fixes it, and so does working from the original month number. The exercise below builds both labels.

Formatting dates for people

Keep dates as ISO text or a date type for storing, sorting and comparing, and format them only for display. In SQLite strftime('%d/%m/%Y', d) writes the day first, as most Indian forms do; PostgreSQL uses to_char(d, 'DD/MM/YYYY') and MySQL DATE_FORMAT(d, '%d/%m/%Y'). A formatted date is text again: sorted, '01/04/2026' comes before '31/03/2026'.

Your turn

-- fy: 'FY' || start year || '-' || last two digits of the end year (April to March);
-- quarter: Q1 = April-June, Q2 = July-September, Q3 = October-December, Q4 = January-March.
SELECT id,
       'FY' || strftime('%Y', ordered_on) AS fy,
       'Q1' AS quarter
FROM orders;

# Sample tests: each one builds a small orders table, runs your query on it and compares the rows it returns (in any
# order).
tests:
  - name: puts the last day of March and the first day of April in different years
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, ordered_on TEXT NOT NULL);
      INSERT INTO orders VALUES (1, '2026-03-31'), (2, '2026-04-01'), (3, '2026-06-30'), (4, '2026-07-01');
    expected:
      columns: [id, fy, quarter]
      rows:
        - [1, 'FY2025-26', 'Q4']
        - [2, 'FY2026-27', 'Q1']
        - [3, 'FY2026-27', 'Q1']
        - [4, 'FY2026-27', 'Q2']
  - name: keeps the turn of the calendar year inside one financial year
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, ordered_on TEXT NOT NULL);
      INSERT INTO orders VALUES (1, '2026-09-30'), (2, '2026-10-01'), (3, '2026-12-31'), (4, '2027-01-01'), (5, '2027-03-31');
    expected:
      columns: [id, fy, quarter]
      rows:
        - [1, 'FY2026-27', 'Q2']
        - [2, 'FY2026-27', 'Q3']
        - [3, 'FY2026-27', 'Q3']
        - [4, 'FY2026-27', 'Q4']
        - [5, 'FY2026-27', 'Q4']
  - name: handles the 31st of a month and a year that ends in a new century
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, ordered_on TEXT NOT NULL);
      INSERT INTO orders VALUES (1, '2026-08-31'), (2, '2099-05-31'), (3, '2100-02-28'), (4, '2024-02-29');
    expected:
      columns: [id, fy, quarter]
      rows:
        - [1, 'FY2026-27', 'Q2']
        - [2, 'FY2099-00', 'Q1']
        - [3, 'FY2099-00', 'Q4']
        - [4, 'FY2023-24', 'Q4']

Interview questions

Fresher · Why do SQLite queries store dates as YYYY-MM-DD text, and what goes wrong with other formats?

SQLite has no date type, so a date is stored as text, as a Julian day number or as Unix seconds, and its date functions understand all three. ISO 8601 text such as 2026-03-05 is the readable choice that still behaves like a date: the year comes first, then the month, then the day, each zero-padded, so ordinary text comparison sorts it in date order and BETWEEN, MIN() and ORDER BY work. A day-first form such as 05/03/2026 sorts by day before month, and an unpadded one such as 2026-3-5 sorts after 2026-11-01; date() returns NULL for both. Convert other formats once, when the data is loaded, and format dates for people only at the end.

Key takeaways

• PostgreSQL and MySQL have date types; SQLite stores dates as zero-padded YYYY-MM-DD text, which sorts correctly only in exactly that form.
• strftime('%w') numbers Sunday 0, %u numbers Monday 1 to Sunday 7, and %G-W%V is the ISO week in SQLite, but %V means a Sunday week in MySQL.
• One month after the 31st is the last day of the next month in PostgreSQL and MySQL, but rolls into the month after in SQLite unless you add floor.
• Count days with julianday() differences and exact hours with unixepoch() differences; timediff() is for reading.
• A financial year that starts in April is the year of the date moved back three months; take the quarter from the original month.
• Format dates only for display, because formatted dates are text that sorts by its first characters.