PostgreSQL DATE_TRUNC() Function

Summary: This tutorial shows you how to use the PostgreSQL DATE_TRUNC() function to truncate a timestamp or interval to a specified precision.

Introduction to the PostgreSQL DATE_TRUNC() function

The DATE_TRUNC() function truncates a TIMESTAMP, a TIMESTAMP WITH TIME ZONE, or an INTERVAL value to a specified precision.

Here’s the basic syntax of the DATE_TRUNC function:

DATE_TRUNC(field, source [,time_zone])

In this syntax:

source

source is a value or an expression of type timestamp, timestamp with time zone, or interval. If you use a value of the date or time type, the function will cast it automatically to timestamp or interval respectively.

field

field specifies the to which precision to truncate the source.

Here are the valid values for the field:

• millennium
• century
• decade
• year
• quarter
• month
• week
• day
• hour
• minute
• second
• milliseconds
• microseconds

time_zone

time_zone specifies the time zone in which the function will perform the truncation. The time_zone argument is the default.

If you omit the time_zone, the function will truncate the source based on the current time zone setting.

The DATE_TRUNC function returns a TIMESTAMP or an INTERVAL value.

PostgreSQL DATE_TRUNC() function examples

Let’s explore some examples of using the DATE_TRUNC() function.

1) Basic PostgreSQL DATE_TRUNC() function example

The following example uses the DATE_TRUNC() function to truncate a TIMESTAMP value to hour part:

SELECT DATE_TRUNC('hour', TIMESTAMP '2017年03月17日 02:09:30');

Output:

date_trunc
---------------------
 2017年03月17日 02:00:00
(1 row)

In this example, the DATE_TRUNC() function returns a timestamp with the hour precision.

If you want to truncate a TIMESTAMP value to a minute, you use the 'minute' field as shown in the following example:

SELECT DATE_TRUNC('minute', TIMESTAMP '2017年03月17日 02:09:30');

The function returns a TIMESTAMP with the precision is minute:

date_trunc
---------------------
 2017年03月17日 02:09:00
(1 row)

2) Using PostgreSQL DATE_TRUNC() function with table data

See the following rental table in the sample database:

[画像:Rental table - PostgreSQL date_trunc function demo] The following example uses the DATE_TRUNC() function to retrieve the number of rentals by month from the rental table:

SELECT
 DATE_TRUNC('month', rental_date) m,
 COUNT (rental_id)
FROM
 rental
GROUP BY
 m
ORDER BY
 m;

Output:

m | count
---------------------+-------
 2005年05月01日 00:00:00 | 1156
 2005年06月01日 00:00:00 | 2311
 2005年07月01日 00:00:00 | 6709
 2005年08月01日 00:00:00 | 5686
 2006年02月01日 00:00:00 | 182
(5 rows)

This query retrieves the month of each rental date and counts the number of rentals each month from the rental table. It then groups the counts by month and sorts the result set by month.

If you want to count the rentals by week, you can pass the week to the DATE_TRUNC() function as follows:

SELECT
 DATE_TRUNC('week', rental_date) week,
 COUNT (rental_id)
FROM
 rental
GROUP BY
 week
ORDER BY
 week;

Output:

week | count
---------------------+-------
 2005年05月23日 00:00:00 | 835
 2005年05月30日 00:00:00 | 321
 2005年06月13日 00:00:00 | 1705
 2005年06月20日 00:00:00 | 606
 2005年07月04日 00:00:00 | 2497
 2005年07月11日 00:00:00 | 956
 2005年07月25日 00:00:00 | 3256
 2005年08月01日 00:00:00 | 1314
 2005年08月15日 00:00:00 | 3148
 2005年08月22日 00:00:00 | 1224
 2006年02月13日 00:00:00 | 182
(11 rows)

The following example uses the DATE_TRUNC() function to count the number of rentals by staff per year:

SELECT
	staff_id,
	date_trunc('year', rental_date) y,
	COUNT (rental_id) rental
FROM
	rental
GROUP BY
	staff_id, y
ORDER BY
	staff_id;

Output:

staff_id | y | rental
----------+---------------------+--------
 1 | 2006年01月01日 00:00:00 | 85
 1 | 2005年01月01日 00:00:00 | 7955
 2 | 2006年01月01日 00:00:00 | 97
 2 | 2005年01月01日 00:00:00 | 7907
(4 rows)

Summary

• Use the PostgreSQL DATE_TRUNC function to truncate a timestamp or an interval value to a specified level of precision