Dates: Advanced

A TIMESTAMP WITH TIME ZONE value is a point on the global timeline plus the zone it should be read in. AT TIME ZONE swaps that zone and keeps the point, so the clock digits change while the moment stays where it was. Every conversion question then has 2 parts: which instant, and whose wall clock reads it.

The trips table stores each end time as text that carries its zone, such as 2024-03-04 05:50:00 UTC. A CAST to TIMESTAMP WITH TIME ZONE reads that text as an instant, AT TIME ZONE 'America/Chicago' reads the instant on a Chicago clock, and a CAST of the result to DATE keeps the Chicago calendar day.

A fixed offset such as -06:00 applies the same shift all year. A named zone such as America/Chicago carries the region's rule history, so it moves from 6 to 5 hours behind UTC at the instant the clocks jumped. Both tabs below read 07:30 and 08:30 UTC on March 10, 2024, half an hour before and after Chicago's jump.

AT TIME ZONE accepts a zone name written as a literal, so it can't read the zone from a column. When each row carries its own zone, route the value through Unix time, which the intermediate lesson covered: to_unixtime turns the instant into seconds since 1970-01-01 UTC, and from_unixtime(seconds, zone) builds a TIMESTAMP WITH TIME ZONE read in the zone that row names. Seconds since the epoch belong to no zone, so the instant passes through unchanged.

date_trunc(unit, value) keeps every field down to the named unit and resets the smaller ones to their minimum: day 1, hour 0, minute 0, second 0. The result has the input's type, so a truncated timestamp can still be sorted and compared, and interval arithmetic works on it. Every value inside the same unit lands on the same start point, which turns the truncated column into a grouping key.

March 14, 2024 is a Thursday, and 'week' snaps it back to Monday, March 11: date_trunc always starts weeks on Monday, the convention the ISO 8601 section covers in full. 'quarter' lands on January 1 because March sits in the first quarter, and 'year' would land there too.

The units are 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter' and 'year'.

Grouping by month(ordered_at), from the intermediate lesson, returns 3 for every March. That puts March 2023 and March 2024 in 1 group. date_trunc('month', ordered_at) returns a full timestamp such as 2024-03-01 00:00:00, which still names its year and sorts across years in calendar order.

The GROUP BY repeats the whole date_trunc expression because GROUP BY runs before the SELECT list names order_month. Change the unit string and the same query counts at another grain, such as week or quarter. Every key stays a timestamp, so later steps can filter on it or format it.

On a TIMESTAMP WITH TIME ZONE, date_trunc cuts at the boundaries of the zone the value carries. Truncate a UTC value to 'day' and days break at UTC midnight, which is 6:00 p.m. in Chicago during standard time, so an evening shift splits across 2 days. Convert with AT TIME ZONE before truncating, and days break at Chicago midnight.

Trips 101 and 103 each move back 1 day once the cut reads Chicago's clock, because both ended at 23:50 local. Trip 102 ended at 1:05 p.m. in Chicago and keeps March 10 either way. A daily total grouped by utc_day would credit 2 Sunday-night fares to Monday.

A pay week that runs Monday 4:00 a.m. to the next Monday 3:59 a.m. matches no unit. Shift every timestamp back 4 hours so the boundary lands on midnight, truncate to 'week', then add the 4 hours back. The fares table below holds Chicago wall-clock times as plain timestamps; with UTC data, convert before shifting. Here are 3 fares walked through by hand.

Dates arrive as text, and each source picks its own layout: a CSV export writes them one way, a partner feed another. date_parse(text, format) reads text into a TIMESTAMP by matching it against a format string: each % specifier stands for one field, and every other character must appear in the text exactly as written.

Minutes are %i because %m already means month. A format that writes %m in the minutes slot reads that slot as a second month field, and any minute value above 12 fails to parse.

%h with %p turns 11:50 PM into 23:50 and 07:05 PM into 19:05. The slashes and the colon outside the specifiers matched the text exactly, and so did the space between date and time.

Real feeds drift. Suppose a second upstream system starts writing 2024-03-10 01:30 into the same column. That text has hyphens where the format expects slashes, so date_parse raises an error at that row and the whole query fails, however many good rows came before it. TRY wraps any expression and turns an error inside it into NULL for that row, the way TRY_CAST handles a bad cast.

With that row added to raw_events, the 3 rows above parse as before and the new one returns NULL in parsed. Counting those NULLs after each load tells you how many rows the format missed, and a count that jumps from 0 to thousands overnight means a source changed its layout.

The text 03/04/2024 means March 4 to a US system and April 3 to most of Europe. Nothing in its 10 characters says which, so the format string records the decision, and only the source system's documentation can settle it.

parse_datetime(text, pattern) and format_datetime(value, pattern) use letter patterns in place of % codes, as in yyyy-MM-dd HH:mm:ss. Letter case carries meaning: MM is month and mm is minutes, so the pattern yyyy-mm-dd reads 2024-03-10 as minute 3 of January 10. parse_datetime returns a TIMESTAMP WITH TIME ZONE, and its Z letter reads a numeric offset such as -0600 from the text.

The offset in the text becomes part of the value, so converting to UTC lands on 07:30. Writing runs the same machinery in reverse: format_datetime turns a timestamp into 2024/03/10/07, an hour-level folder layout for raw files. Code that reads those folder names back needs the identical pattern, so define it once and use it in both directions.

The case slip in yyyy-mm-dd raises no error, so nothing in the load stops it. These 2 columns parse the same text and differ only in the case of the month letters.

month_lower lands on January 10 at 00:03: the mm slot read 03 as minutes, and the month, never read, defaulted to January. Every row of a feed parsed this way counts as parsed, so a NULL check passes while the whole feed piles into January.

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.

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.

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.

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.

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.

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.

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.

date_format(value, format) turns a timestamp into the text a person reads on a statement or a chart axis. It shares its % specifiers with date_parse; the table adds the name and unpadded-number specifiers that matter most on output, and shows each one for 2024-03-04 16:07:09.

The weekly totals built in the DATE_TRUNC section arrive with week_start as a Chicago wall-clock timestamp. Each statement line needs both ends of its week in the form drivers read: the Monday 4:00 a.m. opening, and the close 1 minute short of 7 days later.

%e and %l drop leading zeros, so the text reads Mar 4 3:59 AM. With %d and %h it would print Mar 04 03:59 AM. The query still orders by the timestamp week_start and formats only inside the SELECT list.

A formatted date is a VARCHAR, and text sorts character by character. Apr comes before Jan because A precedes J, so ordering by the label scrambles the calendar. Both tabs below show the same labels and differ only in the ORDER BY column.

The weekly label in that list depends on %x. %Y prints the calendar year and %x the year a week number belongs to, the value year_of_week returns, so the specifiers agree on most days and split only on the days around New Year that ISO files under a neighbouring year. Monday, December 30, 2024 is one such day.

date_format prints the clock digits of the value it receives. On a TIMESTAMP WITH TIME ZONE that means the digits in the zone it carries, so a UTC value formats as a UTC time. Apply AT TIME ZONE first and the label shows the reader's local time, as in this query on the trips table from the first section.

Trip 101 shows Mon 5:50 AM in UTC and Sun 11:50 PM in Chicago. Only the second matches what the driver saw on the dashboard, and it decides which pay week the fare joins.