DATE FUNCTIONS

This lesson on DATE FUNCTIONS is hands-on and example-driven. You will master the three core SQL date functions—EXTRACT, DATE_TRUNC, and DATEDIFF—to manipulate and analyze time-series data effectively. You will be able to calculate durations, group events by arbitrary time periods, and prepare date fields for advanced aggregation and reporting.

What You'll Be Able To Do

• Differentiate between extracting a numerical date part and truncating a timestamp to a period boundary.
• Select the appropriate function to calculate the duration between two events in specific units (e.g., seconds or minutes).
• Write queries that use date functions to group and aggregate data by hour, day, or month.
• Predict the resulting timestamp when a date is truncated to a specific granularity like 'day' or 'year'.

Detailed Concept Walkthrough

1. EXTRACT Function

The EXTRACT function isolates a single numerical component (like the hour or month number) from a date or timestamp value. This is primarily used to categorize or group data based on specific time periods.

• Mechanism: The function takes a specified date part (e.g., YEAR, HOUR, QUARTER) and returns that value as an integer. The original timestamp remains completely unchanged; only the requested component is isolated for use in the query.
• Syntax Rule: The syntax requires the FROM keyword to explicitly specify the source column, which is a key differentiator from similar functions in other SQL dialects. The part requested must be a valid time unit recognized by the database.
• Best Practice: Use EXTRACT when you need to aggregate data (e.g., COUNT(*) GROUP BY EXTRACT(HOUR FROM...)) or filter records based on a specific time component, such as filtering all events that occurred on a Sunday or during business hours.

SELECT
    timestamp_column,
    EXTRACT(YEAR FROM timestamp_column) AS event_year,
    EXTRACT(MONTH FROM timestamp_column) AS event_month
FROM
    sales_data
WHERE
    EXTRACT(HOUR FROM timestamp_column) BETWEEN 9 AND 17; -- Filter by business hours (9am to 5pm)

Key Takeaway: EXTRACT returns a number used for grouping or filtering, not a modified date.

2. DATE_TRUNC Function

DATE_TRUNC resets a timestamp to the beginning of a specified time period (granularity), effectively rounding the date down to the start of that period. The result is always a timestamp data type.

• Mechanism: The function takes the input timestamp and sets all time components less significant than the specified granularity to their minimum possible value. For example, if truncating to MONTH, the day component is set to 1, and all time components (hour, minute, second) are set to zero.
• Under the Hood: This process ensures that all timestamps falling within the same period (e.g., the same calendar week or month) will resolve to the exact same starting timestamp. This makes DATE_TRUNC extremely useful for grouping data chronologically.
• Execution Flow: The function first identifies the start boundary of the specified period that contains the input timestamp, then returns that boundary timestamp precisely. This allows for easy comparison and sorting of events by period.
• Best Practice: Use DATE_TRUNC when you need to group records by a period (like week or month) but still require the output to be a valid, sortable timestamp representing the start of that period, which is often necessary for charting or time-series analysis.

SELECT
    DATE_TRUNC('day', transaction_time) AS start_of_day,
    DATE_TRUNC('year', transaction_time) AS start_of_year,
    COUNT(*)
FROM
    transactions
GROUP BY
    start_of_day; -- Grouping by the start of the day for daily counts

Key Takeaway: DATE_TRUNC returns a timestamp representing the start of the period you specify.

3. DATEDIFF Function

DATEDIFF calculates the numerical duration or interval between two specific dates or timestamps based on a chosen unit of time (granularity). It is essential for measuring elapsed time.

• Mechanism: The function requires three arguments: the unit of measurement (e.g., MINUTE, SECOND, DAY), the starting timestamp, and the ending timestamp. It returns the difference as a signed integer representing the number of units elapsed.
• Syntax Rule: The order of the start and end timestamps is crucial; the function calculates end_timestamp - start_timestamp. If the start time is later than the end time, the result will be a negative number, indicating elapsed time backwards.
• Under the Hood: The function converts both timestamps into the smallest unit required by the granularity (e.g., milliseconds or seconds) and performs a simple subtraction, returning the result scaled back to the requested unit. This ensures high precision for duration calculations.
• Best Practice: Always select the smallest necessary granularity (e.g., use HOUR or MINUTE instead of DAY) to ensure precision when calculating short durations like process latency or session length, as larger units may round the result significantly.

SELECT
    process_end,
    process_start,
    DATEDIFF(MINUTE, process_start, process_end) AS duration_minutes,
    DATEDIFF(SECOND, process_start, process_end) AS duration_seconds
FROM
    system_logs
WHERE
    DATEDIFF(MINUTE, process_start, process_end) > 5; -- Find processes longer than 5 minutes

Key Takeaway: DATEDIFF measures the numerical interval between two points in time using a specified unit.

Topics Covered in DATE FUNCTIONS

1. EXTRACT Function (0:24 - 1:36) — The EXTRACT function pulls a specific numerical part from a timestamp for use in aggregation.
2. DATE_PART Mention (0:26) — DATE_PART is mentioned as being similar to EXTRACT but is not taught in detail.
3. DATE_TRUNC Function (1:39 - 2:40) — DATE_TRUNC resets a timestamp to the beginning of a specified granularity period.
4. Grouping Context (1:36) — Extracted or truncated date parts are essential for grouping data in analytical queries.
5. DATEDIFF Function (2:44 - 3:50) — DATEDIFF calculates the numerical interval between two timestamps based on a chosen unit.

SQL Cheat Sheet

• EXTRACT(part FROM ts) — Pulls a specific numerical component from a timestamp

  SELECT EXTRACT(HOUR FROM event_ts);
  

• DATE_TRUNC(unit, ts) — Resets timestamp to the start of the specified unit

  SELECT DATE_TRUNC('month', event_ts);
  

• DATEDIFF(unit, start, end) — Calculates numerical difference between two dates/times

  SELECT DATEDIFF(MINUTE, start_ts, end_ts);
  

• DATE_PART — Similar to EXTRACT, used in some SQL dialects

Comparison Table

Common Pitfalls

• Mistake: Using EXTRACT when you need the start of the period for grouping. Avoid: Use DATE_TRUNC when grouping by month or day.
• Mistake: Confusing the order of start and end in DATEDIFF. Avoid: Always place the earlier timestamp as the second argument (start).
• Mistake: Assuming DATE_TRUNC returns a simple date type. Avoid: The output is a full timestamp, including zeroed time components.
• Mistake: Using DATEDIFF with too large a granularity. Avoid: Select the smallest unit needed for required precision (e.g., MINUTE).

FAQs

• Why use DATE_TRUNC instead of just EXTRACT(YEAR)? DATE_TRUNC returns a sortable timestamp that represents the exact start of the year (2024-01-01 00:00:00), whereas EXTRACT returns only the year number (2024).
• Can I use EXTRACT for grouping? Yes, EXTRACT is ideal for grouping, such as counting events by the hour of the day or the day of the week, as it provides a clean numerical category.
• What happens to the time components if I truncate to 'day'? The hour, minute, and second components of the timestamp are all set to 00:00:00, effectively giving you the midnight timestamp for that day.
• Is DATE_PART the same as EXTRACT? They are very similar and often interchangeable depending on the SQL dialect, but EXTRACT is the function detailed in this lesson.