TIMESTAMP_TRUNC

7min

Overview
The TIMESTAMP_TRUNC() function rounds a timestamp to a specific day_time granularity, resulting in a truncated timestamp.
Syntax
Syntax
SELECT TIMESTAMP_TRUNC(TIMESTAMP 'YYYY-MM-DD hour:min:sec', day_time);
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:
Timestamp trunc
SELECT TIMESTAMP_TRUNC(TIMESTAMP '2017-09-18 14:43:39.02322', HOUR) ;
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:
Output
+-----------------------------+
| f                           |
+-----------------------------+
| 2017-09-18 14:00:00.00000   |
+-----------------------------+
+-----------------------------+
| f                           |
+-----------------------------+
| 2017-09-18 14:00:00.00000   |
+-----------------------------+
﻿
#Case 2: TIMESTAMP_TRUNC() - Minute
Here we will truncate the specified timestamp into the nearest value:
Timestamp trunc
SELECT TIMESTAMP_TRUNC(TIMESTAMP '2005-03-18 14:13:13', MINUTE) ;
SELECT TIMESTAMP_TRUNC(TIMESTAMP '2005-03-18 14:13:13', MINUTE) ;
﻿
The result will return the truncated timestamp as shown below:
Output
+-----------------------------+
| f                           |
+-----------------------------+
| 2005-03-18 14:13:00.00000   |
+-----------------------------+
+-----------------------------+
| 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:
Timestamp trunc
SELECT TIMESTAMP_TRUNC(TIMESTAMP '2023-03-04', YEAR);
SELECT TIMESTAMP_TRUNC(TIMESTAMP '2023-03-04', YEAR);
﻿
The function will truncate the year and return the following result:
Output
+-----------------------------+
| f                           |
+-----------------------------+
| 2023-01-01 00:00:00.00000   |
+-----------------------------+
+-----------------------------+
| f                           |
+-----------------------------+
| 2023-01-01 00:00:00.00000   |
+-----------------------------+
﻿
﻿

Updated 09 Oct 2023

Did this page help you?

TIMESTAMP_MICROS

EXTRACT

Docs powered by Archbee

TABLE OF CONTENTS

Overview

Syntax

Examples

#Case 1: TIMESTAMP_TRUNC() - Hour

#Case 2: TIMESTAMP_TRUNC() - Minute

#Case 3: Basic TIMESTAMP_TRUNC() function - Year