Date & Time Functions

By the end of this lesson, you will be able to extract specific components from timestamps and perform basic date arithmetic using standard SQL functions.

What it is

Date and time functions are built-in operations that allow analysts to manipulate temporal data. In most SQL dialects (PostgreSQL, MySQL, SQL Server), these functions convert raw timestamp strings into structured data types or extract meaningful parts like year, month, day, or hour. The mental model is treating a timestamp as a composite object: YYYY-MM-DD HH:MM:SS. Functions act as filters or calculators on these components.

Related terms include truncation (rounding down to a unit), extraction (pulling a part), and interval arithmetic (adding/subtracting time).

Why it matters

• Trend Analysis: Grouping daily sales by month reveals seasonal patterns hidden in raw dates.
• Cohort Retention: Calculating the difference between signup and purchase dates determines user lifetime value.
• Data Cleaning: Converting inconsistent string formats into standardized date types ensures accurate sorting and filtering.
• Reporting Windows: Dynamically generating "last 7 days" reports without hardcoding specific dates.

Syntax or steps

The two most common patterns are extraction and truncation. While syntax varies slightly by database, the logic remains consistent.

1. Extraction: Use EXTRACT(part FROM column) or DATETIME_PART() to get integers for years, months, etc.
2. Truncation: Use DATE_TRUNC('unit', column) (PostgreSQL) or FORMATDATETIME() (SQL Server) to round timestamps to the start of a period.
3. Arithmetic: Subtract two dates to get an interval, or add an interval to a date to project future events.

Example

This example uses PostgreSQL syntax, which is widely used in modern analytics stacks. It extracts the month from a transaction date and calculates the duration between order and delivery.

SELECT 
    transaction_id,
    -- Extract the month number (1-12) from the timestamp
    EXTRACT(MONTH FROM transaction_date) AS transaction_month,
    
    -- Truncate to the first day of the month for grouping
    DATE_TRUNC('month', transaction_date) AS month_start,
    
    -- Calculate days between order and delivery
    (delivery_date - order_date) AS delivery_duration_days
FROM 
    orders
WHERE 
    transaction_date >= '2023-01-01';

Part-by-part explanation:

• EXTRACT(MONTH FROM ...): Returns an integer representing the month. Useful for filtering specific periods.
• DATE_TRUNC('month', ...): Converts any timestamp in January 2023 to 2023-01-01 00:00:00. This allows all January transactions to group together correctly.
• (delivery_date - order_date): Subtracts two date columns. In PostgreSQL, this returns an INTERVAL type, which can be cast to days for numeric analysis.

Common mistakes

• Timezone Confusion: Assuming all timestamps are UTC. Always check if your database stores local time or UTC. Mixing them causes incorrect daily aggregations.
• String Comparison: Comparing dates as strings ('2023-1-5' < '2023-10-1') fails because '1' comes before '10' alphabetically. Always cast to DATE or TIMESTAMP types first.
• Ignoring Nulls: If one date in a subtraction is NULL, the result is NULL. Use COALESCE() to handle missing values explicitly.
• Over-truncating: Using DATE_TRUNC('day') when you need hourly precision loses valuable timing data for peak-hour analysis.

When to use it

Compare EXTRACT with CAST depending on whether you need a component or a full type change.

Practice

Guided Exercise: Write a query that counts the number of orders per weekday (Monday=1, Sunday=7). Hint: Use EXTRACT(DOW FROM date).

Challenge: Find all customers who made their second purchase within 30 days of their first. Hint: Use window functions (ROW_NUMBER()) combined with date subtraction.

Quick check

Question: Why is DATE_TRUNC('week', date) preferred over EXTRACT(WEEK FROM date) for grouping sales?

Answer: EXTRACT returns an integer (e.g., 42), but weeks reset annually. Two different years might both have week 42, causing incorrect aggregation. DATE_TRUNC returns a unique timestamp for the start of each week, preserving the year context.

Summary

Date functions transform static timestamps into dynamic analytical tools. By mastering extraction and truncation, you can accurately bucket data for trends and calculate precise durations for performance metrics. Always verify timezone settings to ensure temporal accuracy.