Using the DATE_TRUNC Function in PostgreSQL

Introduction

In PostgreSQL, it’s possible to truncate, or round off, a timestamp value to a specific level of precision. For example, you may want to truncate your date and time value to the nearest hour or even the nearest week. The DATE_TRUNC() function makes it possible to handle this truncation. In this article, we’ll provide an introduction to the DATE_TRUNC() function in PostgreSQL and look at some examples of how to use it.

Prerequisite

There’s only one prerequisite that needs to be in place for this tutorial: You’ll need to have PostgreSQL installed and properly configured on your machine.

PostgreSQL DATE_TRUNC() Syntax

Let’s begin by looking at the basic syntax for the DATE_TRUNC() function:

date_trunc('interval',field)

The DATE_TRUNC() function requires two arguments: the interval and the field. The function will return a timestamp or an interval.

DATE_TRUNC() Function Interval

As we mentioned above, the DATE_TRUNC() function requires a valid interval argument. This argument represents the defined level of precision that will be used to round or truncate the field.

Shown below are some of the units of time that can be used for the value of interval:

  • century
  • day
  • decade
  • hour
  • microsecond
  • millennium
  • millisecond
  • minute
  • month
  • quarter
  • second
  • week
  • year

PostgreSQL DATE_TRUNC() Practical Examples

In this section, we’ll provide a series of examples to show you the different ways that the DATE_TRUNC() function can be used.

NOTE: All of the examples in this section will be using this timestamp: 2019-12-20 14:39:45.

DATE_TRUNC() HOUR precision

Our first example will illustrate how to use DATE_TRUNC() to truncate a timestamp to the nearest HOUR:

SELECT DATE_TRUNC('hour', TIMESTAMP '2019-12-20 14:39:45');

The result will look like the following:

     date_trunc
---------------------
 2019-12-20 14:00:00
(1 row)

DATE_TRUNC() MINUTE precision

Next, we’ll look at an example that uses DATE_TRUNC() to round a timestamp to the MINUTE level of precision.

SELECT DATE_TRUNC('minute', TIMESTAMP '2019-12-20 14:39:45');

Our result should look something like this:

    date_trunc
---------------------
 2019-12-20 14:39:00
(1 row)

DATE_TRUNC() DAY precision

In the example shown below, we see how to use DATE_TRUNC() to round a timestamp value to the nearest DAY:

SELECT DATE_TRUNC('day', TIMESTAMP '2019-12-20 14:39:45');

After executing the query, we should see something like this:

     date_trunc
---------------------
 2019-12-20 00:00:00
(1 row)

Creating A Sample Dataset

Our next examples will feature a more complex application of the DATE_TRUNC() function. We’ll need to create a sample dataset to use in these examples, so we’ll perform the following steps:

  • First, we’ll create a database:
create database hoteldb;
  • Then, we’ll connect to that database using the following command:
\c hoteldb;
  • After that, we’ll create a table:
CREATE TABLE rental
(
    rent_id INT NOT NULL,
    customer_id INT NOT NULL,
    checkin_date TIMESTAMP,
    employee_id INT,
    checkout_date TIMESTAMP
);
  • Our final step will be to insert sample data into that table:
INSERT INTO Rental (rent_id,customer_id,checkin_date,employee_id,checkout_date)
VALUES
(1,5382,'2019-01-24 22:10:34',1,'2019-01-25 10:10:48'),
(2,5383,'2019-01-25 19:06:52',1,'2019-01-26 07:06:52'),
(3,5384,'2019-01-26 16:06:34',2,'2019-01-27 04:06:33'),
(4,5385,'2019-01-27 07:09:34',2,'2019-01-28 19:09:34'),
(5,5386,'2019-01-28 10:27:44',1,'2019-01-29 22:27:34'),
(6,5387,'2019-01-29 15:22:34',2,'2019-01-30 03:06:57'),
(7,5388,'2019-01-30 21:12:47',1,'2019-01-31 09:06:15'),
(8,5389,'2019-01-31 07:23:14',2,'2019-02-01 19:06:24'),
(9,5390,'2019-02-01 22:06:34',2,'2019-02-02 11:09:34'),
(10,5391,'2019-02-02 13:46:01',1,'2019-02-03 01:01:35');

Our table will look like the following:

hoteldb=# select * from rental;
 rent_id | customer_id |    checkin_date     | employee_id |    checkout_date
---------+-------------+---------------------+-------------+---------------------
       1 |        5382 | 2019-01-24 22:10:34 |           1 | 2019-01-25 10:10:48
       2 |        5383 | 2019-01-25 19:06:52 |           1 | 2019-01-26 07:06:52
       3 |        5384 | 2019-01-26 16:06:34 |           2 | 2019-01-27 04:06:33
       4 |        5385 | 2019-01-27 07:09:34 |           2 | 2019-01-28 19:09:34
       5 |        5386 | 2019-01-28 10:27:44 |           1 | 2019-01-29 22:27:34
       6 |        5387 | 2019-01-29 15:22:34 |           2 | 2019-01-30 03:06:57
       7 |        5388 | 2019-01-30 21:12:47 |           1 | 2019-01-31 09:06:15
       8 |        5389 | 2019-01-31 07:23:14 |           2 | 2019-02-01 19:06:24
       9 |        5390 | 2019-02-01 22:06:34 |           2 | 2019-02-02 11:09:34
      10 |        5391 | 2019-02-02 13:46:01 |           1 | 2019-02-03 01:01:35
(10 rows)

DATE_TRUNC() Aggregate Example

Now that we’ve created a sample dataset, let’s try to perform a basic aggregate against a specified time interval:

SELECT
    employee_id,
    date_trunc('month', checkin_date) m,
    COUNT(rent_id) rental
FROM
    rental
GROUP BY
    employee_id, m
ORDER BY
    employee_id;

The query shown above will count the number of check-ins by staff per month. Our result should look something like this:

 employee_id |          m          | rental
-------------+---------------------+--------
           1 | 2019-02-01 00:00:00 |      1
           1 | 2019-01-01 00:00:00 |      4
           2 | 2019-02-01 00:00:00 |      1
           2 | 2019-01-01 00:00:00 |      4
(4 rows)

Conclusion

When you retrieve date and time data from PostgreSQL, you don’t always want the exact timestamp value. Fortunately, it’s possible to round or truncate timestamp values with the help of the DATE_TRUNC() function in PostgreSQL. In this tutorial, we provided an overview of this function and showed multiple examples of how to use it. With our examples to guide you, you’ll have no problem using the DATE_TRUNC() function in your own PostgreSQL queries.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.