Postgres date_trunc() function

The Postgres date_trunc() function truncates a timestamp or interval to a specified precision.

This function is particularly useful for grouping time-series data and performing time-based calculations. For example, it can be used to generate monthly reports, analyze hourly trends, or group events by time period.

Try it on Neon!

Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.

Function signature

The date_trunc() function has the following form:

date_trunc(field, source [, time_zone ]) -> timestamp / interval

field: A string literal specifying the precision to which to truncate the input value. Valid values include microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, and millennium.
source: The timestamp or interval value to be truncated.
time_zone (optional): The timezone in which to perform the truncation. Otherwise, the default timezone is used.

The function returns a timestamp or interval value truncated to the specified precision, i.e., fields less significant than the specified precision are set to zero.

Example usage

Let's consider a table called sales that tracks daily sales data. We can use date_trunc to group sales by different time periods.

CREATE TABLE sales (
  sale_date TIMESTAMP WITH TIME ZONE,
  amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_date, amount) VALUES
  ('2024-03-01 08:30:00+00', 100.50),
  ('2024-03-01 14:45:00+00', 200.75),
  ('2024-03-02 10:15:00+00', 150.25),
  ('2024-04-15 09:00:00+00', 300.00),
  ('2024-05-20 16:30:00+00', 250.50);

-- Group sales by month
SELECT
  date_trunc('month', sale_date) AS month,
  SUM(amount) AS total_sales
FROM sales
GROUP BY date_trunc('month', sale_date)
ORDER BY month;

This query groups sales by month, summing the total sales for each month.

month          | total_sales
------------------------+-------------
 2024-03-01 00:00:00+00 |      451.50
 2024-04-01 00:00:00+00 |      300.00
 2024-05-01 00:00:00+00 |      250.50
(3 rows)

We can further refine the output by extracting the month and year from the truncated timestamp:

SELECT
  EXTRACT(YEAR FROM date_trunc('month', sale_date)) AS year,
  EXTRACT(MONTH FROM date_trunc('month', sale_date)) AS month,
  SUM(amount) AS total_sales
FROM sales
GROUP BY year, month
ORDER BY year, month;

This query groups sales by year and month, providing a more readable output:

year | month | total_sales
------+-------+-------------
 2024 |     3 |      451.50
 2024 |     4 |      300.00
 2024 |     5 |      250.50
(3 rows)

Advanced examples

Use `date_trunc` with different precisions

We can use date_trunc with different precision levels to analyze data at each granularity:

WITH sample_data(event_time) AS (
  VALUES
    ('2024-03-15 14:30:45.123456+00'::TIMESTAMP WITH TIME ZONE),
    ('2024-06-22 09:15:30.987654+00'::TIMESTAMP WITH TIME ZONE),
    ('2024-11-07 23:59:59.999999+00'::TIMESTAMP WITH TIME ZONE)
)
SELECT
  event_time,
  date_trunc('year', event_time) AS year_trunc,
  date_trunc('quarter', event_time) AS quarter_trunc,
  date_trunc('month', event_time) AS month_trunc,
  date_trunc('week', event_time) AS week_trunc,
  date_trunc('day', event_time) AS day_trunc,
  date_trunc('hour', event_time) AS hour_trunc,
  date_trunc('minute', event_time) AS minute_trunc,
  date_trunc('second', event_time) AS second_trunc,
  date_trunc('millisecond', event_time) AS millisecond_trunc
FROM sample_data;

This query demonstrates how date_trunc works with different precision levels, from year down to millisecond.

event_time           |       year_trunc       |     quarter_trunc      |      month_trunc       |       week_trunc       |       day_trunc        |       hour_trunc       |      minute_trunc      |      second_trunc      |     millisecond_trunc
-------------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+----------------------------
 2024-03-15 14:30:45.123456+00 | 2024-01-01 00:00:00+00 | 2024-01-01 00:00:00+00 | 2024-03-01 00:00:00+00 | 2024-03-11 00:00:00+00 | 2024-03-15 00:00:00+00 | 2024-03-15 14:00:00+00 | 2024-03-15 14:30:00+00 | 2024-03-15 14:30:45+00 | 2024-03-15 14:30:45.123+00
 2024-06-22 09:15:30.987654+00 | 2024-01-01 00:00:00+00 | 2024-04-01 00:00:00+00 | 2024-06-01 00:00:00+00 | 2024-06-17 00:00:00+00 | 2024-06-22 00:00:00+00 | 2024-06-22 09:00:00+00 | 2024-06-22 09:15:00+00 | 2024-06-22 09:15:30+00 | 2024-06-22 09:15:30.987+00
 2024-11-07 23:59:59.999999+00 | 2024-01-01 00:00:00+00 | 2024-10-01 00:00:00+00 | 2024-11-01 00:00:00+00 | 2024-11-04 00:00:00+00 | 2024-11-07 00:00:00+00 | 2024-11-07 23:00:00+00 | 2024-11-07 23:59:00+00 | 2024-11-07 23:59:59+00 | 2024-11-07 23:59:59.999+00
