Date Part Constants

Concepts covered: Date Parts

The keyword inside EXTRACT is a date part constant: a fixed name for a single component of a moment. There are 11 for calendar and clock positions, plus a few aliases and 2 more for a zone's offset. Every value in the table comes from the lesson's clock, 2024-03-15 14:30:45, which fell on a Friday.

The second column is each constant's shorthand: day_of_week(ordered_at) and EXTRACT(DAY_OF_WEEK FROM ordered_at) return the same number, and choosing between them is a matter of style. EXTRACT also accepts 4 aliases. DAY_OF_MONTH means DAY and DOW means DAY_OF_WEEK, while DOY stands in for DAY_OF_YEAR and YOW for YEAR_OF_WEEK.

Here are 4 of them on 2 moments: the lesson's Friday afternoon and the last second of 2023. Check each value against the table above.

SELECT
  label,
  quarter(ts) AS quarter_num,
  week(ts) AS iso_week,
  day_of_week(ts) AS weekday_num,
  day_of_year(ts) AS year_day
FROM moments

The last second of 2023 fell on a Sunday, so it reads 7. And 2023 was no leap year, so day 365 was its last.

DAY_OF_YEAR drives year-to-date comparisons: keep rows where day_of_year(ordered_at) <= day_of_year(CURRENT_DATE) and every year is cut at the same point. On the lesson's date that point is day 75, which is March 15 in 2024 but March 16 in 2023, because February 29 pushes every later day of a leap year forward by 1.

Day of week starts on Monday

DAY_OF_WEEK follows ISO 8601: Monday is 1 and Sunday is 7. Spreadsheets and many programming languages number Sunday as 0 or 1 instead, so a weekend filter copied from one of them keeps the wrong days and raises no error. Here, weekend means day_of_week(x) IN (6, 7).

SELECT
  order_id,
  ordered_at,
  day_of_week(ordered_at) AS weekday_num,
  CASE
    WHEN day_of_week(ordered_at) IN (6, 7) THEN 'weekend'
    ELSE 'weekday'
  END AS day_type
FROM orders

2 vocabularies for parts

Date parts appear again in date_add and date_diff, spelled another way. There the unit is a quoted lowercase string, 'month', while EXTRACT takes a bare keyword, MONTH. The 2 lists overlap without matching, because they answer different questions: a constant names a position on the calendar, and a unit names a length of time.

EXTRACT constants: positions

• A bare keyword: EXTRACT(MONTH FROM ts)
• Includes cycle positions: DAY_OF_WEEK, DAY_OF_YEAR, YEAR_OF_WEEK
• Answers: where in the calendar does this moment sit?
• Has no millisecond constant, so use millisecond(ts)

date_add and date_diff units: lengths

• A quoted string: date_add('month', 1, ts)
• Runs from 'millisecond' to 'year', with 'week' and 'quarter' between
• Answers: how far forward, or how far apart?
• Has no 'day_of_week', because a position has no length

Swap the 2 styles, a quoted unit inside EXTRACT or a bare keyword inside date_add, and the query fails before reading a row.

The 2 kinds often meet in a single expression. date_add('day', 1 - day_of_week(d), d) reads a position with a function and moves by a length with a unit, landing on the Monday of the week that holds d. The last section of this lesson puts that pattern to work.

Parts follow the value's zone

On a TIMESTAMP WITH TIME ZONE, every constant reads the wall clock of the value's own zone. The 2 values below are the same instant written in 2 zones, and they report different hours. TIMEZONE_HOUR and TIMEZONE_MINUTE return the zone's offset from UTC.

A report should count only orders placed on a Saturday or a Sunday. Which filter does that?

• WHERE day_of_week(ordered_at) IN (0, 6)
• WHERE day_of_week(ordered_at) IN (1, 7)
• WHERE EXTRACT('dow' FROM ordered_at) IN (6, 7)
• WHERE day_of_week(ordered_at) IN (6, 7)

About this lesson

This section is part of the Dates: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.