MODULE 2 · SESSION 10

Date & Time Basics:
Temporal Arithmetic

Master temporal types (DATE, TIMESTAMP, TIMESTAMPTZ), calculate intervals with AGE, snap timestamps to period starts with DATE_TRUNC, and format reports with TO_CHAR. All in PostgreSQL 18, on RetailMart.

28

Scenes

1

Practice Arena

4

Practice Drills

5

Interview Questions

Today’s Agenda — Date & Time Basics

Four pillars: from raw calendar strings and timestamps to temporal precision and cohort analysis

Pillar 01

🕑 Clocks & Temporal Types

01 Core types: DATE, TIMESTAMP, TIMESTAMPTZ

02 Current time: CURRENT_DATE vs NOW()

03 Timezone offsets: UTC vs IST (+05:30)

Pillar 02

➕ Interval Arithmetic

04 Calendar math: date ± INTERVAL 'X'

05 Tenure calculation: AGE(end, start)

06 Duration in seconds: EXTRACT(EPOCH ...)

Pillar 03

🧩 Component Extraction

07 Calendar parts: EXTRACT(YEAR, MONTH, DOW)

08 Cohort snapping: DATE_TRUNC('month', ts)

09 Weekend & quarterly trading buckets

Pillar 04

📅 Presentation Formatting

10 Format masks: TO_CHAR(ts, 'DD-Mon-YYYY')

11 The blank pad trap: FMDay & FMMonth

12 Elapsed days: integer subtraction vs interval

💡

Why this matters: RetailMart has 1,50,000 orders spanning 2024 to 2026. Determining seasonal demand spikes, customer cohort retention, return expiration windows, or payroll shift durations requires first-class temporal arithmetic instead of fragile string parsing.

Where We Left Off: Text Manipulation to Temporal Precision

Bridging the gap between string operations and true date & time arithmetic

🔤 SESSION 09 • COMPLETED

Clean & Parse Text (Strings)

-- Session 9: string surgery and normalization

SELECT email,

SPLIT_PART(email, '@', 2) AS domain

FROM customers.customers

ORDER BY customer_id LIMIT 3;

-- [email protected] → bmw.com

Session 9 cleaned messy text values (UPPER/LOWER, TRIM, SUBSTRING, SPLIT_PART, CONCAT_WS). But calendar dates cannot be treated as text strings: strings know nothing about leap years, calendar months, or timezones.

✓ Strings cleaned; temporal math awaits

📅 SESSION 10 • THE MISSION

Dates Are NOT Strings

-- “Calculate 14-day return deadline from order date”

SELECT order_id, order_date,

order_date + INTERVAL '14 days' AS return_deadline

FROM sales.orders

ORDER BY order_id LIMIT 3;

-- 2024-05-02 + 14 days → 2024-05-16 00:00:00

Note: DATE + INTERVAL returns a full TIMESTAMP. PostgreSQL provides dedicated temporal types (DATE, TIMESTAMP, TIMESTAMPTZ, INTERVAL). We calculate intervals cleanly, snap cohorts with DATE_TRUNC, and extract components with EXTRACT.

📅 Today: INTERVAL, AGE, EXTRACT, DATE_TRUNC, TO_CHAR

💡

The Core Rule: Time is not a string. Dates have arithmetic, leap years, timezone boundaries, and calendar intervals. Always compute with first-class temporal types (DATE, TIMESTAMP, INTERVAL).

📅

The Midnight Flash Sale Glitch 4-HOUR FLASH SALE • TIMEZONE COLLAPSE

ECOMMERCE • CAMPAIGN CRASH EXAMPLE STORY • NAMES & NUMBERS MADE UP 🕘 SALE LAUNCH: 12:00 AM IST

STEP 01 • THE INCIDENT

ops-alert-bot 12:12 AM

“Midnight Flash Sale launched at 12:00 AM IST. Customers report promo code FLASH40 fails at checkout. Carts abandoned totaling ₹6.4 lakh in first 10 minutes!”

App server logged orders in UTC (18:35 Oct 31); discount rule checked order_date >= '2025-11-01'.

