skip to content

questions

6

In SQL, why does GROUP BY EXTRACT(MONTH FROM order_date) merge different years, and how do you bucket orders by calendar month?

level: juniorimportance: must knowfreq 56%

answer

  1. what one extracted field remembers
  2. March of two different years
  3. group by two fields
  4. truncate to the month's first day
  5. no single truncation spelling

basics

~20 s

EXTRACT returns a single field as a number, so March 2023 and March 2024 both give 3 and share a group. Group by year and month together, or by each date truncated to its month's first day.

solid answer

~40 s

`EXTRACT(MONTH FROM order_date)` returns only the month number, 1 to 12, with no year attached. Three years of orders therefore collapse into twelve month groups at most, and March 2023 adds silently to March 2024. The output looks plausible, so the bug tends to surface only when someone compares totals with another source. One fix is to group by two fields, `EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)`, which works on engines that have `EXTRACT`, such as PostgreSQL, MySQL and Oracle. The other is to truncate each date to its month's first day and group by that single value, which sorts chronologically and makes a ready chart axis. Truncation has no single portable spelling: PostgreSQL has `date_trunc('month', order_date)`, Oracle has `TRUNC(order_date, 'MM')`, MySQL can use `DATE_FORMAT(order_date, '%Y-%m-01')`, and SQLite has `date(order_date, 'start of month')`.

code

sql · 14 lines
sql
-- two fields: works wherever EXTRACT exists
SELECT EXTRACT(YEAR FROM order_date) AS yr,
       EXTRACT(MONTH FROM order_date) AS mon,
       SUM(amount) AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY yr, mon;

-- one key: date_trunc is PostgreSQL's spelling of truncation
SELECT date_trunc('month', order_date)::date AS month_start,
       SUM(amount) AS revenue
FROM orders
GROUP BY date_trunc('month', order_date)::date
ORDER BY month_start;

go deeper

for a junior

Recall that EXTRACT returns a single number with no year attached, and write the two-field GROUP BY from memory. Then say that truncating to the month's first day gives one sortable key.

for a middle

Explain which engines have EXTRACT and how truncation is spelled on each. Mention that weekday numbering differs by engine, and that year plus ISO week disagree around New Year on PostgreSQL.

for a senior

Ask whether the reader wants a timeline or seasonality before fixing the query, and check month buckets against a known total. A merged-year bug looks plausible, so it survives review unless someone reconciles the numbers.

for a principal

Weigh a shared calendar table against per-engine truncation. The table costs a little upkeep, yet it gives every report the same weeks, fiscal periods and empty months, which is what stops teams publishing conflicting numbers.

