To Truncate the timestamp to nearest hour for any given datetime in PostgreSQL, you can use the DATE_TRUNC() function. DATE_TRUNC() function is used to truncate a date or timestamp to a specified level of precision. It allows you to round down a date or timestamp to a specific unit, such as year, quarter, month, week, day, hour, etc . In our case Truncate the timestamp to nearest hour. Also we will populate the timestamp with nearest hour, based on the date column in postgresql Table.
Syntax for DATE_TRUNC() Function in PostgreSQL:
unit: Specifies the unit to which you want to truncate the date or timestamp (e.g., ‘year’, ‘quarter’, ‘month’, ‘week’, ‘day’, ‘hour’, etc.).
source: The date or timestamp that you want to truncate.
In Our case. The values of the unit must be “hour”.
Truncate the timestamp to nearest hour in postgresql – DATE_TRUNC() function :
In order to truncate the timestamp to nearest hour in our example, we will be using DATE_TRUNC() function.
In the below Example we will be passing hour as “unit” argument and date as “source” argument in order to truncate the timestamp to nearest hour
SELECT DATE_TRUNC('hour', '2024-02-24 15:30:45'::timestamp) AS Nearest_hour_timestamp;
Output:
Populate the timestamp with nearest hour in PostgreSQL table using DATE_TRUNC()
We will be using below Student_detail table for our example to depict on how to populate First day of Week (Monday) from date in postgresql
Student_detail:
In the above table we will be using DATE_TRUNC() Function, which will take “hour” and column named “birthdaytime” as argument. Which will Truncate the datetime to nearest hour based on the “birthdaytime” column and store in the new column of postgresql table as shown below
SELECT *, DATE_TRUNC('hour', birthdaytime::timestamp) AS Nearest_hour_timestamp from Student_detail;
So the resultant table will have Nearest_hour_timestamp column