STEP 02 • ROOT CAUSES

🌎 UTC Server vs IST Shoppers

Timezone Offset Drop

Cloud servers recorded timestamps in UTC. When shoppers checked out at 12:05 AM IST on 1 Nov, the server logged 18:35 UTC on 31 Oct, falling outside the promo date.

📝 String Date Comparison

WHERE date >= '2025-11-01'

A developer filtered timestamps with string comparison. The query evaluated 31 Oct UTC as before 1 Nov, automatically rejecting flash coupons.

💸 ₹6.4 Lakh in Lost Sales

Cart Abandonment

Over 800 customers saw discount codes fail at midnight checkout, causing widespread cart abandonment before engineering intervened.

STEP 03 • THE TEMPORAL FIX

TIMESTAMPTZ + INTERVAL

🐙 Safe Timezone Window

SELECT
  order_id,
  order_date::timestamp AS order_time,
  net_total
FROM sales.orders
WHERE order_date::timestamp >=
      TIMESTAMPTZ '2024-11-01 00:00:00+05:30'
  AND order_date::timestamp <
      TIMESTAMPTZ '2024-11-01 00:00:00+05:30'
      + INTERVAL '4 hours'
ORDER BY order_id
LIMIT 10;
-- Exact 4h window preserved across timezones

✓ Timezone Offset Preserved Interval math guarantees 4h

STEP 04 • CORE PRINCIPLE

The Moral: Never compare dates as plain text strings across distributed servers. Always anchor with TIMESTAMPTZ and use INTERVAL arithmetic to compute exact promotional windows.

Mapping Flash Sale Operations to PostgreSQL Temporal Tools

How each operational reality of the flash sale maps to PostgreSQL date and time functions.

⏰ Clocks & Timezone Anchors

Problem: Server UTC vs Indian Standard Time mismatch.
PostgreSQL Weapon: CURRENT_DATE, NOW(), and TIMESTAMPTZ.
Captures the exact microsecond across global timezone offsets.

📅 Duration & Aging Calculations

Problem: Flash sale windows and delivery SLA tracking.
PostgreSQL Weapon: INTERVAL arithmetic and AGE().
Shift timestamps accurately: ts + INTERVAL '4 hours', and compute elapsed customer tenure.

💱 Period Snapping & Formatted Reports

Problem: Grouping sales into hours/months, and printing readable receipts.
PostgreSQL Weapon: DATE_TRUNC() and TO_CHAR().
Snaps timestamps to period starts (1st of month, start of hour) and formats dates cleanly with FMDay.

Visual Studio: The Temporal Clock & System Time

Understanding session clocks: CURRENT_DATE, CURRENT_TIME, and NOW().

INSPECT CLOCK:

RETURNED TEMPORAL VALUE:

NOW() → 2026-10-18 14:30:15.842+05:30

👁 NOW() returns the start timestamp of the current transaction (TIMESTAMPTZ). It remains frozen and constant throughout a single query transaction.

Definition & Syntax: System Clocks & Core Types

Five types hold dates and times: DATE, TIME, TIMESTAMP, TIMESTAMPTZ and INTERVAL. And three clocks tell you 'now'.

DATE / TIME Calendar & Clock

DATE stores year, month, day (4 bytes). TIME stores clock time without date (8 bytes). Neither stores timezone info.

TIMESTAMP / TZ A Moment in Time

TIMESTAMP is date + time with no zone (8 bytes). TIMESTAMPTZ stores the moment in UTC and shows it in your session's time zone. Use TIMESTAMPTZ for events like orders.

INTERVAL Elapsed Duration

Stores spans of time: years, months, days, hours, minutes, seconds (16 bytes). Used for arithmetic operations and expiry logic.

SQL DQL DEMOSystem Clocks and Timezone Setting in PostgreSQL

-- System clocks and timezone introspection
SELECT
  CURRENT_DATE AS today_date,
  CURRENT_TIME AS current_time_tz,
  CURRENT_TIMESTAMP AS full_timestamp,
  NOW() AS txn_start_timestamp,
  -- Timezone configuration
  CURRENT_SETTING('TIMEZONE') AS session_tz;

