MySmartCoPilot
Ask Passes Your pass 7 days left 1 day left Last day

Your country

Tools that support it use your country for local currency, number formats, units and paper size. Your choice is saved only in this browser.

SQL (PostgreSQL, MySQL, SQLite)  Module 19 – SQL for data analysis

Time buckets in SQL: weeks, months, fiscal years

Group timestamps into hours, ISO weeks, months and fiscal quarters, fill empty buckets from a calendar and compare periods in PostgreSQL, MySQL and SQLite.

  • Intermediate
  • 30 minutes
  • Examples run with sql.js 1.14.2
  • By MySmartCoPilot
Free: From a timestamp to the start of its buckets · The same orders on two clocks · Your turn. With a pass: 3 in-depth parts and 3 interview answers, about 21 minutes.

What you will learn

  • Group timestamps into hours, days, ISO weeks, months and quarters of the financial year
  • Convert stored UTC moments to the business's own clock, such as India Standard Time, before bucketing them
  • Build a calendar that keeps the buckets without events, with 0 instead of a missing row
  • Compare periods month to date, year over year and with the same weekday a week earlier

Before you start

What you’ll learn · Before you start

What you will learn

  • Group timestamps into hours, days, ISO weeks, months and quarters of the financial year
  • Convert stored UTC moments to the business's own clock, such as India Standard Time, before bucketing them
  • Build a calendar that keeps the buckets without events, with 0 instead of a missing row
  • Compare periods month to date, year over year and with the same weekday a week earlier

Before you start

On this page

A time bucket is a stretch of the calendar that an event falls into: an hour, a day, a week, a month or a quarter of a financial year. To bucket a column of timestamps, turn each one into the first moment of its bucket and group by that value: date_trunc('month', placed_at) in PostgreSQL, DATE_FORMAT(placed_at, '%Y-%m-01') in MySQL and date(placed_at, 'start of month') in SQLite. Every order of a month then carries the same month start, and GROUP BY returns one row per month. Three choices decide whether such a report is right, and each belongs in its definition: the clock (UTC, or the business’s own time zone), the week (an ISO week from Monday, or one from Sunday) and the year (the calendar year, or a financial year that starts in April). Time buckets without events vanish from a GROUP BY, and calendars put them back; comparisons then read one bucket against another.

Every query here runs in your browser on SQLite 3.49.1 inside sql.js 1.14.2. Where PostgreSQL 18 or MySQL 8.4 needs another function, the text and the table name it.

From a timestamp to the start of its buckets

Six orders of an online shop are stored in UTC. The query moves each one to India Standard Time (IST), UTC+05:30, and then truncates it four ways:

Six orders and the start of each one's buckets SQL · buckets_per_order.sql 
-- Six orders of an online shop. placed_at is stored in UTC, the way most systems store moments.
CREATE TABLE orders (
  id        INTEGER PRIMARY KEY,
  placed_at TEXT    NOT NULL,  -- 'YYYY-MM-DD HH:MM:SS' in UTC
  amount    INTEGER NOT NULL   -- rupees
);
INSERT INTO orders VALUES
  (1, '2026-03-29 20:15:00',  450),
  (2, '2026-03-31 18:40:00', 1200),
  (3, '2026-04-04 07:05:00',  380),
  (4, '2026-04-05 18:50:00', 2250),
  (5, '2026-06-30 19:30:00',  640),
  (6, '2026-12-31 20:00:00',  999);

-- Move each moment to India's clock (UTC+05:30) first, then truncate it to the first
-- moment of its hour, its ISO week (which starts on Monday), its month and its quarter of
-- the financial year that starts in April.
WITH ist AS (
  SELECT id, datetime(placed_at, '+05:30') AS t FROM orders
),
parts AS (
  SELECT id, t,
         cast(strftime('%Y', t) AS INTEGER) AS y,
         cast(strftime('%m', t) AS INTEGER) AS m
  FROM ist
)
SELECT id,
       strftime('%Y-%m-%d %H:00', t)     AS hour,
       date(t, '-6 days', 'weekday 1')   AS week,
       date(t, 'start of month')         AS month,
       printf('FY%d-%02d Q%d', y - (m < 4), (y - (m < 4) + 1) % 100,
              (m + 8) % 12 / 3 + 1)      AS fy_quarter
FROM parts
ORDER BY id;

Output

