Teradata Timestamp - Part 7 - Extracting from 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

Teradata Date Time Part 12 - Casting Date Time interval and EXTRACT ing HOUR,MINUTE and SECOND

Conversion between different types of day-time interval types is easily accomplished using the CAST feature.

Conversions which include a remainder which is not supported by the conversion interval type will be dropped, not rounded. 

You can CAST any of the values in the left column AS any of the values in the right column.

CAST

DAY DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR HOUR TO MINUTE HOUR TO SECOND MINUTE MINUTE TO SECOND SECOND

AS

DAY DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR HOUR TO MINUTE HOUR TO SECOND MINUTE MINUTE TO SECOND SECOND

Select CAST(INTERVAL '50' HOUR AS INTERVAL DAY)

Result is 2

Select CAST(INTERVAL '50' HOUR AS INTERVAL DAY);

Result is 1 day --> No rounding

Select CAST(INTERVAL '50' HOUR AS INTERVAL DAY TO MINUTE);

2 02:00

• Extracting from TIME.

We can extract number of Hours, Minutes and seconds from the TIME data type using the EXTRACT function.

We can extract from the TIME data type of from the calculation that results in time data type.

Examples:

SELECT EXTRACT ( HOUR FROM TIME '12:12:12'); --> 12

SELECT EXTRACT ( HOUR FROM (TIME '12:12:12' + INTERVAL '4' HOUR)); --> 16

SELECT EXTRACT (MINUTE FROM CURRENT_TIME);

SELECT EXTRACT (SECOND FROM CURRENT_TIME);