Visual Studio: The Interval Machine & Arithmetic

Perform real calendar arithmetic across months, days, and years.

TEST INTERVAL MATH:

EVALUATED TEMPORAL ARITHMETIC:

DATE '2026-10-18' + INTERVAL '14 days' → 2026-11-01 00:00:00

👁 Adding 14 days cleanly rolls over the month boundary from October into November, correctly handling calendar day lengths.

Definition & Syntax: Interval Arithmetic & AGE()

Add days and months, subtract dates, and get a human-readable age.

+ / - INTERVAL Date Shifting

date + INTERVAL 'X unit' adds time. Units include: microsecond, second, minute, hour, day, week, month, year.

AGE() Human Duration

AGE(timestamp1, timestamp2) gives years, months and days between two dates. With one argument it measures from today (CURRENT_DATE).

date - date Day Count Difference

DATE - DATE is a whole number of days ('2026-10-18'::date - '2026-10-10'::date → 8), and DATE + 3 is the date 3 days later.

SQL DEMODelivery promise and return window

-- Delivery promise and return window for each order
SELECT
  order_id,
  order_date,
  order_date + 3 AS promised_by,                   -- DATE + days = DATE
  order_date + INTERVAL '30 days' AS return_until, -- + INTERVAL = timestamp
  CURRENT_DATE - order_date AS days_since,         -- DATE - DATE = days
  AGE(order_date) AS order_age
FROM sales.orders
LIMIT 10;

Visual Studio: The Component Extractor (EXTRACT)

Deconstruct timestamps into numeric parts: Year, Month, Day, DOW, Hour, and Unix Epoch.

EXTRACT PART:

TIMESTAMP: '2026-10-18 14:30:15'

EXTRACT(YEAR FROM TIMESTAMP '2026-10-18 14:30:15') → 2026

👁 EXTRACT(YEAR ...) returns the 4-digit calendar year as a number (type numeric).

Definition & Syntax: EXTRACT() & DATE_PART()

Pull one number out of a date or time: the year, the month, the hour, the weekday.

SQL STANDARD EXTRACT(part FROM ts)

The ANSI-SQL standard syntax. Parts include: YEAR, QUARTER, MONTH, WEEK, DAY, DOW, DOY, HOUR, MINUTE, SECOND, EPOCH.

POSTGRESQL SYNTAX DATE_PART('part', ts)

PostgreSQL's own version, same parts: DATE_PART('month', ts). One difference: EXTRACT returns numeric, DATE_PART returns double precision. Prefer EXTRACT, the SQL standard.

EPOCH Unix Timestamp

EXTRACT(EPOCH FROM ts) returns total elapsed seconds since Jan 1, 1970. Divide by 86400 to convert interval differences into numeric days.

SQL DEMOEvening shoppers on web_events.page_views

-- Evening shoppers: page views between 6 PM and 10:59 PM
SELECT
  view_id,
  view_timestamp,
  EXTRACT(YEAR FROM view_timestamp) AS yr,
  EXTRACT(MONTH FROM view_timestamp) AS mon,
  EXTRACT(DOW FROM view_timestamp) AS dow,
  EXTRACT(HOUR FROM view_timestamp) AS hr,
  DATE_PART('hour', view_timestamp) AS hr_date_part
FROM web_events.page_views
WHERE EXTRACT(HOUR FROM view_timestamp) BETWEEN 18 AND 22
LIMIT 10;

Visual Studio: The DATE_TRUNC Guillotine

Snap timestamps to period boundaries: Year, Quarter, Month, Week, and Day.

SNAP PRECISION:

ORIGINAL TIMESTAMP: '2026-10-18 14:30:15'

DATE_TRUNC('month', TIMESTAMP '2026-10-18 14:30:15') → '2026-10-01 00:00:00'

👁 DATE_TRUNC('month', ...) resets day to the 1st of the month and time to midnight. Essential for monthly cohort revenue summaries.

Definition & Syntax: DATE_TRUNC() For Reporting