┌────┬──────────────────┬────────────┬────────────┬──────────────┐
│ id │       hour       │    week    │   month    │  fy_quarter  │
├────┼──────────────────┼────────────┼────────────┼──────────────┤
│ 1  │ 2026-03-30 01:00 │ 2026-03-30 │ 2026-03-01 │ FY2025-26 Q4 │
│ 2  │ 2026-04-01 00:00 │ 2026-03-30 │ 2026-04-01 │ FY2026-27 Q1 │
│ 3  │ 2026-04-04 12:00 │ 2026-03-30 │ 2026-04-01 │ FY2026-27 Q1 │
│ 4  │ 2026-04-06 00:00 │ 2026-04-06 │ 2026-04-01 │ FY2026-27 Q1 │
│ 5  │ 2026-07-01 01:00 │ 2026-06-29 │ 2026-07-01 │ FY2026-27 Q2 │
│ 6  │ 2027-01-01 01:00 │ 2026-12-28 │ 2027-01-01 │ FY2026-27 Q4 │
└────┴──────────────────┴────────────┴────────────┴──────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < buckets_per_order.sql

  • strftime('%Y-%m-%d %H:00', t) keeps the date and the hour and drops the minutes, so 00:10 becomes 00:00.
  • date(t, '-6 days', 'weekday 1') goes back six days, then forward to the next Monday: the Monday that starts the ISO week of t, since ISO weeks run from Monday to Sunday.
  • date(t, 'start of month') gives the first day of the month.
  • The financial-year label comes from the month. A month before April belongs to the financial year that began the April before, and (m + 8) % 12 / 3 + 1 numbers April to June as quarter 1 and January to March as quarter 4.
An order stored at 18:40 UTC on the last day of March is 00:10 IST in April, then truncated to its hour, week, month and quarter.Stored in UTC:18:40 on the last day of MarchOn India's clock, UTC+05:30:00:10 on the first day of AprilTruncated to the start of each bucketGROUP BY a bucket start:one row per bucketHour: from 00:00 that dayISO week: from the Mondaytwo days earlier, in MarchMonth: AprilFinancial-year quarter:Q1 of FY2026-27+05:30 firsttruncategroup

Move to the business's clock first, then truncate to the start of the bucket

Text description of the diagram

The diagram follows order 2 of the first example from top to bottom.

1. The order is stored in UTC, at 18:40 on the last day of March. 2. Adding 5 hours 30 minutes puts it on India's clock: 00:10 on the first day of April, already the next day. 3. That local time is truncated to the start of each bucket: the hour that starts at 00:00, the ISO week that began on the Monday two days earlier, still in March, the month of April, and the first quarter of the financial year 2026-27. 4. A report groups by one of those starts and returns one row per bucket.

A bucket start is an ordinary value: it sorts, it joins, and any number of events share it. Group by the start rather than by a label such as Apr or W14, which sorts in the wrong order or repeats every year, and print the label from the start at the end of the query.

The same orders on two clocks

Where a bucket ends depends on the clock, so the same six orders fall into different quarters on UTC dates and on IST dates:

Revenue per financial-year quarter, on UTC and on IST SQL · quarters_two_clocks.sql 
-- The same six orders as before, stored in UTC.
CREATE TABLE orders (
  id        INTEGER PRIMARY KEY,
  placed_at TEXT    NOT NULL,  -- 'YYYY-MM-DD HH:MM:SS' in UTC
  amount    INTEGER NOT NULL   -- rupees
);
INSERT INTO orders VALUES
  (1, '2026-03-29 20:15:00',  450),
  (2, '2026-03-31 18:40:00', 1200),
  (3, '2026-04-04 07:05:00',  380),
  (4, '2026-04-05 18:50:00', 2250),
  (5, '2026-06-30 19:30:00',  640),
  (6, '2026-12-31 20:00:00',  999);

-- Revenue per quarter of the financial year, bucketed once on the UTC clock and once on
-- India's clock, side by side.
WITH both_clocks AS (
  SELECT 'utc' AS clock, placed_at AS t, amount FROM orders
  UNION ALL
  SELECT 'ist', datetime(placed_at, '+05:30'), amount FROM orders
),
labelled AS (
  SELECT clock, amount,
         cast(strftime('%Y', t) AS INTEGER)
           - (cast(strftime('%m', t) AS INTEGER) < 4)         AS fy,
         (cast(strftime('%m', t) AS INTEGER) + 8) % 12 / 3 + 1 AS q
  FROM both_clocks
)
SELECT printf('FY%d-%02d Q%d', fy, (fy + 1) % 100, q)   AS fy_quarter,
       sum(CASE WHEN clock = 'utc' THEN amount ELSE 0 END) AS utc_revenue,
       sum(CASE WHEN clock = 'ist' THEN amount ELSE 0 END) AS ist_revenue
