EXTRACT function in Amazon QLDB - Amazon Quantum Ledger Database (Amazon QLDB)
SyntaxArgumentsReturn typeExamplesRelated functions

EXTRACT function in Amazon QLDB

Important

End of support notice: Existing customers will be able to use Amazon QLDB until end of support on 07/31/2025. For more details, see Migrate an Amazon QLDB Ledger to Amazon Aurora PostgreSQL.

In Amazon QLDB, use the EXTRACT function to return the integer value of a specified date or time part from a given timestamp.

Syntax

EXTRACT ( datetimepart FROM timestamp )

Arguments

datetimepart

The date or time part that the function extracts. This parameter can be one of the following:

  • year

  • month

  • day

  • hour

  • minute

  • second

  • timezone_hour

  • timezone_minute

timestamp

The field name or expression of data type timestamp that the function extracts from. If this parameter is an unknown type (NULL or MISSING), the function returns NULL.

An Ion timestamp literal value can be denoted with backticks (`...`). For formatting details and examples of timestamp values, see Timestamps in the Amazon Ion specification document.

Return type

int

Examples

EXTRACT(YEAR FROM `2010-01-01T`)                           -- 2010
EXTRACT(MONTH FROM `2010T`)                                -- 1 (equivalent to 2010-01-01T00:00:00.000Z)
EXTRACT(MONTH FROM `2010-10T`)                             -- 10
EXTRACT(HOUR FROM `2017-01-02T03:04:05+07:08`)             -- 3
EXTRACT(MINUTE FROM `2017-01-02T03:04:05+07:08`)           -- 4
EXTRACT(TIMEZONE_HOUR FROM `2017-01-02T03:04:05+07:08`)    -- 7
EXTRACT(TIMEZONE_MINUTE FROM `2017-01-02T03:04:05+07:08`)  -- 8

-- Runnable statements
SELECT EXTRACT(YEAR FROM `2010-01-01T`) FROM << 0 >> -- 2010
SELECT EXTRACT(MONTH FROM `2010T`) FROM << 0 >>      -- 1

Related functions