Snap every date down to the start of its month, quarter or year, the first step of every trend report.

SYNTAX DATE_TRUNC('unit', ts)

First argument is the precision unit (e.g. 'year', 'quarter', 'month', 'week', 'day', 'hour'). Second argument is the source timestamp.

RETURN TYPE Full Timestamp

EXTRACT gives one number (just the month); DATE_TRUNC gives a full timestamp, so January 2024 and January 2025 stay apart.

GROUP BY PREVIEW Revenue Trends

In Sessions 13 and 14, you will combine GROUP BY DATE_TRUNC('month', order_date) with SUM(net_total) to build monthly executive revenue reports.

SQL DQL DEMOMonthly Snapping with DATE_TRUNC on RetailMart Orders

-- Monthly order cohort snapping and revenue trend view
SELECT
  order_id,
  order_date,
  -- Monthly bucket timestamp
  DATE_TRUNC('month', order_date) AS month_bucket,
  -- Formatted month label
  TO_CHAR(DATE_TRUNC('month', order_date), 'Mon YYYY') AS month_label,
  net_total
FROM sales.orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'
ORDER BY order_date
LIMIT 12;

Visual Studio: The TO_CHAR Formatter & FM Modifier

Format timestamps for executive dashboards and eliminate trailing space padding.

FORMAT PATTERN:

TIMESTAMP: '2026-10-18 14:30:15'

TO_CHAR(ts, 'FMDay') → 'Sunday' (Clean 6 chars, no padding)

👁 FM (Fill Mode) eliminates trailing spaces and leading zeroes. Always use FMDay when formatting day names.

Definition & Syntax: TO_CHAR Formatting Codes

Turn dates, times and amounts into text people can read. FM removes the padding.

DATE CODES Year, Month, Day

YYYY (4-digit year), MM (01-12), Mon (Jan), Month (January), DD (01-31), Day (Sunday). Prefix with FM to remove padding.

TIME CODES Hours & Minutes

HH24 (00-23), HH12 (01-12), MI (minutes 00-59), SS (seconds 00-59), AM / PM (meridiem indicator).

NUMBER CODES Currency Formatting

9 (digit position), 0 (leading zero), , (comma separator), . (decimal point). E.g. 'FM99,99,99,999.00'.

SQL DEMOReport-ready dates and amounts

-- Order dates and amounts, formatted for a report
SELECT
  order_id,
  order_date,
  TO_CHAR(order_date, 'DD-Mon-YYYY') AS display_date,
  TO_CHAR(order_date, 'FMDay') AS day_name,
  TO_CHAR(order_date, 'FMDD FMMonth YYYY') AS long_date,
  '₹' || TO_CHAR(net_total, 'FM99,99,99,999.00') AS total_inr,
  TO_CHAR(TIMESTAMP '2026-10-18 14:30:00', 'HH12:MI AM') AS sample_time
FROM sales.orders
LIMIT 10;

Pro Tips & Common Date/Time Pitfalls

Two mistakes and two habits for dates and times.

⚠ Mistake 1: The DOW Sunday Trap

Wrong: Assuming Monday is 1 and Sunday is 7.
Why: EXTRACT(DOW FROM date) returns 0 for Sunday and 6 for Saturday.
Fixed: For weekend checks, use WHERE EXTRACT(DOW FROM date) IN (0, 6).

⚠ Mistake 2: The Padded Day Comparison

Wrong: WHERE TO_CHAR(order_date, 'Day') = 'Monday': 0 rows on RetailMart. With 'FMDay': 21,580.
Why: 'Day' outputs 'Monday ' (padded to 9 characters).
Fixed: Use TO_CHAR(date, 'FMDay') or TRIM(TO_CHAR(date, 'Day')).

✔ Pro Tip 1: DATE + 7 Works, TIMESTAMP + 7 Fails

DATE + 7 works (7 days later). TIMESTAMP + 7 is an error: write ts + INTERVAL '7 days'.

✔ Pro Tip 2: Days Elapsed Calculation

