1

I am trying to extract the local timestamp from a timestamp with time zone data type column.

I currently have the below table schema:

ARRIVAL_DTM TIMESTAMP(0) WITH TIME ZONE

I am able to convert the timestamp to a date format without teradata forcing the time zone on it as shown below:

SEL 
CAST(ARRIVAL_DTM AS DATE AT SOURCE TIME ZONE) AS Date, 
ARRIVAL_DTM AS 'Timestamp with Time Zone' 
From tblname;

Result:

Date         Timestamp with Time Zone
2021-06-01   2021-06-01 23:13:51-06:00
2021-06-01   2021-06-01 23:27:58-06:00
2021-06-02   2021-06-02 01:28:50-06:00
2021-06-02   2021-06-02 02:41:29-06:00

From the above first record, if the time zone was applied, the date should have been 2021-06-02

What I am trying to do is to extract the timestamp without td automatically adding the timezone to the result.

I wish a result like below, Column Timestamp without Time Zone:

Timestamp without Time Zone               Timestamp with Time Zone
2021-06-01 23:13:51                       2021-06-01 23:13:51-06:00
2021-06-01 23:27:58                       2021-06-01 23:27:58-06:00
2021-06-02 01:28:50                       2021-06-02 01:28:50-06:00
2021-06-02 02:41:29                       2021-06-02 02:41:29-06:00

If I do cast as timestamp(0), the end result is the timestamp including the time zone in it which I do not want. td is automatically giving me the timestamp + time zone in it.

SEL 
CAST(ARRIVAL_DTM AS TIMESTAMP(0)) AS Date,
ARRIVAL_DTM AS 'Timestamp with Time Zone' 
From tblname;

Result:

Date                  Timestamp with Time Zone
2019-04-04 19:28:38   2019-04-04 13:28:38-06:00
2019-05-02 19:30:41   2019-05-02 13:30:41-06:00
2019-05-30 16:36:13   2019-05-30 10:36:13-06:00

Edit:

I am able to extract the time zone value from the timestamp column only if I convert it to VARCHAR and then remove the right 6 characters using INSTR, etc.. which requires a lot of string manipulation

Isn't there another way of doing this with built in functions/options in TD?

SELECT
    LEFT( 
        CAST( ARRIVAL_DTM AS VARCHAR(40) ),
        CAST( 
            INSTR( CAST( ARRIVAL_DTM AS VARCHAR(40) ),
                    RIGHT( CAST ( ARRIVAL_DTM AS VARCHAR(40) ), 6 )
                 )
        AS INTEGER ) - 1 AS 'Timestamp without Time Zone (VARCHAR)'
    ), 
ARRIVAL_DTM AS 'Timestamp with Time Zone (TIMESTAMP)' 
From tblname;

Result:

Timestamp without Time Zone (VARCHAR)  Timestamp with Time Zone (TIMESTAMP)
2019-06-29 23:22:51                    2019-06-29 23:22:51-06:00
2019-07-16 05:59:46                    2019-07-16 05:59:46-06:00
2019-07-20 18:44:18                    2019-07-20 18:44:18-06:00
Jonathan Lam
  • 1,237
  • 3
  • 20
  • 49
  • The TIMESTAMP data type always has an associated time zone, whether explicitly stored or implied from the session time zone. When you CAST as timestamp (without time zone) you are just shifting the "same" point in time value to be relative to the session time zone. What do you want to do with this "extracted" value? If you just want to display it, then use `TO_CHAR` or `CAST` with a format that omits the time zone. – Fred Jul 10 '21 at 16:59
  • How can I cast with a format that omits the time zone? Cast to varchar? I would like to know if there is any other way I did it above. I will use it for grouping and display the number of records under the timestamp. I need the timestamp value without the timezone as it represents the local date time an item has been processed and the requirement is on the local date time. – Jonathan Lam Jul 10 '21 at 22:54
  • Either `CAST(CAST(ARRIVAL_DTM AS FORMAT 'YYYY-MM-DD HH:MI:SS') AS VARCHAR(19))` or `TO_CHAR(ARRIVAL_DTM,'YYYY-MM-DD HH:MI:SS')` should do for converting to a display value that simply omits the time zone offset. – Fred Jul 12 '21 at 16:38
  • The Cast as Varchar is not working for me as it is removing the space in between the date and time. For ex: 2019-06-1900:31:11. The ToChar is working and is displaying correctly with the space. Can you put your ToChar answer to the question. – Jonathan Lam Jul 12 '21 at 18:25
  • Oh, you are right. FORMAT for CAST uses B to indicate space as delimiter. Corrected in answer below. – Fred Jul 12 '21 at 22:40

1 Answers1

2

Either CAST(CAST(ARRIVAL_DTM AS FORMAT 'YYYY-MM-DDBHH:MI:SS') AS VARCHAR(19)) or TO_CHAR(ARRIVAL_DTM,'YYYY-MM-DD HH:MI:SS') should do for converting to a display value that simply omits the time zone offset.

Fred
  • 1,916
  • 1
  • 8
  • 16