SQL for Analytics
Intermediate 13 min read SQL for Analytics

SQL Date and Time Functions

Getting time-based analysis right

Dates are the unit of most analytics. Truncation to day/week/month, date arithmetic, timezone conversions and correct boundaries are the difference between a clean weekly trend and a misleading one.

DATE_TRUNCtimezonesdate arithmeticdate functionsweek boundariesUTC
Prerequisites: SQL for Analysts
Before we start

What you will learn

  • Truncate timestamps to day/week/month with DATE_TRUNC
  • Do date arithmetic safely across month/year boundaries
  • Define timezone handling and test boundary cases
Introduction

What is this?

SQL date functions bucket, compare and shift timestamps: DATE_TRUNC('month', ts) floors to the period; date_part/EXTRACT pulls components; date arithmetic (+/- INTERVAL) shifts calendars; and timezone conversions (CONVERT_TZ, AT TIME ZONE) control whether timestamps mean UTC or local. Correct analytics require deciding the timezone once and truncating without off-by-one edges.

Motivation

Why do you need it?

  • Trends and cohorts live at day/week/month boundaries — the wrong bucket ruins them.
  • INTERVAL math across year ends is where naive date filters silently break.
  • PyWeek-to-week comparisons depend on a consistent week start (ISO vs US Sunday).
  • Timezone mixing (UTC events vs local billing) creates phantom daily dips.
Think about it

Mental model

Imagine

Cutting a long pie into calender pods

Think of

DATE_TRUNC

You can slice by day, by month, by quarter — the pie is the same timestamps, but the slices define the analysis units you count against. Slice consistently and each pod represents one period.

Mechanics

How it works

  • DATE_TRUNC('month', ts) returns the first instant of that month; same for 'day', 'week', 'year'.
  • date_part('dow', ts) pulls weekday; EXTRACT(year FROM ts) pulls components.
  • ts + INTERVAL '1 month' shifts calendar months safely (Jan 31 + 1mo = Feb 28/29).
  • AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' converts zones properly.
  • Where possible, store UTC and convert only at the presentation boundary.

Truncating a raw event stream into periods.

Raw ts
TRUNC day
TRUNC month
TRUNC week(ISO)
Group
Hands-on

Examples

Weekly trend that survives years

Weekly trend that survives years
SELECT
  DATE_TRUNC('week', ts)  AS week_start,
  COUNT(DISTINCT user_id) AS active_users
FROM events
WHERE ts >= NOW() - INTERVAL '180 days'
  AND ts <  DATE_TRUNC('week', NOW())
GROUP BY 1
ORDER BY 1;
 
-- Timezone-aware boundaries
SELECT DATE_TRUNC('day', ts AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York') AS local_day
FROM events;

Truncating to week gives stable Monday-start buckets; the timezone double-cast converts UTC to local before bucketing.

Real world

In production

  • DAU/WAU computed from DATE_TRUNC day/week buckets.
  • Monthly cohort tables built with DATE_TRUNC('month', created_at).
  • A revenue report that converts to the finance team's local business day.
Guidelines

Best practices

  • Pick ONE canonical timezone (usually UTC) for storage and analytics.
  • Truncate to the same grain for comparisons — never day-vs-month mixed.
  • Define your week start and use the engine's ISO option consistently.
  • Use exclusive upper bounds (ts < '2026-02-01') so days don't overlap.
Watch out

Common mistakes

  • Comparing a UTC-bucketed series to a local-bucketed series.
  • Using BETWEEN with naive day boundaries overlapping midnight.
  • Assuming date_part('dow') returns the same base across engines.
  • Shifting with day arithmetic and having Dec/Jan months split weirdly.
When things break

Debugging tips

  • A phantom dip at month end? Check truncation and timezone, not the data.
  • Week comparisons off by one day? Confirm the engine's week start.
  • Boundary overlap? Switch to half-open intervals [start, end).
  • Print one timestamp through all your date transforms to see where it lands.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

What does DATE_TRUNC('month', '2026-01-14 13:45') return?

2

Timezone handling is irrelevant as long as all dates are stored consistently.

Go deeper

Official documentation

PostgreSQL date/time functions
docs