Timestamp with Timezone

Database
Enthusiast

Timestamp with Timezone

Hi ,

 

I have a date column SEGMT_END_DTTM which is defined as Timestamp with Timezone.

 

I need to derive the below 2 columns

INTV_STRT_DTTM TIMESTAMP(0) WITH TIME ZONE,

INTV_STRT_TM TIME(0) WITH TIME ZONE,

 

INTV_STRT_DTTM - timestamp with timezone of  SEGMT_END_DTTM with time rounded to the nearest half hour.

INTV_STRT_TM - only time of SEGMT_END_DTTM with time rounded to the nearest half hour.

 

I am able to round it to the nearest half hour, but loosing the time zone. Below is a sample data.

 

 

SEGMT_END_DTTMINTV_STRT_DTTMINTV_STRT_TM
2017-06-10 21:23:44-04:00 2017-06-10 21:00:00+00:00 21:00:00+00:00 
2017-06-10 16:37:05-05:00 2017-06-10 16:30:00+00:00 16:30:00+00:00 
2017-06-10 14:00:41-04:00 2017-06-10 14:00:00+00:00 14:00:00+00:00 

 


Accepted Solutions
Junior Contributor

Re: Timestamp with Timezone

Based on EXTRACT:

INTV_STRT_DTTM
  - (Extract(MINUTE From INTV_STRT_DTTM) MOD 30 * INTERVAL '1' MINUTE) 
  - (Extract(SECOND From INTV_STRT_DTTM)        * INTERVAL '1' SECOND)

 

1 ACCEPTED SOLUTION
9 REPLIES
Enthusiast

Re: Timestamp with Timezone

How to retain the timezone as well in the above case

Junior Contributor

Re: Timestamp with Timezone

Based on EXTRACT:

INTV_STRT_DTTM
  - (Extract(MINUTE From INTV_STRT_DTTM) MOD 30 * INTERVAL '1' MINUTE) 
  - (Extract(SECOND From INTV_STRT_DTTM)        * INTERVAL '1' SECOND)

 

Enthusiast

Re: Timestamp with Timezone

Hi Dnoeth,

 

The below query helped for column INTV_STRT_DTTM (TIMESTAMP WITH TIME ZONE).

 

The time zone is missing for INTV_STRT_TM (TIME WITH TIME ZONE ) column

 

ex: INTV_STRT_DTTM = 2017-09-08 19:30:00-05:00

CAST(INTV_STRT_DTTM AS TIME(0) WITH TIME ZONE) --> OUTPUT 19:30:00+00:00 

 

Please suggest how I can reatin the time zone in the above case

 

Highlighted
Junior Contributor

Re: Timestamp with Timezone

Works fine for me:

SEGMT_END_DTTM
  - (Extract(MINUTE From SEGMT_END_DTTM) MOD 30 * INTERVAL '1' MINUTE) 
  - (Extract(SECOND From SEGMT_END_DTTM)        * INTERVAL '1' SECOND) AS INTV_STRT_DTTM,
CAST(INTV_STRT_DTTM AS TIME(0) WITH TIME ZONE)

Do you connect using .NET/ODBC/JDBC?

 

Enthusiast

Re: Timestamp with Timezone

ODBC through Teradata SQL Assistant

 

ex: INTV_STRT_DTTM = 2017-09-08 19:30:00-05:00

 

CAST(INTV_STRT_DTTM AS TIME(0) WITH TIME ZONE) --> 

 

The Output of the obove statement is  19:30:00+00:00  , but the timezone is lost.

 

Expexted result ---> 9:30:00-5:00.

 

Trying to extract Time with Time Zone from a Timestamp with Time Zone Column

Could you please suggest

Junior Contributor

Re: Timestamp with Timezone

SQL Assistant using ODBC doesn't show any time zone for me.

 

You better switch to .NET as ODBC has known limitations:

Time Zone information will not be displayed.
Time fields do not display fractional seconds or Time Zone information.

 

 

 

 

ODBC Specific Limitations

Enthusiast

Re: Timestamp with Timezone

Hi Dnoeth,

 

I have used CAST to see the time zone in SQL Assistant

SELECT
CAST(SEGMT_END_DTTM AS VARCHAR(26))  ,
CAST(SEGMT_END_DTTM
  - (Extract(MINUTE From SEGMT_END_DTTM) MOD 30 * INTERVAL '1' MINUTE)
  - (Extract(SECOND From SEGMT_END_DTTM)        * INTERVAL '1' SECOND)  AS VARCHAR(26)) INTV_STRT_DTTM,
CAST(CAST(INTV_STRT_DTTM AS TIME(0) WITH TIME ZONE) AS VARCHAR(19))
FROM XDW_DEV_COREV.CALL_SEGMT_INB
SAMPLE 3

 

SEGMT_END_DTTM              INTV_STRT_DTTM                   INTV_STRT_DTTM
2017-06-10 15:42:50-04:00    2017-06-10 15:30:00-04:00       15:30:00+00:00
2017-06-10 12:33:24-04:00    2017-06-10 12:30:00-04:00       12:30:00+00:00
2017-06-10 14:30:13-04:00    2017-06-10 14:30:00-04:00       14:30:00+00:00

 

I have tried CAST ,SUBSTR, EXTRACT(hour ,minute,second,timezone_hour,timezone_minute) , but none of them retain the timezone :(

 

Junior Contributor

Re: Timestamp with Timezone

What's the Teradata/ODBC/SQL Assistant versions?

 

Does .NET return the same result? Then it might be due to some Teradata settings (there are multiple options regarding time(stamps) and time zones. 

 

You can try to cast to a string first and then substring like:

Substr(Cast(INTV_STRT_DTTM AS VARCHAR(25)), 12) -- if INTV_STRT_DTTM is a timestamp

Substr(INTV_STRT_DTTM, 12) -- if INTV_STRT_DTTM is already a string
Enthusiast

Re: Timestamp with Timezone

Teradata 15.10 is the version of SQL Assistant.

 

I changed to .NET connection in  SQL Assistant and it didnt reatin the TIME ZONE

 

The below work around returned the results as expected

SELECT
SEGMT_END_DTTM,

CAST(SEGMT_END_DTTM
  - (Extract(MINUTE From SEGMT_END_DTTM) MOD 30 * INTERVAL '1' MINUTE)
  - (Extract(SECOND From SEGMT_END_DTTM)        * INTERVAL '1' SECOND)  AS VARCHAR(26))  INTV_STRT_DTTM,

substr( cast  ( SEGMT_END_DTTM
  - (Extract(MINUTE From SEGMT_END_DTTM) MOD 30 * INTERVAL '1' MINUTE)
  - (Extract(SECOND From SEGMT_END_DTTM)        * INTERVAL '1' SECOND) as varchar(26))  ,11)  INTV_STRT_TM

 

 

Thanks Dnoeth , much appreciated