Cloud

timestamp_trunc

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 accepts these time values:

  • SECOND

  • MINUTE

  • HOUR

  • DAY

  • MONTH

  • YEAR

Examples

timestamp_trunc() - hour

This 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 the timezone in which the query was issued:

+-----------------------------+
| f                           |
+-----------------------------+
| 2017-09-18 14:00:00.00000   |
+-----------------------------+

timestamp_trunc() - minute

This example truncates the specified timestamp into the nearest value:

SELECT TIMESTAMP_TRUNC(TIMESTAMP '2005-03-18 14:13:13', MINUTE) ;

The result is the truncated timestamp:

+-----------------------------+
| f                           |
+-----------------------------+
| 2005-03-18 14:13:00.00000   |
+-----------------------------+

Basic timestamp_trunc() function - year

Run this query to round the date to the closest value:

SELECT TIMESTAMP_TRUNC(TIMESTAMP '2023-03-04', YEAR);

The function will truncate the year and returns:

+-----------------------------+
| f                           |
+-----------------------------+
| 2023-01-01 00:00:00.00000   |
+-----------------------------+