FROM labelled
GROUP BY fy, q
ORDER BY fy, q;

Output

┌──────────────┬─────────────┬─────────────┐
│  fy_quarter  │ utc_revenue │ ist_revenue │
├──────────────┼─────────────┼─────────────┤
│ FY2025-26 Q4 │ 1650        │ 450         │
│ FY2026-27 Q1 │ 3270        │ 3830        │
│ FY2026-27 Q2 │ 0           │ 640         │
│ FY2026-27 Q3 │ 999         │ 0           │
│ FY2026-27 Q4 │ 0           │ 999         │
└──────────────┴─────────────┴─────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < quarters_two_clocks.sql

Three of the six orders change quarter. Order 2 was placed ten minutes after midnight on the first day of April in India, but in UTC it is still the evening of the last day of March, so the UTC report gives its 1,200 rupees to the previous financial year. Midnight in India is 18:30 UTC: every order placed from then until the end of the UTC day belongs to the next day on India’s calendar, and sometimes to the next month, quarter or year. A business that reports on IST converts before it truncates, never after.

The rest of the lesson builds on five more ideas, each in a sentence:

  • ISO weeks start on Monday, and week 1 of a year is the week that holds its first Thursday.
  • Sunday weeks start a day earlier, so a weekly report has to say which week it means.
  • Custom widths such as 15 minutes are whole multiples of the width counted from a fixed origin.
  • A calendar lists every bucket and is joined to the events, so empty buckets show 0 instead of vanishing.
  • Period comparisons set each bucket beside the one a month, a year or a week before it.
The start of a bucket in PostgreSQL, MySQL and SQLite
Criterion PostgreSQL 18MySQL 8.4SQLite 3.49
Hour date_trunc('hour', t)DATE_FORMAT(t, '%Y-%m-%d %H:00:00')strftime('%Y-%m-%d %H:00:00', t)
Day date_trunc('day', t), or t::dateDATE(t)date(t)
ISO week, from Monday date_trunc('week', t)DATE(t) - INTERVAL WEEKDAY(t) DAYdate(t, '-6 days', 'weekday 1')
Month date_trunc('month', t)DATE_FORMAT(t, '%Y-%m-01')date(t, 'start of month')
Calendar quarter date_trunc('quarter', t)MAKEDATE(YEAR(t), 1) + INTERVAL QUARTER(t) - 1 QUARTERA CASE on the month
UTC moment to IST first t AT TIME ZONE 'Asia/Kolkata' (a timestamptz t)CONVERT_TZ(t, '+00:00', '+05:30')datetime(t, '+05:30')

None of the three engines knows a financial-year quarter: compute it from the month as above, or store it once in a calendar table.

Week Number Calculator Look up the ISO week of any date, with its Monday and Sunday, to check the weeks a report shows.

Your turn

Exercise · Medium · SQL

Weekly revenue for a quarter, empty weeks included

The table orders has one row per order:

  • id: the order number;
  • ordered_on: the day the order was placed on India's calendar, as text in the form YYYY-MM-DD;
  • amount: what the order was worth, in whole rupees.

Report the first quarter of the financial year 2026-27, the days from 2026-04-01 to 2026-06-30, week by week. Weeks are ISO weeks: they start on Monday and end on Sunday. Return one row for every week that has at least one day inside the quarter, with three columns:

  • week_start: the Monday that starts the week, as YYYY-MM-DD;
  • week: the week's ISO label, such as 2026-W14;
  • revenue: the total amount of the orders placed in that week and inside the quarter, or 0 when there are none.

Orders outside the quarter count nowhere, even when they fall in a week that the report lists. Sort the rows by week_start. The order of the rows is checked.

Starter code · weekly_revenue.sql 

-- Weekly revenue for the first quarter of FY2026-27, one row per ISO week.
-- So far this groups the orders by the Monday of their week, but weeks without orders
-- are missing, the label is not filled in yet, and orders outside the quarter still count.
SELECT date(ordered_on, '-6 days', 'weekday 1') AS week_start,
       NULL                                     AS week,
       sum(amount)                              AS revenue