## What EXTRACT returns `EXTRACT(field FROM source)` reads **one field** out of a date or timestamp and returns it as a number. `EXTRACT(MONTH FROM DATE '2024-03-15')` returns 3, and `EXTRACT(YEAR FROM DATE '2024-03-15')` returns 2024. The form is widely available but not universal: PostgreSQL, MySQL and Oracle have it, SQLite does not, and SQL Server uses its own `DATEPART`, which is not portable. Three details decide how it behaves in a real query: - **The result knows nothing else.** The number 3 carries no year, so nothing tells March 2023 from March 2024. - **The field must fit the type.** On PostgreSQL, fields smaller than a day cannot be taken from a `date`, so `EXTRACT(HOUR FROM DATE '2024-03-15')` is an error. - **NULL stays NULL.** A row with no `order_date` gives a NULL field, and `GROUP BY` puts those rows in one extra group. ## Why a month-only group merges years This query runs without complaint and returns a number for every month: ```sql SELECT EXTRACT(MONTH FROM order_date) AS month_no, SUM(amount) AS revenue FROM orders GROUP BY EXTRACT(MONTH FROM order_date); ``` With three years of non-null dates it returns at most 12 rows, and each row adds up the same month from all three years. That is the right query for a **seasonality** report, where you want every March together. It is the wrong query for a **timeline**, where each March is a separate point. The mistake is hard to see because every row looks like a sensible total, so ask which of the two the reader expects before you write the `GROUP BY`. ## Two correct ways to bucket by month 1. **Group by two fields.** `GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)` keeps the years apart on any engine with `EXTRACT`. You sort by both fields, and you build a label yourself if a chart needs one. 2. **Truncate to the period's start.** Replace each date with the first day of its month, then group by that one value. It sorts chronologically, it is a real date for further arithmetic, and it plots directly on a time axis. A third, fully portable option is a **calendar table** with one row per day and a `month_start` column. You join to it and group by that column, and it also gives you the months that had no orders. ## Truncation has no single spelling | Engine | First day of the month | Result | |---|---|---| | PostgreSQL | `date_trunc('month', order_date)` | a timestamp; cast it with `::date` when you want a date | | Oracle | `TRUNC(order_date, 'MM')` | a `DATE` | | MySQL | `DATE_FORMAT(order_date, '%Y-%m-01')` | a string in sortable ISO form | | SQLite | `date(order_date, 'start of month')` | text in ISO form | Avoid a label such as `'Mar 2024'` as the bucket: it sorts alphabetically, so April comes before March. PostgreSQL's `date_trunc` also deserves one warning. Given a `date`, it returns a `timestamp with time zone` in the session's zone, so the cast back to `date` keeps a session setting out of what you print. ## Weekday numbers are not portable Extracting a weekday is where `EXTRACT` stops being a portable habit, because the numbering belongs to each engine: - **PostgreSQL** has `EXTRACT(DOW ...)`, Sunday 0 to Saturday 6, and `EXTRACT(ISODOW ...)`, Monday 1 to Sunday 7. - **MySQL** has no weekday field in `EXTRACT`. `DAYOFWEEK()` runs Sunday 1 to Saturday 7, and `WEEKDAY()` runs Monday 0 to Sunday 6. - **Oracle** has no weekday field in `EXTRACT` either, and `TO_CHAR(d, 'D')` depends on the session's territory setting. - **SQLite** has `strftime('%w', d)`, Sunday 0 to Saturday 6, returned as text. A query that filters on day 1 therefore means Monday on PostgreSQL and Sunday on MySQL's `DAYOFWEEK()`. State which numbering a query relies on, or keep a weekday column in a calendar table so nobody has to remember. ## The same trap with weeks Grouping by `EXTRACT(YEAR ...)` and `EXTRACT(WEEK ...)` has a related flaw on PostgreSQL. `WEEK` is the ISO 8601 week, while `YEAR` is the calendar year, and the two disagree around New Year. 1 January 2021 has year 2021 and week 53, because it belongs to the last ISO week of 2020, so one real week splits across two groups. Pair `WEEK` with `ISOYEAR` instead, or truncate to the start of the week. ## Filtering to one month Use a date range rather than comparing extracted fields: `order_date >= DATE '2024-03-01' AND order_date < DATE '2024-04-01'`. It reads the same on any engine that accepts the literal, and it leaves the column bare, which matters for index use.

  • How do you keep a weekday filter correct when a query moves between engines?
    Do not carry the raw number across, because Sunday is 0 under PostgreSQL's `DOW` and 1 under MySQL's `DAYOFWEEK()`. Rewrite the filter for the target engine's numbering, ideally the Monday-first `ISODOW` on PostgreSQL, or join a calendar table with its own weekday column, which reads the same everywhere.
  • Why can grouping by EXTRACT(YEAR ...) and EXTRACT(WEEK ...) split a week on PostgreSQL?
    `WEEK` follows ISO 8601 while `YEAR` is the calendar year. 1 January 2021 reports year 2021 and week 53, though it sits in the same ISO week as the end of December 2020, so that week lands in two groups. Use `ISOYEAR` with `WEEK`, or truncate to the week's start.
  • When is a month-only GROUP BY the right query?
    When the question is seasonality: does March usually sell better than June, across all years? There you want every March together, and `EXTRACT(MONTH ...)` alone says that exactly. Label the column so a reader does not take it for a timeline, and check that each month contributes the same number of years.

  • EXTRACT(MONTH ...) keeps the year internally, so March 2023 and March 2024 stay separate groups.
  • date_trunc is portable SQL that runs unchanged on every engine.
  • Weekday numbers are the same on every engine, so filtering on day 1 is portable.
  • A month label such as 'Mar 2024' sorts chronologically in ORDER BY.
  • Pairing EXTRACT(YEAR ...) with EXTRACT(WEEK ...) always keeps one calendar week in one group.
open as a page

In SQL, what does created_at AT TIME ZONE 'Europe/Berlin' do, and how does the answer depend on whether created_at has a time zone?

level: middleimportance: must knowfreq 50%

basics

~10 s

On PostgreSQL, AT TIME ZONE converts by input type: a timestamp with time zone becomes Berlin's wall-clock reading, and a plain timestamp is read as Berlin's wall clock and becomes an instant.

open as a page

In SQL, how do CURRENT_DATE and CURRENT_TIMESTAMP differ, and why does PostgreSQL's CURRENT_TIMESTAMP stay frozen through a long transaction?

level: middleimportance: should knowfreq 42%

basics

~10 s

CURRENT_DATE gives today's date and CURRENT_TIMESTAMP adds the time of day. PostgreSQL fixes both at the transaction's start, so they do not advance inside a transaction; clock_timestamp() reads the real clock.

open as a page

In SQL, what does DATE '2024-01-31' + INTERVAL '1' MONTH return, and why does a monthly schedule built by repeated addition drift?

level: middleimportance: should knowfreq 46%

basics

~20 s

PostgreSQL and MySQL clamp 31 January plus a month to 29 February 2024, and the lost day never returns: a second month gives 29 March. Compute each date from the original anchor, not the previous result.

open as a page

A daily sales report for users in several time zones shows a short last day each month, and its buckets shift by an hour twice a year; how do you diagnose and fix it in SQL?

level: seniorimportance: should knowfreq 48%

basics

~20 s

A short last day and shifting buckets are usually two bugs. An inclusive or UTC upper bound cuts the day, so use a half-open range in the report's zone. Fixed offsets shift buckets, so use a named zone.

open as a page

For a reporting system with users in several time zones, which zone should store event times, which should define a day's bucket, and what does each choice cost?

level: principalimportance: should knowfreq 37%

basics

~20 s

Store past events as instants, usually in UTC, and pick bucket zones by who uses the number. One business zone keeps totals reconcilable; viewer zones match local expectations but stop totals agreeing; serving both needs finer rollups.

open as a page