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 

EXTRACT

13min

This article will assist you in using the EXTRACT() function to extract a given time part from a timestamp or interval.

Overview

The EXTRACT() function extracts a given part from a specified source. The source must be a value expression of timestamp, time, or interval.

Syntax

Syntax
|
EXTRACT(field from source)


Let’s analyze the above syntax:

  • field: used to specify the time parts that are to be extracted.
  • source: used to identify a date/time value. The value type is TIMESTAMP (YYYY-MM-DD HH:MM:SS) or INTERVAL (year - month - day hour - minute - second).

Input and Return Type

The table below shows the supported input types of field and the return type of the EXTRACT() function:

Document image


Examples

#Case 1: EXTRACT() with Timestamp - Year

The below example uses the EXTRACT() function to extract a given timestamp’s YEAR:

Extract
|
SELECT EXTRACT(YEAR FROM TIMESTAMP '2020-12-31 13:30:15');


The final output will be as follows:

Output
|
+----------+
| extract  |
+----------+
| 2020     |
+----------+


#Case 2: EXTRACT() with Timestamp - Month

Here we will use the EXTRACT() function to extract a given timestamp’s MONTH:

Extract
|
SELECT EXTRACT(MONTH FROM TIMESTAMP '2016-10-31 13:30:15');


The final output will take the month’s part of a given timestamp:

Output
|
+----------+
| extract  |
+----------+
| 10       |
+----------+


#Case 3: EXTRACT() with Interval - Day

In this example, we will extract the DAY part of a given interval:

Extract
|
SELECT EXTRACT(DAY FROM INTERVAL '6 years 8 months 20 days 4 hours 47 minutes 22 second' );


The final output will be as follows:

Output
|
+----------+
| extract  |
+----------+
| 20       |
+----------+


#Case 4: EXTRACT() with Interval - Second

The below example uses the EXTRACT() function to extract seconds from the given interval.

Extract
|
SELECT EXTRACT(SECOND FROM INTERVAL '6 years 2 months 13 days 8 hours 30 minutes 43 second' );


The final output will be as follows:

Output
|
+------------+
| extract    |
+------------+
| 43.000000  |
+------------+




Updated 11 May 2023
Did this page help you?
Yes
No
PREVIOUS
TIMESTAMP_TRUNC
NEXT
Boolean Function
Docs powered by archbee 
TABLE OF CONTENTS
Overview
Syntax
Input and Return Type
Examples
#Case 1: EXTRACT() with Timestamp - Year
#Case 2: EXTRACT() with Timestamp - Month
#Case 3: EXTRACT() with Interval - Day
#Case 4: EXTRACT() with Interval - Second

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