Use of Extract () in Teradata Timestamp and Timestamp with Zone

Use of Extract () in Teradata Timestamp and Timestamp with Zone

EXTRACT from TIMESTAMP:

We can extract following pieces from TIMESTAMP.

1. HOUR

2. MINUTE

3. SECOND

4. DAY

5. YEAR

6. MONTH

We can also extract these pieces from the calculation that results in timestamp.

Example 1:

SELECT

EXTRACT (HOUR FROM CURRENT_TIMESTAMP),

EXTRACT (MINUTE FROM CURRENT_TIMESTAMP),

EXTRACT (SECOND FROM CURRENT_TIMESTAMP),

EXTRACT (DAY FROM CURRENT_TIMESTAMP),

EXTRACT (MONTH FROM CURRENT_TIMESTAMP),

EXTRACT (YEAR FROM CURRENT_TIMESTAMP);

Hour	Minute	Second	Day	Month	Year
8	59	19.18	13	4	2013

Example 2:

SELECT EXTRACT( HOUR FROM (TIMESTAMP '2013-12-12 11:13:14' + INTERVAL '3 02:12' DAY TO MINUTE))

Result : 13

Example 3:

SELECT EXTRACT( DAY FROM (TIMESTAMP '2013-12-12 11:13:14' + INTERVAL '3 02:12' DAY TO MINUTE))

Result : 15

Note we don’t extract TIME or DATE from timestamp.

For getting the DATE from TIMEstamp we make use of CAST function as follows:

SELECT CAST(CURRENT_TIMESTAMP AS DATE) ;

For getting the TIME from TIMESTAMP we can use the below query:

SELECT

CAST (CASE

WHEN EXTRACT(HOUR FROM CURRENT_TIMESTAMP) > 9

THEN  TRIM(EXTRACT(HOUR FROM CURRENT_TIMESTAMP) )

ELSE

'0'||TRIM(EXTRACT(HOUR FROM CURRENT_TIMESTAMP) )

END

||':'||

CASE

WHEN EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) > 9

THEN  TRIM(EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) )

ELSE

'0'||TRIM(EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) )

END

||':'||

TRIM(EXTRACT(SECOND FROM CURRENT_TIMESTAMP)) AS TIME )

Extracting from TIMESTAMP WITH ZONE /TIME WITH ZONE:

We can also use EXTRACT TIME ZONE HOUR and TIME ZONE MINUTE from TIME WITH TIMEZONE and TIMESTAMP WITH TIME ZONE.

SELECT

EXTRACt(TIMEZONE_HOUR FROM tstamp ) ,

EXTRACt(TIMEZONE_MINUTE FROM tstamp )

from test;

Timezone_Hour	Timezone_Minute
6	0

We can extract TIMEZONE_HOUR and TIMEZONE_MINUTE from the normalized timestamp also.

SELECT

EXTRACt(TIMEZONE_HOUR FROM tstamp AT LOCAL ) ,

EXTRACt(TIMEZONE_MINUTE FROM tstamp AT LOCAL)

from test;

Timezone_Hour	Timezone_Minute
-11	0