Date functions and time series

E-commerce runs on time: when was the order placed, how long did delivery take, what are monthly trends? Date functions let you extract parts of timestamps, truncate to periods, and calculate time differences.

PostgreSQL has two timestamp types: TIMESTAMP (no time zone) and TIMESTAMPTZ (with time zone). The Olist data uses TIMESTAMP without time zone. For production systems, always use TIMESTAMPTZ so PostgreSQL handles conversions automatically.

date_extract.sql
sql
-- EXTRACT parts from a timestamp
SELECT
  order_id,
  order_purchase_timestamp,
  EXTRACT(YEAR FROM order_purchase_timestamp) AS order_year,
  EXTRACT(MONTH FROM order_purchase_timestamp) AS order_month,
  EXTRACT(DOW FROM order_purchase_timestamp) AS day_of_week
FROM orders
LIMIT 10;

EXTRACT pulls a specific part from a date/timestamp. DOW returns 0=Sunday through 6=Saturday.

EXTRACT gives you a single number, but for grouping data by month or week, DATE_TRUNC is more useful because it preserves the full date structure.

date_trunc.sql
sql
-- DATE_TRUNC for monthly order counts
SELECT
  DATE_TRUNC('month', order_purchase_timestamp) AS order_month,
  COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_purchase_timestamp)
ORDER BY order_month;

DATE_TRUNC rounds a timestamp down to the specified precision. "month" turns 2018-03-15 14:30:00 into 2018-03-01 00:00:00.

You can also do arithmetic with dates. Subtracting two timestamps gives you an interval, which is perfect for calculating delivery times.

date_math.sql
sql
-- Date arithmetic: delivery time in days
SELECT
  order_id,
  order_purchase_timestamp,
  order_delivered_customer_date,
  order_delivered_customer_date - order_purchase_timestamp AS delivery_interval,
  EXTRACT(DAY FROM (order_delivered_customer_date - order_purchase_timestamp)) AS delivery_days
FROM orders
WHERE order_delivered_customer_date IS NOT NULL
ORDER BY delivery_days DESC
LIMIT 10;

-- AGE function for a human-readable interval
SELECT
  order_id,
  AGE(order_delivered_customer_date, order_purchase_timestamp) AS delivery_time
FROM orders
WHERE order_delivered_customer_date IS NOT NULL
LIMIT 10;

Subtracting timestamps gives an interval. EXTRACT(DAY FROM interval) gets just the days. AGE gives a human-readable string.

Use DATE_TRUNC for grouping (monthly totals, quarterly reports) because it returns a proper timestamp you can GROUP BY and ORDER BY. Use EXTRACT for filtering (WHERE EXTRACT(YEAR) = 2018) or when you need the numeric value of a specific part.

Fill in the blanks: Complete the date query

Loading practice…

Flashcards: Flashcards

Loading practice…

Quiz: Quiz

Loading practice…

Code playground: Average delivery days by state per quarter

Loading practice…