CodeRang
SQL topics

SQL Date Functions with Examples

SQL date functions help extract date parts, group timestamps, calculate periods, and filter precise date ranges. This example focuses on calendar-month grouping, timestamp boundaries, and time-zone-aware reporting.

Updated · SQL Arena team

SQL from zero → Analyst trackAll problems

What you will practice

  • Group timestamps by week or month with DATE_TRUNC
  • Read date parts with EXTRACT
  • Build relative ranges with INTERVAL
  • Use half-open date and timestamp ranges
  • Handle timestamptz values in a chosen time zone

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Count monthly events in the Berlin time zone

The events table stores account activity with a timestamptz start time. Return events from March through May 2026 using Berlin local time. The output must contain the local month start as month_start, the number of events as event_count, and the number of distinct accounts as accounts. June must not be included.

Query 1PostgreSQLv16
SELECT
  DATE_TRUNC('month', starts_at AT TIME ZONE 'Europe/Berlin')::date AS month_start,
  COUNT(*) AS event_count,
  COUNT(DISTINCT account_id) AS accounts
FROM events
WHERE starts_at >= TIMESTAMPTZ '2026-03-01 00:00:00 Europe/Berlin'
  AND starts_at < TIMESTAMPTZ '2026-06-01 00:00:00 Europe/Berlin'
GROUP BY 1
ORDER BY 1;
Submit
Resultaccountsevents
Answer accepted

SQL date functions in a practical query

The WHERE clause uses a half-open interval: the start of March is included, while the start of June is excluded. This pattern is safer than trying to construct an inclusive timestamp for the final second of May. It also works cleanly when the column contains fractional seconds. The same idea scales to a single day, a quarter, or a full year.

Because starts_at is timestamptz, the calendar month depends on the reporting time zone. AT TIME ZONE 'Europe/Berlin' turns the stored instant into Berlin local date and time before DATE_TRUNC chooses the month boundary. Casting the truncated value to date keeps the output compact. This order matters for events close to midnight, where UTC and local calendar dates may differ.

GROUP BY 1 groups the rows by the calculated month. COUNT(*) measures events, while COUNT(DISTINCT account_id) counts participating accounts. EXTRACT is better when you need a single numeric component such as the hour or weekday. INTERVAL is useful for relative calculations such as timestamp_col >= current_date - INTERVAL '30 days' or advancing a month boundary by INTERVAL '1 month'.

SQL date functions vary between database systems, but the underlying tasks are similar: extract year or month values, calculate differences, truncate timestamps to a reporting period, and obtain the current date. Date formatting should be treated separately from date storage because converting a DATE or TIMESTAMP to formatted text changes its representation, not the underlying concept. For range filtering, half-open intervals are particularly reliable: include the starting boundary with >= and exclude the next boundary with <. This avoids guessing the final possible time value of a day or month.

SQLPostgreSQL v16
SELECT
  DATE_TRUNC('month', starts_at AT TIME ZONE 'Europe/Berlin')::date AS month_start,
  COUNT(*) AS event_count,
  COUNT(DISTINCT account_id) AS accounts
FROM events
WHERE starts_at >= TIMESTAMPTZ '2026-03-01 00:00:00 Europe/Berlin'
  AND starts_at < TIMESTAMPTZ '2026-06-01 00:00:00 Europe/Berlin'
GROUP BY 1
ORDER BY 1;
Query result
month_startevent_countaccounts
2026-03-0122
2026-04-0133
2026-05-0122

Common SQL date function mistakes

01

Using an inclusive end-of-month timestamp

A condition ending at '2026-05-31 23:59:59' can still miss values with greater timestamp precision. Use a lower bound with >= and the next period boundary with <. The query becomes easier to reason about and does not depend on timestamp precision.

02

Grouping before choosing the time zone

A timestamptz value can belong to different calendar dates in different zones. If the report is defined in local business time, convert to that zone before deriving the day, week, or month bucket.

03

Grouping only by month number

EXTRACT(MONTH FROM created_at) produces values from 1 to 12, so January from different years is merged into one group. Use DATE_TRUNC('month', created_at) or include both year and month in the grouping key.

Practice problems on this topic

SQL from zero → Analyst track

The next step is to practice several kinds of date conditions instead of repeating one pattern. SQL Arena includes free Easy exercises such as finding patients born in May, purging stale audit rows, selecting a user's most recent login in a given year, and detecting sessions that remained open for more than a day. Harder tasks add reporting windows and month- or year-based spending calculations. For a broader sequence of analytics skills, continue with the track “SQL from Zero → Analyst.”

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What are SQL date functions?

SQL date functions are functions and expressions used to work with DATE, TIMESTAMP, and related values. They can extract date parts, calculate differences, truncate values to periods, and obtain current date or time values.

What are common date functions in SQL with examples?

Common operations include extracting a year or month, truncating a timestamp to a reporting period, getting the current date, and calculating a difference between two dates. Exact function names and syntax vary by SQL dialect.

How do I extract a year from a date in SQL?

Many SQL systems support syntax such as EXTRACT(YEAR FROM date_value). Other systems may provide a dedicated year function.

How do I calculate the difference between dates in SQL?

The method depends on the database. Some systems provide a date-difference function, while others support date subtraction or interval expressions.

How do I filter dates correctly in SQL?

A reliable pattern is a half-open interval such as date_value >= start_date AND date_value < next_boundary. This avoids problems with unknown time components at the end of a day or reporting period.

See also

Practice SQL date functions

SQL Arena has 1,192 automatically checked exercises, with separate PostgreSQL, MySQL, ClickHouse, and BigQuery sandboxes. The free tier includes 292 exercises without a card, and the “SQL from Zero → Analyst” track provides a structured next step.

Practice SQL date exercises

More practice from CodeRang