To get the exact day difference from timestamps as a float/integer, compute: ROUND(EXTRACT(EPOCH FROM (end_ts - start_ts)) / 86400.0).

Real World: RetailMart Delivery SLA Fulfillment

How long did each parcel take? sales.shipments has shipped_date and delivered_date.

🚀 LOGISTICS PIPELINE Both columns are DATEs, so delivered_date - shipped_date is a whole number of days. RetailMart parcels take 2 to 6 days. With a 5-day promise, 18,154 of the 90,005 that arrived are late.

REAL WORLDLate parcels on sales.shipments

-- Shipments that took more than 5 days to arrive
SELECT
  shipment_id,
  order_id,
  courier_name,
  shipped_date,
  delivered_date,
  delivered_date - shipped_date AS days_in_transit,
  TO_CHAR(delivered_date, 'FMDay') AS delivered_on
FROM sales.shipments
WHERE delivered_date IS NOT NULL
  AND delivered_date - shipped_date > 5
ORDER BY shipped_date DESC
LIMIT 12;

65%

🧠Practice Time

Try It Live: 4 Quick Date Challenges

Run each one, then change it for the "your turn" line. All on real RetailMart tables.

TRY ITLatest quarter (orders end 26 Feb 2026)

SELECT order_id, order_date, net_total
FROM sales.orders
WHERE order_date >= DATE_TRUNC('quarter', DATE '2026-02-26')
ORDER BY order_date DESC
LIMIT 10;

Your turn: The quarter before it: DATE_TRUNC('quarter', DATE '2026-02-26' - INTERVAL '3 months').

TRY ITWeekend sign-ups

SELECT customer_id,
  TO_CHAR(registration_date, 'FMDay') AS reg_day,
  EXTRACT(DOW FROM registration_date) AS dow
FROM customers.customers
WHERE EXTRACT(DOW FROM registration_date) IN (0, 6)
LIMIT 10;

Your turn: Now the weekday sign-ups: DOW NOT IN (0, 6).

TRY IT5-year work anniversary

SELECT employee_id, joining_date,
  joining_date + INTERVAL '5 years' AS five_years,
  TO_CHAR(joining_date + INTERVAL '5 years',
          'DD-Mon-YYYY') AS five_years_fmt
FROM stores.employees
ORDER BY joining_date
LIMIT 10;

Your turn: Who completes 5 years in 2026? (You should find 56 people.)

TRY ITOne date, many formats

SELECT order_date,
  TO_CHAR(order_date, 'DD-Mon-YYYY') AS f1,
  TO_CHAR(order_date, 'YYYY/MM/DD') AS f2,
  TO_CHAR(order_date, 'Dy, DD Mon YYYY') AS f3,
  TO_CHAR(order_date, 'FMMonth DD, YYYY') AS f4
FROM sales.orders
LIMIT 5;

Your turn: Make your own: try 'FMMonth FMDD' and 'DD/MM/YYYY'.

Pillars 1–4: Temporal Mastery Key Takeaways

Core rules and interview takeaways for Date/Time Basics.

📅 DATE_TRUNC Over EXTRACT

Use DATE_TRUNC('month', ts) when aggregating chronological trends so year context is retained. Use EXTRACT only for seasonal attributes.

⏱ Explicit Interval Shifting

Shift dates using ts + INTERVAL 'X unit'. Never add bare numbers to timestamps without casting.

💱 FM Modifier For Clean UI

Always use FMDay and FMMonth in TO_CHAR() to suppress trailing spaces that break equality comparisons.

Interview Studio: Master Temporal Questions

Five essential date & time questions interviewers ask, with production-grade answers.

Q1 · DATE_TRUNC vs EXTRACT

When do you use DATE_TRUNC and when EXTRACT? How would you build a monthly revenue report?

Answer to give:
EXTRACT returns a single numeric component (e.g., EXTRACT(MONTH FROM order_date) → 3), which loses year context unless combined with YEAR. DATE_TRUNC('month', order_date) returns a full timestamp snapped to the first microsecond of that month (e.g. '2025-03-01 00:00:00'), keeping March 2024 and March 2025 completely distinct. A monthly revenue report groups by DATE_TRUNC and formats the display label with TO_CHAR.

