DocsAdvancedDate Functions

Date Functions

Advanced

Concept

Date and time functions let you extract parts of dates, perform date arithmetic, format dates for display, and generate date series. They are essential for time-based reporting: grouping by month, calculating durations, filtering by date ranges, and computing age or tenure.

Common extraction functions include EXTRACT(part FROM date) which pulls out the year, month, day, hour, etc., and DATE_TRUNC(part, date) which rounds a timestamp down to a specified precision. For example, DATE_TRUNC('month', order_date) converts any date in January 2024 to 2024-01-01, making it perfect for monthly aggregation.

Date arithmetic varies by database. PostgreSQL supports interval arithmetic (date + INTERVAL '30 days'), while MySQL uses DATE_ADD and DATE_SUB. AGE() computes the difference between dates as an interval. CURRENT_DATE and NOW() provide the current date and timestamp. Understanding your database's date functions is crucial for writing portable queries.

Syntax

-- Extract parts
SELECT
  EXTRACT(YEAR FROM order_date)  AS year,
  EXTRACT(MONTH FROM order_date) AS month,
  EXTRACT(DOW FROM order_date)   AS day_of_week
FROM orders;

-- Truncate to precision
SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

-- Date arithmetic
SELECT
  order_date,
  order_date + INTERVAL '30 days' AS due_date,
  CURRENT_DATE - order_date AS days_ago
FROM orders;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Monthly revenue trend with year-over-year comparison
SELECT
  DATE_TRUNC('month', order_date) AS month,
  EXTRACT(YEAR FROM order_date) AS year,
  SUM(total_amount) AS revenue
FROM orders
GROUP BY
  DATE_TRUNC('month', order_date),
  EXTRACT(YEAR FROM order_date)
ORDER BY month;

-- Average days between first and second order per customer
SELECT
  AVG(days_to_second) AS avg_days_to_repeat
FROM (
  SELECT
    customer_id,
    order_date - LAG(order_date) OVER (
      PARTITION BY customer_id ORDER BY order_date
    ) AS days_to_second,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id ORDER BY order_date
    ) AS order_num
  FROM orders
) t
WHERE order_num = 2;

Common Pitfalls & Tips

1Date function names differ across databases. PostgreSQL uses DATE_TRUNC; MySQL uses DATE_FORMAT and YEAR()/MONTH(). Write database-specific code or use an abstraction layer.
2Timestamps include time components. Comparing a timestamp to a date may miss same-day records. Use DATE_TRUNC or cast to DATE for accurate date-only comparisons.
3Time zones matter. CURRENT_TIMESTAMP and NOW() return different results depending on the session timezone. Use AT TIME ZONE or store timestamps in UTC.

Practice date function queries