FROM orders
GROUP BY week_start
ORDER BY week_start;
The sample tests · tests.yaml 
# Sample tests: each one builds a small orders table, runs your query on it and compares the rows it returns, in
# order.
tests:
  - name: orders on the quarter's edges, and orders just outside it
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, ordered_on TEXT NOT NULL, amount INTEGER NOT NULL);
      INSERT INTO orders (ordered_on, amount) VALUES
        ('2026-03-30', 700), ('2026-03-31', 300), ('2026-04-01', 450), ('2026-04-05', 200),
        ('2026-04-06', 1000), ('2026-05-20', 640), ('2026-06-29', 120), ('2026-06-30', 80),
        ('2026-07-01', 999), ('2026-07-05', 555);
    expected:
      columns: [week_start, week, revenue]
      rows:
        - ['2026-03-30', '2026-W14', 650]
        - ['2026-04-06', '2026-W15', 1000]
        - ['2026-04-13', '2026-W16', 0]
        - ['2026-04-20', '2026-W17', 0]
        - ['2026-04-27', '2026-W18', 0]
        - ['2026-05-04', '2026-W19', 0]
        - ['2026-05-11', '2026-W20', 0]
        - ['2026-05-18', '2026-W21', 640]
        - ['2026-05-25', '2026-W22', 0]
        - ['2026-06-01', '2026-W23', 0]
        - ['2026-06-08', '2026-W24', 0]
        - ['2026-06-15', '2026-W25', 0]
        - ['2026-06-22', '2026-W26', 0]
        - ['2026-06-29', '2026-W27', 200]
  - name: a quarter without a single order still has fourteen weeks
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, ordered_on TEXT NOT NULL, amount INTEGER NOT NULL);
      INSERT INTO orders (ordered_on, amount) VALUES ('2026-03-15', 500), ('2026-07-10', 800);
    expected:
      columns: [week_start, week, revenue]
      rows:
        - ['2026-03-30', '2026-W14', 0]
        - ['2026-04-06', '2026-W15', 0]
        - ['2026-04-13', '2026-W16', 0]
        - ['2026-04-20', '2026-W17', 0]
        - ['2026-04-27', '2026-W18', 0]
        - ['2026-05-04', '2026-W19', 0]
        - ['2026-05-11', '2026-W20', 0]
        - ['2026-05-18', '2026-W21', 0]
        - ['2026-05-25', '2026-W22', 0]
        - ['2026-06-01', '2026-W23', 0]
        - ['2026-06-08', '2026-W24', 0]
        - ['2026-06-15', '2026-W25', 0]
        - ['2026-06-22', '2026-W26', 0]
        - ['2026-06-29', '2026-W27', 0]
  - name: a Sunday belongs to the week that started on the Monday before it
    ordered: true
    setup: |
      CREATE TABLE orders (id INTEGER PRIMARY KEY, ordered_on TEXT NOT NULL, amount INTEGER NOT NULL);
      INSERT INTO orders (ordered_on, amount) VALUES
        ('2026-04-14', 250), ('2026-04-14', 400), ('2026-04-19', 350), ('2026-06-03', 1500);
    expected:
      columns: [week_start, week, revenue]
      rows:
        - ['2026-03-30', '2026-W14', 0]
        - ['2026-04-06', '2026-W15', 0]
        - ['2026-04-13', '2026-W16', 1000]
        - ['2026-04-20', '2026-W17', 0]
        - ['2026-04-27', '2026-W18', 0]
        - ['2026-05-04', '2026-W19', 0]
        - ['2026-05-11', '2026-W20', 0]
        - ['2026-05-18', '2026-W21', 0]
        - ['2026-05-25', '2026-W22', 0]
        - ['2026-06-01', '2026-W23', 1500]
        - ['2026-06-08', '2026-W24', 0]
        - ['2026-06-15', '2026-W25', 0]
        - ['2026-06-22', '2026-W26', 0]
        - ['2026-06-29', '2026-W27', 0]
A hint

Make the list of weeks first and the totals second. A recursive common table expression can start at the Monday of the quarter's first day, date('2026-04-01', '-6 days', 'weekday 1'), and add '+7 days' until it reaches the Monday of the quarter's last day. Then LEFT JOIN the orders of the quarter, each turned into the Monday of its own week the same way, and coalesce(sum(…), 0) turns a week without orders into 0.

