Postgresql date_trunc function + Examples

In this Postgresql tutorial, we will learn about the “Postgresql date_trunc function” to extract specific parts of the date and time like a second, minute, hour, date, day and month, etc.

We are going to cover the following topics:

• Postgresql date_trunc function
• Postgresql date_trunc minutes
• Postgresql date_trunc week
• Postgresql date_trunc day
• Postgresql date_trunc timezone
• Postgresql date_trunc hour
• Postgresql date_trunc without time
• Postgresql date_trunc month and year
• Postgresql date_trunc second

Sometimes when we want to query the database records for a specific time or maybe you are an analyst in the XYZ company of the USA who wants to generate a report of sales of every year, month, and week.

Maybe business analysts want to use it for web traffic or trend analysis that are coming from the United State of America because these things are based on timestamps and that tell us when things happen.

In that case, the date_trunc function is very useful.

Postgresql date_trunc function

In Postgresql, date_trunc is used to extract and truncate the specific datepart ( level of precision ) of the date and time like second, minutes, hour, week, etc that is based on timestamp or interval.

Syntax

date_trunc('datepart', field)

Datepart is used to extract the field value, the following is the datepart:

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

The field is the timestamp or interval value that the date_trunc function truncates. The return value of the field is timestamp or interval.

Also Read: PostgreSQL TO_NUMBER() function

Postgresql date_trunc minutes

In Postgresql, to truncate minutes from timestamp and interval, pass the first argument to date_trunc function as a minute.

Use the below command to truncate the minute part from the timestamp of the United States :

SELECT DATE_TRUNC('minute', TIMESTAMP '2021-03-17 02:09:30');

In the above output, 2021-03-17 02:09:30 truncated to 2021-03-17 02:09:00 and it does not show the seconds of timestamp value.

The date_trunc function returns a result as a minute.

Postgresql date_trunc week

In Postgresql, to truncate or extract the week of the timestamp value, pass the week as a string to the date_trunc function.

Use the below command:

SELECT 
date_trunc('week', timestamp'2021-08-23 19:14:20');

In the above output, it shows the output like a day of the timestamp value but we can find the week number with the help of the calendar.

Read: PostgreSQL TO_TIMESTAMP function

Postgresql date_trunc day

In Postgresql, to extract or truncate day pass the day string to date_trunc function as the first argument.

Use the below command:

SELECT 
date_trunc('day', timestamp'2021-08-23 19:14:20');

Postgresql date_trunc timezone

In Postgresql, to extract timezone, we need to specify the “with time zone” option in the field part of the date_trunc function.

Use the below command:

SELECT date_trunc('hour', timestamp with time zone '2021-08-23 17:29:31+00');

In the above output, the time zone is India Standard TimeTime zone in India (GMT+5:30) and it differs from country to country.

We can also specify the full-time zone name to date_trunc function as the third argument:

SELECT
date_trunc(‘hour’, timestamp with time zone ‘2021-08-23 17:29:31+12’, ‘Asia/Calcutta’);

Read: PostgreSQL TO_CHAR function

Postgresql date_trunc hour

In Postgresql, to truncate hour using date_trunc function, pass the first argument as an hour to date_trunc function.

Use the below command:

SELECT 
date_trunc('hour', timestamp'2021-08-23 19:14:20');

In the above output, 19:14:20 ( circled in red color ) changed to 19:00:00 and it eliminated the minutes, seconds part of the time.

Postgresql date_trunc without time

In Postgresql, to truncate without time or we need only the date part then caste the output of date_trunc function to the only date using :: date.

Use the below command:

SELECT 
date_trunc('day', timestamp'2021-08-23 19:14:20')::date;

In the above output, it truncated from 2021-08-23 19:14:20 to 2021-08-23.

Read: How to backup PostgreSQL database

Postgresql date_trunc month and year

In Postgresql, to truncate month and year pass the “month” and “year” to date_trunc function of Postgresql as the first argument.

use the below command for the month:

SELECT 
date_trunc('month', timestamp'2021-08-23 19:14:20');

In the above output, the timestamp is truncated from 2021-08-23 19:14:20 to 2021-08-01 00:00:00, it also eliminated the time and reduces the date from 23 to 1.

Use the below command for the year:

SELECT 
date_trunc('year', timestamp'2021-08-23 19:14:20');

In the above output, the timestamp is truncated from 2021-08-23 19:14:20 to 2021-01-01 00:00:00, it also eliminated the time and reduces the date, month to 1.

Also Read: How to import CSV file into PostgreSQL

Postgresql date_trunc second

In Postgresql, to truncate second from time using date_trun function, pass the second as the argument to date_func function.

SELECT 
date_trunc('second', timestamp'2021-08-23 19:14:20');

Let’s again understand with another example, here we find the current timestamp of the USA and Canada using the function CURRENT_TIMESTAMP and extract the second part of that timestamp.

SELECT date_trunc('second',CURRENT_TIMESTAMP);

From the above output, we have extracted the second part of the current timestamp of the USA and Canada.

You may also like reading the following articles.

• PostgreSQL drop all tables
• PostgreSQL Export Table to CSV
• Postgres RegEx
• Postgresql REGEXP_REPLACE
• Postgresql escape single quote
• Postgresql cast int

So, in this Postgresql tutorial, we have covered the “Postgresql date_trunc function” which helps in truncating different parts of timestamp value. And we have also covered the following topics.

• Postgresql date_trunc function
• Postgresql date_trunc minutes
• Postgresql date_trunc week
• Postgresql date_trunc day
• Postgresql date_trunc timezone
• Postgresql date_trunc hour
• Postgresql date_trunc without time
• Postgresql date_trunc month and year
• Postgresql date_trunc second