DATE_TRUNC vs EXTRACT in SQL: Date Functions & Syntax Guide

Almost every high-value business metric depends on time: Monthly Active Users (MAU), Year-over-Year (YoY) revenue growth, 30-day retention cohorts, and daily order volume.

Yet dates are notorious for generating subtle, silent bugs. A misplaced BETWEEN clause drops an entire day's worth of transactions. A naive GROUP BY EXTRACT(MONTH...) accidentally lumps January 2022 together with January 2025. And forgetting timezone offsets reports midnight orders on the wrong calendar day.

In this guide, we master SQL date manipulation from the ground up: comparing EXTRACT vs DATE_TRUNC, performing dynamic date math with INTERVAL, avoiding the BETWEEN timestamp trap, and laying the groundwork for cohort analysis. Every query is executed against a verified PostgreSQL e-commerce dataset containing 2,000 orders spanning from January 2022 to mid-2025. For foundational query building, check our SQL CTE Guide and SQL Window Functions Guide.

1. EXTRACT: Isolating Date Components for Seasonality

The EXTRACT() function pulls out a specific numeric piece (year, month, day of week, hour) from a date or timestamp.

Syntax

EXTRACT(field FROM source)

Orders Per Calendar Year

Let's group our 2,000 orders by year:

SELECT EXTRACT(YEAR FROM order_date)::int AS order_year,
       COUNT(*)                           AS total_orders
FROM orders
GROUP BY order_year
ORDER BY order_year;

The Output

Notice that 2025 has fewer orders (263) because the dataset ends on June 1, 2025.

Analyzing Day-of-Week Seasonality

EXTRACT(DOW FROM order_date) returns the day of the week as an integer (0 for Sunday, 6 for Saturday):

SELECT EXTRACT(DOW FROM order_date)::int AS day_of_week,
       COUNT(*)                          AS order_volume
FROM orders
WHERE status = 'completed'
GROUP BY day_of_week
ORDER BY day_of_week;

EXTRACT is perfect for answering questions like: "Which day of the week has the highest purchase volume across all historical data?" However, because it discards the year, you cannot use EXTRACT alone to build a chronological timeline.

2. DATE_TRUNC: Building Continuous Trendlines

To track monthly or weekly trends over time, you must keep the year and month bound together. DATE_TRUNC() rounds a timestamp down to the beginning of the specified interval.

Truncation Examples

• DATE_TRUNC('month', '2024-03-24 15:30:00'::timestamp) → 2024-03-01 00:00:00
• DATE_TRUNC('year', '2024-03-24 15:30:00'::timestamp) → 2024-01-01 00:00:00
• DATE_TRUNC('day', '2024-03-24 15:30:00'::timestamp) → 2024-03-24 00:00:00

Monthly Revenue Timeline Query

SELECT DATE_TRUNC('month', order_date)::date AS order_month,
       COUNT(*)                               AS completed_orders,
       ROUND(SUM(total_amount), 2)            AS monthly_revenue
FROM orders
WHERE status = 'completed'
GROUP BY order_month
ORDER BY order_month;

The Output (First 4 Months)

3. The BETWEEN Trap on Dates: Why Production Queries Drop Data

Filtering date ranges is where many analysts accidentally lose data. Look at this query designed to pull all orders in January 2024:

-- ⚠️ THE TEMPTING (BUT DANGEROUS) WAY
SELECT COUNT(*) AS total_orders
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

Result in our dataset: 47 orders.

Now look at the safe half-open range:

-- ✅ THE BULLETPROOF WAY: Half-Open Range
SELECT COUNT(*) AS total_orders
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date <  '2024-02-01';

Result in our dataset: 47 orders.

Why They Match Here — And Why It Breaks in Production

In our synthetic educational dataset, every order_date is stored exactly at midnight (00:00:00). Therefore, the 31st at midnight matches both queries.

However, in production systems, timestamps contain hours, minutes, and seconds (e.g. 2024-01-31 16:45:12).

BETWEEN '2024-01-01' AND '2024-01-31'
is expanded by SQL to:
>= '2024-01-01 00:00:00' AND <= '2024-01-31 00:00:00'

Any order placed at 10:00 AM on January 31st is strictly greater than 2024-01-31 00:00:00. BETWEEN silently drops the entire last day of the month without any warning or error.

4. Date Math with INTERVAL

SQL allows dynamic arithmetic using the INTERVAL keyword, enabling flexible rolling windows without hardcoded dates.

Rolling 90-Day Window Filter

SELECT COUNT(*)                   AS orders_last_90d,
       ROUND(SUM(total_amount), 2) AS revenue_last_90d
FROM orders
WHERE order_date >= DATE '2025-06-01' - INTERVAL '90 days'
  AND order_date <  DATE '2025-06-01'
  AND status = 'completed';

In a production dashboard, replace the static '2025-06-01' with CURRENT_DATE:

WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
  AND order_date <  CURRENT_DATE

Common INTERVAL Expressions

• INTERVAL '7 days' / INTERVAL '1 week'
• INTERVAL '1 month' / INTERVAL '3 months'
• INTERVAL '1 year'
• INTERVAL '2 hours 30 minutes'

5. Month-over-Month (MoM) Growth Analysis

Combining DATE_TRUNC with the LAG() window function allows you to calculate month-over-month growth in a clean, reproducible query.

WITH monthly_metrics AS (
    SELECT DATE_TRUNC('month', order_date)::date AS order_month,
           ROUND(SUM(total_amount), 2)           AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY order_month
)
SELECT order_month,
       revenue,
       LAG(revenue, 1) OVER (ORDER BY order_month) AS prev_month_revenue,
       ROUND(revenue - LAG(revenue, 1) OVER (ORDER BY order_month), 2) AS mom_change,
       ROUND(
           ((revenue - LAG(revenue, 1) OVER (ORDER BY order_month)) / 
           NULLIF(LAG(revenue, 1) OVER (ORDER BY order_month), 0)) * 100, 
           1
       ) AS mom_growth_pct
FROM monthly_metrics
ORDER BY order_month;

The Output (First 3 Months)

6. Cohort Analysis Foundation: First-Order Month

In product analytics, cohort retention tracks groups of customers who signed up or made their first purchase in the same month.

Finding each customer's cohort acquisition month requires MIN(DATE_TRUNC('month', order_date)):

SELECT customer_id,
       MIN(DATE_TRUNC('month', order_date))::date AS first_order_month
FROM orders
WHERE customer_id IS NOT NULL
GROUP BY customer_id
ORDER BY first_order_month, customer_id
LIMIT 10;

In our dataset, the first-order cohorts start in January 2022 with 47 customers, followed by 43 customers in February 2022, forming the baseline for cohort retention curves.

7. Date Function Decision Guide

8. Summary & Practice

• Use DATE_TRUNC() when preserving continuous timelines for charts, dashboards, and MoM calculations.
• Use EXTRACT() when isolating seasonal cycles across multiple years.
• Avoid BETWEEN for timestamp filtering—always use >= start AND < next_period to prevent losing the final day's transactions.
• Cast truncated timestamps with ::date for clean, standards-compliant date outputs.

Practice live date queries, MoM growth calculations, and cohort analyses in the Interactive SQL Practice Sandbox or build end-to-end data pipelines in our Data Analyst Career Track. To master query structure and readability, explore our SQL CTE Guide.