PostgreSQL Tutorial

  • Home
  • Stored Procedures
  • Triggers
  • Views
  • Interfaces
    • PostgreSQL PHP
    • PostgreSQL Python
    • PostgreSQL JDBC
  • Functions
Home / PostgreSQL Functions / PostgreSQL DATE_TRUNC Function

PostgreSQL DATE_TRUNC Function

Summary: this tutorial shows you how to use the PostgreSQL date_trunc function to truncates a timestamp or interval to a specified level of precision.

Introduction to the PostgreSQL date_trunc function

The date_trunc function truncates a TIMESTAMP or an  INTERVAL value based on a specified date part e.g., hour, week, or month and returns the truncated timestamp or interval with a level of precision.

The following illustrates the syntax of the date_trunc function:

1
date_trunc('datepart', field)

The datepart argument is the level of precision used to truncate the field, which can be one of the following:

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

The field argument is a TIMESTAMP or an INTERVAL value to truncate. It could be an expression that evaluates to a timestamp or an interval.

The date_trunc function returns a TIMESTAMP or an INTERVAL value.

PostgreSQL date_trunc examples

The following example truncates a TIMESTAMP value to hour date part:

1
SELECT DATE_TRUNC('hour', TIMESTAMP '2017-03-17 02:09:30');

The following is the output:

1
2
3
4
     date_trunc
---------------------
2017-03-17 02:00:00
(1 row)

The date_trunc function returns a result with the hour precision.

If you want to truncate the TIMESTAMP value to minute, you pass the 'minute' string as the first argument:

1
SELECT date_trunc('minute', TIMESTAMP '2017-03-17 02:09:30');

The function returns a TIMESTAMP whose precision level is minute:

1
2
3
4
date_trunc
---------------------
2017-03-17 02:09:00
(1 row)

See the following rental table in the sample database:

Rental table - PostgreSQL date_trunc function demo

You  can count the number of rentals by month by using the date_trunc function as follows:

1
2
3
4
5
6
7
8
9
SELECT
    date_trunc('month', rental_date) m,
    COUNT (rental_id)
FROM
    rental
GROUP BY
    m
ORDER BY
    m;

In this query, the date_trunc function truncates the rental date to month part. The COUNT function counts the number of rentasl and the GROUP BY clause groups the rentals by month.

1
2
3
4
5
6
7
8
        month        | 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)

Similarly, you can count the number of rentals by staff per year as follows:

1
2
3
4
5
6
7
8
9
10
SELECT
staff_id,
date_trunc('year', rental_date) y,
COUNT (rental_id) rental
FROM
rental
GROUP BY
staff_id, y
ORDER BY
staff_id

The following shows the output:

1
2
3
4
5
6
7
staff_id |          y          | rental
----------+---------------------+--------
        1 | 2006-01-01 00:00:00 |     85
        1 | 2005-01-01 00:00:00 |   7955
        2 | 2005-01-01 00:00:00 |   7907
        2 | 2006-01-01 00:00:00 |     97
(4 rows)

In this tutorial, you have learned how to use the PostgreSQL date_trunc function to truncate a timestamp or an interval value.

Previous Tutorial: PostgreSQL NOW Function
Next Tutorial: PostgreSQL ROW_NUMBER function

PostgreSQL Quick Start

  • What is PostgreSQL?
  • Install PostgreSQL
  • Connect to Database
  • Download PostgreSQL Sample Database
  • Load Sample Database
  • Explore Server and Database Objects

PostgreSQL Fundamentals

  • PostgreSQL Select
  • PostgreSQL Order By
  • PostgreSQL Select Distinct
  • PostgreSQL Where
  • PostgreSQL LIMIT
  • PostgreSQL IN
  • PostgreSQL Between
  • PostgreSQL Like
  • PostgreSQL Inner Join
  • PostgreSQL Left Join
  • PostgreSQL Full Outer Join
  • PostgreSQL Cross Join
  • PostgreSQL Natural Join
  • PostgreSQL Group By
  • PostgreSQL Having
  • PostgreSQL Union
  • PostgreSQL Intersect
  • PostgreSQL Except
  • PostgreSQL Subquery
  • PostgreSQL Insert
  • PostgreSQL Update
  • PostgreSQL Delete

Managing Table Structure

  • PostgreSQL Data Types
  • PostgreSQL Create Table
  • PostgreSQL Alter Table
  • PostgreSQL Drop Table
  • PostgreSQL Truncate Table
  • PostgreSQL CHECK Constraint
  • PostgreSQL Not-Null Constraint
  • PostgreSQL Foreign Key
  • PostgreSQL Primary Key
  • PostgreSQL UNIQUE Constraint

PostgreSQL Views

  • Managing PostgreSQL Views
  • Creating Updatable Views
  • PostgreSQL Materialized Views
  • The WITH CHECK OPTION Views
  • PostgreSQL Recursive View

PostgreSQL Triggers

  • Introduction to Trigger
  • Creating A Trigger
  • Managing PostgreSQL Triggers

About PostgreSQL Tutorial

PostgreSQLTutorial.com is a website dedicated to developers and database administrators who are working on PostgreSQL database management system.

We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. All PostgreSQL tutorials are simple, easy-to-follow and practical.

Recent PostgreSQL Tutorials

  • How To Change The Password of a PostgreSQL User
  • PostgreSQL AGE Function
  • PostgreSQL DATE_PART Function
  • PostgreSQL List Users
  • PostgreSQL NOW Function
  • PostgreSQL DATE_TRUNC Function
  • PostgreSQL TO_DATE Function: Convert String to Date
  • A Look at PostgreSQL User-defined Data Types
  • PostgreSQL Copy Database Made Easy
  • How to Get Table, Database, Indexes, Tablespace, and Value Size in PostgreSQL

More Tutorials

  • PostgreSQL Cheat Sheet
  • PostgreSQL Administration
  • PostgreSQL PHP
  • PostgreSQL Python
  • PostgreSQL JDBC
  • PostgreSQL Resources

Site Info

  • Home
  • About Us
  • Contact Us
  • Privacy Policy

Copyright © 2017 by PostgreSQL Tutorial Website. All Rights Reserved.