Self-Check Studio: 6 Temporal Retention Cards

Click any flashcard to flip and test your memory on PostgreSQL date and time behavior.

CLICK TO FLIP • SELF-CHECK

How do you calculate the exact difference between two DATE columns in days?

ANSWER: Subtract the dates directly: date_2 - date_1 returns an integer day count. Subtracting timestamps instead returns an INTERVAL.

CLICK TO FLIP • SELF-CHECK

What is the output data type of EXTRACT(YEAR FROM NOW())?

ANSWER: numeric, a number (not text, not a timestamp). You can immediately perform arithmetic or integer comparisons on it.

CLICK TO FLIP • SELF-CHECK

What does DATE_TRUNC('month', TIMESTAMP '2025-08-25 14:30:00') return?

ANSWER: '2025-08-01 00:00:00'. It snaps down to the start of the specified unit, preserving full timestamp precision.

CLICK TO FLIP • SELF-CHECK

What does the 'FM' modifier in TO_CHAR(date, 'FMDay') do?

ANSWER: Fill Mode: strips trailing spaces from day/month names (e.g. 'Monday ' -> 'Monday') and suppresses leading zeroes from day numbers.

CLICK TO FLIP • SELF-CHECK

What value does EXTRACT(DOW FROM DATE '2026-10-18') return for Sunday?

ANSWER: 0. In PostgreSQL DOW (Day Of Week), Sunday is 0, Monday is 1, through Saturday as 6. (ISO DOW uses 1 for Monday to 7 for Sunday).

CLICK TO FLIP • SELF-CHECK

How do you shift a timestamp forward by exactly 30 days?

ANSWER: Use ts + INTERVAL '30 days'. Never add bare numbers to timestamps; interval math handles varying month lengths and leap years cleanly.

Key Takeaways: The 6 Temporal Laws

Universal principles for writing robust temporal queries and date arithmetic.

01

DATE_TRUNC For Cohorts & Trends

Never group by EXTRACT(MONTH) across multi-year data. Snap to the period start with DATE_TRUNC('month', ts) to keep individual years cleanly separated.

02

'FM' Mode Prevents Padding Traps

TO_CHAR pads day and month names to 9 characters by default. Always use 'FMDay' and 'FMMonth' to prevent equality filters from silently failing.

03

INTERVAL Math For Clean Queries

Use ts + INTERVAL '30 days' or ts - INTERVAL '1 month'. PostgreSQL adjusts for leap years and daylight saving transitions automatically.

04

EXTRACT For Numbers, TO_CHAR For Display

EXTRACT returns a numeric value for computation or filtering. TO_CHAR returns formatted text for human display. Never perform mathematical operations on TO_CHAR text.

05

Range Filters Beat Function Wrappers in WHERE

Filtering WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01' allows index scans. Wrapping the column in EXTRACT() stops a normal index on it from being used.

06

Subtracting Dates Gives Days, Timestamps Give Intervals

Date subtraction (d2 - d1) yields an integer day count. Timestamp subtraction (ts2 - ts1) returns an INTERVAL. Use EPOCH extraction to convert intervals to hours.

Quick Reference: Temporal Cheatsheet

Complete PostgreSQL date and time function index with interactive ▶ Run links.

CLOCKS, INTERVALS & AGE

NOW (Transaction timestamp with TZ)▶ Run

SELECT NOW();

CURRENT_DATE (Calendar date)▶ Run

SELECT CURRENT_DATE;

INTERVAL (Temporal arithmetic)▶ Run

SELECT CURRENT_DATE + INTERVAL '14 days';

AGE (Human difference interval)▶ Run

SELECT AGE(DATE '2025-01-01');

DATE SUBTRACTION (Integer days)▶ Run

SELECT CURRENT_DATE - DATE '2025-01-01';

EXTRACTION, SNAPPING & FORMATTING

EXTRACT (Numeric sub-field)▶ Run

SELECT EXTRACT(YEAR FROM NOW());

