Skip to main content

​
Overview

The TIMESTAMP_TRUNC() function rounds a timestamp to a specific day_time granularity, resulting in a truncated timestamp.

​
Syntax

SELECT TIMESTAMP_TRUNC(TIMESTAMP 'YYYY-MM-DD hour:min:sec', day_time);
day_time can be replaced with various time values as follows:
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • MONTH
  • YEAR

​
Examples

​
Case #1: TIMESTAMP_TRUNC() - Hour

The following example shows how to round the hour to the closest value: 
SELECT TIMESTAMP_TRUNC(TIMESTAMP '2017-09-18 14:43:39.02322', HOUR) ;
The final result will display the current date and time in your timezone: 
+-----------------------------+
| f                           |
+-----------------------------+
| 2017-09-18 14:00:00.00000   |
+-----------------------------+

​
Case #2: TIMESTAMP_TRUNC() - Minute

Here we will truncate the specified timestamp into the nearest value: 
SELECT TIMESTAMP_TRUNC(TIMESTAMP '2005-03-18 14:13:13', MINUTE) ;
The result will return the truncated timestamp as shown below: 
+-----------------------------+
| f                           |
+-----------------------------+
| 2005-03-18 14:13:00.00000   |
+-----------------------------+

​
Case #3: Basic TIMESTAMP_TRUNC() function - Year

Run the following query to round the date to the closest value: 
SELECT TIMESTAMP_TRUNC(TIMESTAMP '2023-03-04', YEAR);
The function will truncate the year and return the following result: 
+-----------------------------+
| f                           |
+-----------------------------+
| 2023-01-01 00:00:00.00000   |
+-----------------------------+