website logo
⌘K
🏠Homepage
👋Introduction
Key Concepts & Architecture
🚀Getting Started
👨‍💻SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
🚨Error Handling
🆚Differences Between Oxla vs. PostgreSQL
Docs powered by archbee 

TIMESTAMP_TRUNC

10min

This article will assist you in how to use the TIMESTAMP_TRUNC() function.

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


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


The final result will display the current date and time in your timezone:

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


The result will return the truncated timestamp as shown below:

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


The function will truncate the year and return the following result:

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




Updated 12 May 2023
Did this page help you?
Yes
No
PREVIOUS
TIMESTAMP_MICROS
NEXT
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

Dedicated to data analytics that provides 10x faster execution of analytical queries than current state of the art solutions. We are launching SOON! Join the waiting list for more info.




©2022 Oxla