Dates: Intermediate

A TIMESTAMP such as 2024-03-15 14:30:45 is a single value holding 6 facts, from the year at the top down to the second. EXTRACT(field FROM value) returns the fact you name as a BIGINT, which turns the parts of a date into numbers you can filter and group by.

Here is that timestamp taken apart one field at a time, with ts standing for TIMESTAMP '2024-03-15 14:30:45'.

Each part comes back as a plain number: month 3, with no leading zero and no name. That number sorts before 12 and takes part in arithmetic, while the string 'March' sorts after 'December' and adds to nothing. EXTRACT reads a DATE the same way for its calendar parts, so EXTRACT(YEAR FROM DATE '2024-03-15') is also 2024.

SUBSTRING can also pull a month out of a timestamp: cast it to text and take 2 characters starting at position 6. The 2 routes hand back different types, and only one of them goes into arithmetic without another cast.

A part is a number, so it works in WHERE like any other column. What a single part loses is the larger parts around it. EXTRACT(MONTH FROM ordered_at) = 12 keeps December 2022 rows alongside December 2023 rows, since both read as month 12. Add the year when you mean a particular December.

A part is an expression like any other, so it can sit inside CASE or BETWEEN or go into plain arithmetic. Labelling each order with a meal slot takes a single CASE over EXTRACT(HOUR FROM ordered_at).

Order 306 has no timestamp. EXTRACT of a NULL is NULL, so every WHEN test is unknown and the row falls through to 'other'. Put WHEN ordered_at IS NULL THEN 'unknown' first when missing times should stay visible.

Each field also has a function of the same name: year(ordered_at) returns what EXTRACT(YEAR FROM ordered_at) returns. The next section lists every field in both forms.

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.

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 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).

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.

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.

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 user signs up at 2024-01-31 23:00:00 and places a first order at 2024-02-01 01:00:00. Between those moments 2 hours passed, and over the same span the calendar crossed 1 midnight and 1 month boundary. Each count is correct for its own question, and a report has to choose which question it is asking.

date_diff(unit, start, end) measures time passed. It counts how many complete units fit between the 2 values and drops whatever is left over. On timestamps a day means 24 full hours, so 23:00 one evening to 01:00 the next morning is 0 days. A DATE has no time of day, so between 2 dates each whole day passed is also 1 midnight crossed. Cast both sides to DATE and the same call counts calendar days.

Swap the 2 arguments and the sign flips. date_diff counts from its second argument to its third, so an end earlier than its start returns a negative number.

User 3 shows another rule of date_diff. January 31 to February 29 counts as 1 complete month, because a start on day 31 completes its month on the last day of a shorter month.

For months, date_diff offers no boundary count, so build one with EXTRACT. Turn each value into a running month number with year * 12 + month and subtract one from the other. January 2024 becomes 24289 and February 2024 becomes 24290, so any 2 moments in those months differ by exactly 1. The day and hour don't matter. Years need less: the difference of the 2 years counts year boundaries crossed.

The split shows up at every size. From 10:59 to 11:01, date_diff('hour', ...) returns 0 while the clock moved into a new hour, and from December 31 to January 1, date_diff('year', ...) returns 0 while the calendar moved into a new year.

Retention reports show what the wrong choice costs. For a January cohort, month 1 is supposed to mean February. With date_diff('month', ...), a user who joined on January 20 and came back on February 10 lands in month 0 beside the people active in January, and month 1 shows fewer returning users than really returned.

Unix time, also called epoch time, stores a moment as a single number: the seconds since 1970-01-01 00:00:00 UTC. The lesson's clock, 2024-03-15 14:30:45 UTC, is 1710513045. The number has no zone, so it names the same instant in Tokyo and in Lisbon, which is why event logs and APIs use it so often.

from_unixtime(seconds, zone) turns an epoch number into a TIMESTAMP WITH TIME ZONE, displayed on the clock of the zone you pass. A milliseconds column is divided by 1000 first. Cast it to DOUBLE before dividing, because BIGINT / BIGINT is integer division and throws the milliseconds away.

to_unixtime(ts) goes the other way and returns a DOUBLE of seconds, fractions included. Multiply by 1000 and cast to BIGINT to match a milliseconds column. The cast rounds to the nearest whole number, which is exact for a timestamp stored to the millisecond.

Subtracting 2 epoch values always gives time passed. Session 2 crossed midnight and still reads 280 seconds. Session 3 ran 86399 seconds, 1 short of a full day, so date_diff reports 0 days for it.

Some file formats store a DATE as whole days since 1970-01-01. date_add('day', 19797, DATE '1970-01-01') turns day 19797 back into 2024-03-15, and date_diff('day', DATE '1970-01-01', d) gives the day number of any date d.

The zone argument changes only how the instant is displayed and which parts EXTRACT reads from it. from_unixtime(1710513045, 'UTC') shows 14:30:45 and from_unixtime(1710513045, 'America/New_York') shows 10:30:45 the same morning, so hour() returns 14 for one and 10 for the other. Group a day's events by hour in the zone your users live in, or the morning rush lands at the wrong hours.

A monthly revenue report is a GROUP BY on date parts. The grouping has to include every larger part as well: month alone puts March 2023 and March 2024 in one group, so group on year and month together.

Quarters work the same way through quarter(). In the animation, 4 orders from 2024 fold into 2 groups keyed on year and quarter. COUNT and SUM then run once per group.

quarter() knows only calendar quarters. A fiscal year that starts in February needs its own mapping: quarter(date_add('month', -1, ordered_at)) shifts every date back 1 month before reading the quarter, so February to April becomes quarter 1.

Weekly reports group on the ISO week, week(). An ISO week runs Monday to Sunday. Week 1 is the week holding the year's first Thursday, so the last days of December can sit in week 1 of the next year. Pair week() with year_of_week(), the year that owns the week, and never with year().

With year() and week() as the key, the last 2 days of 2024 read as 2024 week 1. January 1 to 7, 2024 read the same way, so 2 weeks 12 months apart merge into 1 row.

Grouping on 2 number columns per period leaves output that is clumsy to sort and to chart, and a single date groups better. date_add('day', 1 - day(d), d) moves a date back to the 1st of its month, and date_add('day', 1 - day_of_week(d), d) moves it back to its Monday. Grouped on that date, each row is labelled with a real period start that sorts and charts as a date. The CTE casts each timestamp to DATE once so the grouping expression stays short.