PostgreSQL date_trunc function

In this PostgreSQL tutorial, we will learn about the PostgreSQL date_trunc function, which extracts specific parts of the date and time, such as seconds, minutes, hours, days, months, and years.

Postgresql date_trunc function

In PostgreSQL, the date_trunc function is used to extract and truncate the specific date part (level of precision) of the date and time, such as seconds, minutes, hours, or weeks, based on a timestamp or interval.

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

Perhaps business analysts want to use it for web traffic or trend analysis originating from the United States of America, as these are based on timestamps that indicate when events occur.

In that case, the date_trunc function is beneficial.

Syntax

date_trunc('datepart', field)

Datepart is used to extract the field value. The following is the date part:

• 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 a timestamp or an interval.

Also Read: PostgreSQL TO_NUMBER() function

Let us discuss various PostgreSQL date_trunc function examples.

Example 1: Truncate minutes from a timestamp

In PostgreSQL, to truncate minutes from a timestamp and interval, pass the first argument to the 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');

After executing the above query, I got the expected output as shown in the screenshot below.

In the above output, 2021-03-17 02:09:30 is truncated to 2021-03-17 02:09:00, and the seconds of the timestamp value are not displayed.

The date_trunc function returns a result as a minute.

Example 2: Truncate the week of the timestamp

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');

I got the expected output after executing the above query as shown in the screenshot below.

In the above output, it displays the timestamp value in a day format, but we can determine the week number by referring to the calendar.

Read: PostgreSQL TO_TIMESTAMP function

Example 3: Truncate the day

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

Use the below command:

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

After executing the above query, I got the expected output as shown in the screenshot below.

Example 4: Extract the timezone

In PostgreSQL, to extract the 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');

After executing the above query, I got the expected output as shown in the screenshot below.

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

We can also specify the full-time zone name to the 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

Example 5: Truncate hour

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

Use the below command:

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

After executing the above query, I got the expected output as per the below screenshot.

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.

Example 6: Truncate the date part 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;

After executing the above query, I got the expected output as shown below.

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

Read: How to backup PostgreSQL database

Example 7: Truncate 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');

After executing the above query, I got the expected output as shown in the below screenshot.

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

Example 7: Truncate second from time

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');

After executing the above query, I got the expected output as shown below.

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);

After executing the above query, I got the expected output as shown in the below screenshot.

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

Conclusion

So, in this Postgresql tutorial, we have covered the “Postgresql date_trunc function” with multiple real time examples which helps in truncating different parts of timestamp value.

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