(3 rows)

Use `date_trunc` with timezones

The date_trunc function can be used with specific timezones:

SELECT
  date_trunc('day', '2024-03-15 23:30:00+00'::TIMESTAMP WITH TIME ZONE) AS utc_trunc,
  date_trunc('day', '2024-03-15 23:30:00+00'::TIMESTAMP WITH TIME ZONE, 'America/New_York') AS ny_trunc,
  date_trunc('day', '2024-03-15 23:30:00+00'::TIMESTAMP WITH TIME ZONE, 'Asia/Tokyo') AS tokyo_trunc;

This query shows how date_trunc behaves differently when truncating to the day in different timezones.

utc_trunc        |        ny_trunc        |      tokyo_trunc
------------------------+------------------------+------------------------
 2024-03-15 00:00:00+00 | 2024-03-15 04:00:00+00 | 2024-03-15 15:00:00+00
(1 row)

Use `date_trunc` for time-based analysis

Below, we use date_trunc to analyze user activity patterns for a hypothetical social media application:

CREATE TABLE user_activities (
  user_id INT,
  activity_type VARCHAR(50),
  activity_time TIMESTAMP WITH TIME ZONE
);

INSERT INTO user_activities (user_id, activity_type, activity_time) VALUES
  (1, 'login', '2024-03-01 08:30:00+00'),
  (2, 'login', '2024-03-01 12:30:00+00'),
  (2, 'post', '2024-03-03 09:15:00+00'),
  (1, 'comment', '2024-03-05 10:45:00+00'),
  (3, 'login', '2024-03-08 14:00:00+00'),
  (2, 'logout', '2024-03-08 16:30:00+00'),
  (1, 'logout', '2024-03-12 18:00:00+00'),
  (3, 'post', '2024-03-15 19:30:00+00'),
  (3, 'logout', '2024-03-18 20:45:00+00');


-- Analyze daily activity pattern
SELECT
  date_trunc('day', activity_time) AS day,
  activity_type,
  COUNT(*) AS activity_count
FROM user_activities
GROUP BY date_trunc('day', activity_time), activity_type
ORDER BY day, activity_type;

This query uses date_trunc to group user activities by each day.

day           | activity_type | activity_count
------------------------+---------------+----------------
 2024-03-01 00:00:00+00 | login         |              2
 2024-03-03 00:00:00+00 | post          |              1
 2024-03-05 00:00:00+00 | comment       |              1
 2024-03-08 00:00:00+00 | login         |              1
 2024-03-08 00:00:00+00 | logout        |              1
 2024-03-12 00:00:00+00 | logout        |              1
 2024-03-15 00:00:00+00 | post          |              1
 2024-03-18 00:00:00+00 | logout        |              1
(8 rows)

Use `date_trunc` with interval types

The date_trunc function can also be used with interval data:

SELECT
  date_trunc('hour', INTERVAL '2 days 3 hours 40 minutes') AS truncated_interval,
  date_trunc('day', '2024-03-15 23:30:00+00'::TIMESTAMPTZ - '2023-09-14 11:20:00+00'::TIMESTAMPTZ) AS truncated_day;

This query truncates the first interval to the nearest hour, while the second column truncates the difference between two timestamps to the nearest day.

truncated_interval | truncated_day
--------------------+---------------
 2 days 03:00:00    | 183 days
(1 row)

Additional considerations

Timezone awareness

When using date_trunc with timestamps, the function uses the default timezone of the session, or that specified in the input. As shown in the previous section, the truncation result can vary depending on the timezone.

Truncating intervals

When truncating intervals, the date_trunc function rounds the interval to the nearest value based on the specified precision. However, note that the output might not be intuitive and depends on how the interval is defined.

For example, the query below attempts to truncate a month from an interval specified as some number of days.

SELECT
    date_trunc('month', '183 days'::INTERVAL) AS colA,
    date_trunc('month', '2 years 3 months'::INTERVAL) AS colB;

This query outputs the following:

cola   |      colb
----------+----------------
 00:00:00 | 2 years 3 mons
(1 row)

The first input interval didn't have a month component, so even with the number of days being bigger than a month, the output is zero. The second input interval has a month component, so the output is the input interval truncated to the month.

Performance considerations

When using date_trunc in WHERE clauses or for grouping large datasets, consider creating an index on the truncated values to improve query performance:

CREATE INDEX idx_sales_month ON sales (date_trunc('month', sale_date));

This creates an index on the monthly truncated sale dates, which can speed up queries that group or filter by month.