DATE_FORMAT()

Concepts covered: Date Formatting

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.

Formatting a pay statement

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.

SELECT
  date_format(week_start, '%a, %b %e %l:%i %p') AS opens,
  date_format(week_start + INTERVAL '7' DAY - INTERVAL '1' MINUTE, '%a, %b %e %l:%i %p') AS closes,
  earnings
FROM pay_weeks
ORDER BY week_start

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

What does this query return?

SELECT
  date_format(TIMESTAMP '2024-03-04 16:05:00', '%l:%i %p') AS label

• 04:05 PM
• 16:05 PM
• 4:05 PM
• 4:5 PM

Sort by the value, show the text

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.

By label

Alphabetical order: Apr, Feb, Jan, Mar, Nov.

SELECT
  date_format(month_start, '%b %Y') AS month_label,
  revenue
FROM monthly
ORDER BY month_label

By date

Calendar order: Jan, Feb, Mar, Apr, Nov, because the sort reads the date.

SELECT
  date_format(month_start, '%b %Y') AS month_label,
  revenue
FROM monthly
ORDER BY month_start

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.

%Y-W%v on 2024-12-30

• Prints 2024-W01
• Points at the week of January 1, 2024, almost a year earlier
• Puts the last Monday of 2024 at the top of a 2024 list

%x-W%v on 2024-12-30

• Prints 2025-W01
• Points at the week that opens on this Monday
• Agrees with year_of_week and week for the same date

Format after converting the zone

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.

SELECT
  trip_id,
  date_format(CAST(ended_at AS TIMESTAMP WITH TIME ZONE), '%a %l:%i %p') AS utc_label,
  date_format(CAST(ended_at AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'America/Chicago', '%a %l:%i %p') AS chicago_label
FROM trips
ORDER BY trip_id

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.

Build a query that lists each trip with its end time as a Chicago label such as Sun 11:50 PM, in the order the trips ended. 2 tiles don't belong.

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.