DATE_TRUNC (Snap to time boundary)▶ Run

SELECT DATE_TRUNC('month', NOW());

EXTRACT(DOW) (Day of week)▶ Run

SELECT EXTRACT(DOW FROM CURRENT_DATE);

TO_CHAR (Indian date formatting)▶ Run

SELECT TO_CHAR(NOW(), 'DD-Mon-YYYY');

TO_CHAR FMDay (Trimmed day name)▶ Run

SELECT TO_CHAR(NOW(), 'FMDay, DD-Mon-YYYY');

Take-Home Challenge: 4 Date Queries on RetailMart

Only today's temporal functions, no joins and no GROUP BY. Write each one in the playground, then check your row count against the one given.

01

Formatted order report

Show 20 orders from 2025 (it has 69,923): display order_id, order_date formatted as '02-May-2025 (Friday)' using TO_CHAR with FMDay, and net_total.

02

Employee tenure report

From stores.employees (3,000 rows), display first_name, joining_date, and tenure formatted as 'X yrs Y mos' using AGE() and EXTRACT().

03

Orders older than 180 days

Find orders placed more than 180 days before RetailMart's last order day: display order_id, order_date and the days elapsed using DATE '2026-02-26' - order_date (1,15,643 orders).

04

Quarter labeling

Label each order with its business quarter: 'Q' || EXTRACT(QUARTER FROM order_date) || ' ' || EXTRACT(YEAR FROM order_date). Show 20 orders.

✓

Self-Correction Tip

Every task uses a function from today's Quick Reference. Open the playground, write the query, and inspect the dates and intervals.

Your SQL Journey: Module 2 Unlocked

Track your progress across the 59-session SQL course.

MODULE 1 S01–S08: Foundations COMPLETED ✓

MODULE 2 S09: String Functions COMPLETED ✓

MODULE 2 S10: Date & Time Basics YOU ARE HERE ●

MODULE 2 S11–S12: CASE, CAST & NULLs UP NEXT

MODULE 2 S13–S23: Aggregates & Joins LOCKED

MODULES 3–6 S24–S59: Advanced SQL LOCKED

Next Session Preview: Conditional Logic

Coming next in Module 2: CASE WHEN & CAST.

Conditional Logic & Casting: CASE WHEN & CAST

MODULE 2 · SESSION 11

Master CASE WHEN ... THEN ... ELSE END branching, type casting with CAST() and the :: operator. NULL handling (COALESCE, NULLIF, three-valued logic) follows in Session 12.

Conditional Logic & Casting → Review From Beginning ↺

Date & Time Basics:Temporal Arithmetic

Today’s Agenda — Date & Time Basics

Where We Left Off: Text Manipulation to Temporal Precision

Clean & Parse Text (Strings)

Dates Are NOT Strings

Mapping Flash Sale Operations to PostgreSQL Temporal Tools

Visual Studio: The Temporal Clock & System Time

Definition & Syntax: System Clocks & Core Types

Visual Studio: The Interval Machine & Arithmetic

Definition & Syntax: Interval Arithmetic & AGE()

Visual Studio: The Component Extractor (EXTRACT)

Definition & Syntax: EXTRACT() & DATE_PART()

Visual Studio: The DATE_TRUNC Guillotine

Definition & Syntax: DATE_TRUNC() For Reporting

Visual Studio: The TO_CHAR Formatter & FM Modifier

Definition & Syntax: TO_CHAR Formatting Codes

Pro Tips & Common Date/Time Pitfalls

Real World: RetailMart Delivery SLA Fulfillment

Try It Live: 4 Quick Date Challenges

Pillars 1–4: Temporal Mastery Key Takeaways

Interview Studio: Master Temporal Questions

Self-Check Studio: 6 Temporal Retention Cards

Key Takeaways: The 6 Temporal Laws

Quick Reference: Temporal Cheatsheet

Take-Home Challenge: 4 Date Queries on RetailMart

Your SQL Journey: Module 2 Unlocked

Next Session Preview: Conditional Logic

Date & Time Basics:
Temporal Arithmetic