Extracing ZONE part from a TIMESTAMP(0) WITH TIME ZONE column

Analytics

Extracing ZONE part from a TIMESTAMP(0) WITH TIME ZONE column

In table definition the below column is mentioned like:

Lcl_Dttmz TIMESTAMP(0) WITH TIME ZONE FORMAT 'yyyy-mm-ddbhh:mi:ss' TITLE 'Sale Location Local Dttmz ' COMPRESS

Value retriving looks for the same is like '2010-08-26 14:42:45+10:00' .Want to cast the value as VARCHAR(25) to extract the zone part.

But cast(Lctn_Lcl_Dttmz as varchar(25)) is retriving value like '2010-08-26 14:42:45' .

Can anyone plz help me out how to extract the zone part of the particular value..

3 REPLIES
Supporter

Re: Extracing ZONE part from a TIMESTAMP(0) WITH TIME ZONE column

Check the extract function. From the manual:


Extracts a single specified full ANSI SQL:2008 field from any DateTime or Interval value,


converting it to an exact numeric value.


Syntax element … Specifies …


YEAR that the integer value for YEAR is to be extracted from the date


represented by value.


MONTH that the integer value for MONTH is to be extracted from the date


represented by value.


DAY that the integer value for DAY is to be extracted from the date represented


by value.


HOUR that the integer value for HOUR is to be extracted from the date


represented by value.


MINUTE that the integer value for MINUTE is to be extracted from the date


represented by value.


TIMEZONE_HOUR that the integer value for TIMEZONE_HOUR is to be extracted from the


date represented by value.


TIMEZONE_MINUTE that the integer value for TIMEZONE_MINUTE is to be extracted from


the date represented by value.


SECOND that the integer value for SECOND is to be extracted from the date


represented by value.


value an expression that results in a DateTime, Interval, or UDT value.


FF07D144


EXTRACT


MONTH


( YEAR FROM value)


DAY


HOUR


MINUTE


SECOND


TIMEZONE_HOUR


TIMEZONE_MINUTE

Enthusiast

Re: Extracing ZONE part from a TIMESTAMP(0) WITH TIME ZONE column

I tried doing it like this:

SELECT EXTRACT(TIMEZONE_HOUR FROM CAST('2010-08-26 14:42:45+10:00' AS TIMESTAMP(0) WITH TIME ZONE));

Result:

10

HTH

Cheers,

Eco

Supporter

Re: Extracing ZONE part from a TIMESTAMP(0) WITH TIME ZONE column


SELECT substr(cast(CAST('2010-08-26 14:42:45-08:00' AS TIMESTAMP(0) WITH TIME \

ZONE FORMAT 'YYYY-MM-DDBHH:MI:SS.S(F)Z') as char(25)),20,6);


would do the substr