CODEWITHSUNDEEP
sqlteradatateradatasql
Jonathan Lam
Jonathan Lam

Reputation: 1330

How to extract timestamp without the time zone in sql and without converting it to string for manipulation?

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

Upvotes: 1

Views: 3517

Answers (1)

Fred
Fred

Reputation: 2080

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.

Upvotes: 2

Related Questions