ISO 8601 Functions

Concepts covered: Date Formatting

ISO 8601 is the international standard for writing dates and times as text. It puts the largest unit first in fixed-width fields and ends with an explicit offset. 2024-03-10T01:30:00-06:00 names its instant with no outside context, and values written in the same offset sort correctly as plain text, which is why many APIs and log formats use it.

Reading timestamps without a format string

from_iso8601_timestamp(text) reads ISO 8601 timestamp text into a TIMESTAMP WITH TIME ZONE with no format string. It accepts the variations the standard allows within one column, so Z for UTC and a numeric offset can sit side by side, with or without fractional seconds. A date_parse format matches exactly one of those layouts.

SELECT
  event_id,
  sent_at,
  from_iso8601_timestamp(sent_at) AT TIME ZONE 'UTC' AS sent_utc
FROM webhook_events

Rows e1 and e2 look nothing alike as text, yet they name the same instant, and e3 lands 250 milliseconds later. Compared as strings, all 3 differ; compared after parsing, e1 equals e2.

Sorting the raw text puts e2 first and e3 last, because text compares the clock digits as written: 01:30, 07:30, 09:45, 16:30. On the UTC timeline e1 and e2 tie at 07:30, with e3 250 milliseconds behind them. Row e4 comes last at 08:45. ORDER BY on the text column reports the first order, and MIN and MAX do the same.

Calendar, week and ordinal dates

from_iso8601_date(text) returns a DATE and reads 3 ISO spellings of a day: the calendar form 2024-12-30, the week form 2025-W01-1 (week-year 2025, week 1, day 1 for Monday), and the ordinal form 2024-366, the 366th day of 2024.

SELECT
  label,
  from_iso8601_date(label) AS as_date
FROM plan_dates

The first 2 labels name the same Monday, and the week form files it under 2025. That filing rule is the one ISO week numbers follow.

ISO weeks and the year they belong to

ISO weeks start on Monday, and week 1 of a year is the week that holds the year's first Thursday. The last days of December can therefore sit in week 1 of the next year, and the first days of January in week 52 or 53 of the previous one. week(d) returns the ISO week number, year_of_week(d) returns the ISO year that number belongs to, and day_of_week(d) returns 1 for Monday through 7 for Sunday.

The Thursday rule gives a way to compute the ISO year by hand. Every ISO week holds exactly 1 Thursday, and the week belongs to the year that Thursday falls in. date_add('day', 4 - day_of_week(d), d) moves any date to the Thursday of its week, 3 days forward from a Monday and 3 back from a Sunday, and year() of that Thursday is the value year_of_week(d) returns. The query below computes it that way so the rule is visible in every row.

SELECT
  d,
  year(d) AS cal_year,
  year(date_add('day', 4 - day_of_week(d), d)) AS iso_year,
  week(d) AS iso_week,
  day_of_week(d) AS iso_dow
FROM edge_days
ORDER BY d

On 2024-12-30, cal_year says 2024 while iso_week says 1. A weekly report grouped by year(d), week(d) files that Monday under week 1 of 2024, a week that ended almost a year earlier, and it splits Friday 2021-01-01 away from the Thursday before it. Grouping by year_of_week(d), week(d) keeps every week whole.

Count the days in edge_days per ISO week, and keep each week whole across New Year by taking the ISO year from the Thursday of the week. Two tiles don't belong.

Writing ISO text

to_iso8601(value) writes the standard form back out. A DATE becomes 2024-03-10, and a TIMESTAMP WITH TIME ZONE keeps its offset, with UTC written as Z. Use it whenever a timestamp leaves the database for another system, since the receiver needs no agreement beyond the standard to parse it.

SELECT
  to_iso8601(DATE '2024-03-10') AS date_text,
  to_iso8601(from_iso8601_timestamp('2024-03-10T01:30:00-06:00') AT TIME ZONE 'UTC') AS utc_text

Do

• Send timestamps to other systems with to_iso8601, in UTC
• Parse ISO text with from_iso8601_timestamp and skip hand-written formats
• Group weekly data by year_of_week(d) and week(d) together

Avoid

• Compare ISO strings that carry different offsets as text
• Pair week(d) with year(d) in a weekly report
• Strip the offset before parsing to make the text look uniform

What does this query return?

SELECT
  year(DATE '2021-01-02') AS cal_year,
  year_of_week(DATE '2021-01-02') AS iso_year,
  week(DATE '2021-01-02') AS iso_week

• 2021, 2021, 53
• 2021, 2020, 53
• 2021, 2021, 1
• 2020, 2020, 53

About this lesson

This section is part of the Dates: Advanced 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.