The sample tests run on this device, in your browser (sql.js): nothing is sent to mysmartcopilot.com. The first run downloads SQLite (about 0.7 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

Check yourself

4 questions about this lesson. Every answer and why it is right is on the page, behind “Show the answer”. Your score stays in this browser.

  1. Question 1 of 4 An order is stored at 19:00 UTC on the last day of March. In which quarter does an Indian report on India Standard Time count it?

    Choose one answer.

    Show the answer

    Answer: In Q1 of the new financial year, because 19:00 UTC is 00:30 IST on the first day of April

    IST is UTC+05:30, so 19:00 UTC is already 00:30 on the next day in India. That day is the first of April, which opens quarter 1 of a new financial year. Convert the moment to the business's clock first, then truncate it.

  2. Question 2 of 4 The six orders are bucketed into financial-year quarters on both clocks. What does the second example print?

    What does this program print? Choose one answer. 

    -- The same six orders as before, stored in UTC.
CREATE TABLE orders (
  id        INTEGER PRIMARY KEY,
  placed_at TEXT    NOT NULL,  -- 'YYYY-MM-DD HH:MM:SS' in UTC
  amount    INTEGER NOT NULL   -- rupees
);
INSERT INTO orders VALUES
  (1, '2026-03-29 20:15:00',  450),
  (2, '2026-03-31 18:40:00', 1200),
  (3, '2026-04-04 07:05:00',  380),
  (4, '2026-04-05 18:50:00', 2250),
  (5, '2026-06-30 19:30:00',  640),
  (6, '2026-12-31 20:00:00',  999);

-- Revenue per quarter of the financial year, bucketed once on the UTC clock and once on
-- India's clock, side by side.
WITH both_clocks AS (
  SELECT 'utc' AS clock, placed_at AS t, amount FROM orders
  UNION ALL
  SELECT 'ist', datetime(placed_at, '+05:30'), amount FROM orders
),
labelled AS (
  SELECT clock, amount,
         cast(strftime('%Y', t) AS INTEGER)
           - (cast(strftime('%m', t) AS INTEGER) < 4)         AS fy,
         (cast(strftime('%m', t) AS INTEGER) + 8) % 12 / 3 + 1 AS q
  FROM both_clocks
)
SELECT printf('FY%d-%02d Q%d', fy, (fy + 1) % 100, q)   AS fy_quarter,
       sum(CASE WHEN clock = 'utc' THEN amount ELSE 0 END) AS utc_revenue,
       sum(CASE WHEN clock = 'ist' THEN amount ELSE 0 END) AS ist_revenue
FROM labelled
GROUP BY fy, q
ORDER BY fy, q;
    Show the answer

    Answer: it prints 

    ┌──────────────┬─────────────┬─────────────┐
│  fy_quarter  │ utc_revenue │ ist_revenue │
├──────────────┼─────────────┼─────────────┤
│ FY2025-26 Q4 │ 1650        │ 450         │
│ FY2026-27 Q1 │ 3270        │ 3830        │
│ FY2026-27 Q2 │ 0           │ 640         │
│ FY2026-27 Q3 │ 999         │ 0           │
│ FY2026-27 Q4 │ 0           │ 999         │
└──────────────┴─────────────┴─────────────┘

    Orders 2, 5 and 6 are placed after 18:30 UTC, so on India's clock each one moves to the next day, and each of those days opens a new quarter. The UTC columns keep them in the earlier quarter; the IST columns move 1,200, 640 and 999 rupees forward. A quarter that only one clock has shows 0 on the other.

  3. Question 3 of 4 Which SQLite expression returns the Monday that starts the ISO week of a date d, for any day of that week?

    Choose one answer.

    Show the answer

    Answer: date(d, '-6 days', 'weekday 1')

    Going back six days and then forward to the next Monday always lands on the Monday of the same week, and on d itself when d is a Monday. 'weekday 1' alone moves forward to the next Monday, which is the following week for every day but Monday, and %W is a week number, not a date.

  4. Question 4 of 4 In a financial year that starts in April, which quarter is January in? Use the formula of the lesson, (m + 8) % 12 / 3 + 1 with integer division.

    Type a number.

    Show the answer

    Answer: 4

    For January, m is 1: (1 + 8) % 12 is 9, 9 / 3 is 3 and 3 + 1 is 4. January to March are the last quarter of the financial year that began the April before.

Local hours, ISO weeks and custom widths, with the failures reproduced Pro

Each of the three choices fails in a way that the report itself never shows. This part reproduces an hourly table that cannot be moved to India’s clock and a week that splits in two at New Year, then builds buckets of any width and works out which widths line up on both clocks.

In this part:

  • An hourly report in UTC that cannot be moved to IST, reproduced
  • Week numbers that split one week in two at New Year, reproduced
  • Buckets of any width, and the widths that line up on both clocks

3 runnable examples (SQL) · about 4 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

  • Pro pass · 1 week₹79$2.99
  • 30 days₹179$5.99
  • 1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a passI have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Calendars that keep empty buckets, and averages over the right days, with numbers Pro

A report built from events alone has only the buckets in which something happened. This part fills an hour-by-day grid with zeros, measures how far an average per day is overstated without a calendar, and counts the short weeks at the edges of a quarter.

In this part:

  • An evening heat map with every hour, the empty ones included
  • Average orders per day: the calendar decides the denominator, measured
  • Short weeks at the edges of a quarter, counted from a calendar

3 runnable examples (SQL) · about 3 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

  • Pro pass · 1 week₹79$2.99
  • 30 days₹179$5.99
  • 1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a passI have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Month to date, year over year and the same weekday a week before, with numbers Pro

A comparison is only as right as the period it compares with. This part reproduces three comparisons that read the wrong period, a part of a month set against a whole month, a quarter that is missing and a closed day, and fixes each one by naming the period it means.

In this part:

  • Month to date against the same days of the month before, measured
  • Year over year by quarter: join on the period, not four rows back
  • The same weekday a week before: seven days back, not seven rows

3 runnable examples (SQL) · about 3 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

  • Pro pass · 1 week₹79$2.99
  • 30 days₹179$5.99
  • 1 year₹1,699$45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a passI have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Interview questions

  1. Fresher to mid · Warm-up

    Why can the same orders give different daily totals in a report grouped by UTC date and in one grouped by IST date?

    Because a day belongs to a clock. Orders are usually stored as UTC moments, and India Standard Time is UTC+05:30 all year, so a day in India starts at 18:30 UTC on the evening before. Grouped by the UTC date, every order placed between 18:30 and midnight UTC counts on the earlier day, although in India it was already after midnight. Grouped by the IST date, it counts on the day the customer saw. Single days differ, and so do months, quarters and financial years whenever their boundary falls in that window, such as the evening that ends a financial year in March. The fix is to convert first and truncate second: date(placed_at, '+05:30') in SQLite, or (placed_at AT TIME ZONE 'Asia/Kolkata')::date in PostgreSQL for a timestamptz column.

  2. Mid to senior

    A pipeline stores hourly order counts in UTC for two years. The team in India wants an hourly dashboard on IST and proposes shifting each UTC hour's label by five and a half hours. Why does that fail, and what do you build instead?

    First step and follow-ups

  3. Fresher to mid

    A weekly revenue chart shows a week labelled 2027-W53 with three days of sales and a week 2026-W53 with four, where the business expected one ordinary week. What went wrong, and how do you bucket and label weeks so that it cannot happen again?

    First step and follow-ups

  4. Mid to senior

    A quarterly report shows each seller's year-over-year growth with LAG(revenue, 4). Some sellers paused for a quarter, and the current quarter is only half over. Which figures are wrong, and how do you compute growth that is right for every seller?

    First step and follow-ups

Key takeaways

  • A time bucket is the first moment of its hour, day, week, month or quarter: truncate each timestamp to it and group by it.
  • Move stored UTC moments to the business’s clock before truncating: from 18:30 UTC an order is already on the next day in India.
  • ISO weeks run from Monday to Sunday; group by the Monday’s date and print labels at the end.
  • A financial year that starts in April puts January to March in quarter 4 of the year that began the April before.
  • A calendar keeps the buckets without events, and comparisons set each bucket beside an earlier one.

References

Related tools

Week Number Calculator Today’s week number, the week of any date and the dates of any week. Time Zone Converter See one moment in many cities and find meeting hours that suit everyone. SQLite Online (SQL Playground) A full SQLite database in your browser — open, query, import, and export with a pass.

Report a problem with this lesson