TIMESTAMP(6) WITH TIME ZONE, space between seconds and timezone, need to remove it

Database

TIMESTAMP(6) WITH TIME ZONE, space between seconds and timezone, need to remove it

I’m trying to extract the Time zone offset from a TIMESTAMP(6) WITH TIME ZONE column. I’ll call the column ‘EVENT_DTSZ’

The values in the column ‘EVENT_DTSZ’ follows like ‘03/03/2013 8:00:00 -06:00’

I’m trying take this value in EVENT_DTSZ and then populate it into two columns, one with just the Timestamp based on 24 hour clock and the other with just the offset

i.e.

select EVENT_DTSZ

returns ‘03/03/2013 8:00:00 -06:00’

select cast(EVENT_DTSZ as varchar(19))

returns ‘2013-03-03 20:00:00’

EXTRACT(TIMEZONE_HOUR FROM EVENT_DTSZ)

returns ‘0’

select EXTRACT(TIMEZONE_HOUR FROM CAST(EVENT_DTSZ AS TIMESTAMP(6) WITH TIME ZONE ))

returns ‘0’

EXTRACT(TIMEZONE_HOUR FROM CAST('2013-04-09 10:00:00-05:00' AS TIMESTAMP(6) WITH TIME ZONE

returns ‘-5’

I think it has something to do where there being a space between the seconds and the -6:00

Any idea how I can trim that blank space from the EVENT_DTSZ’ column with it being a TIMESTAMP(6) WITH TIME ZONE datatype? Or how I can get the timezone out? Going VARCHAR <19 just gives blank zeros. i.e. +00:00 past the seconds.

Thanks,

8 REPLIES
Junior Contributor

Re: TIMESTAMP(6) WITH TIME ZONE, space between seconds and timezone, need to remove it

The returned value has an unusual format, if it was formatted by TD there would be a leading zero before the hour.

What's your client tool/connectivity?

Did you check the FORMAT of EVENT_DTSZ? A blank shouldn't cause any problems, it's just for display, the internal storage is always the same.

What's the expected result? EVENT_DTSZ normalized to UTC, your local timezone?

What's your local time zone, TD release?

Do you want to split it just for display (SELECT) or use it in an INSERT/SELECT?

Dieter

Re: TIMESTAMP(6) WITH TIME ZONE, space between seconds and timezone, need to remove it

I use Teradata SQL Assistant via either Teradata.Net or ODBC, and Nexus

sel EVENT_DTSZ

FROM DB.EVENT_LOGS

sample 1

Teradate.Net gives me:

‘4/10/2013 7:50:00 PM +00:00’

ODBC gives me:

‘4/10/2013 19:50:00.000000’

In Nexus Query

Source Type Teradata, ODBC, it gives me:

“04/10/2013 7:50:00 -05:00”

In Nexus, I have to check show AM/PM, otherwise 7 am and 7 pm look the same, it then gives:

“04/10/2013 7:50:00 -05:00 PM”

I can cast it in Nexus to Varchat(19) to get it in 24 hour format.

What I want in a result is one column with “4/10/2013 19:50:00” and another with “-5:00” or “-5.00”, preferably through Nexus

Thanks,

Junior Contributor

Re: TIMESTAMP(6) WITH TIME ZONE, space between seconds and timezone, need to remove it

Dealing with "timestamp with time zone" is complicated and the rules how it's casted depend on some dbscontrol settings, too :-)

Plus there's a known limitation in ODBC, which doesn't support time zones, you always have to cast to string. 

You could play around with adding the difference of the time zones of EVENT_DTSZ and CURRENT_TIMESTAMP, but this gets ugly cause there might be hours and minutes.

I'd suggest casting to strings:

event_dtsz (FORMAT 'YYYY-MM-DDBHH:MI:SS.S(2)') (VARCHAR(22)) -- optional (TIMESTAMP(2))

event_dtsz (FORMAT 'Z') (VARCHAR(6)) -- optional (INTERVAL HOUR(2) TO MINUTE)

If this is only for display you don't need the optional cast, but you should use ANSI format YYYY-MM-DD instead of MM/DD/YYYY for correct sorting.

When you need this calculation in several places you might plug it into a SQL UDF In TD13.10.

Dieter

Enthusiast

Re: TIMESTAMP(6) WITH TIME ZONE, space between seconds and timezone, need to remove it

Dieter, thanks for all of your wonderful posts on this site.  You're truly a rock star.

I agree wholeheartedly that this is complicated, but I have the same question as the OP.  For whatever reason, Oracle includes this blank space in a TSWTZ value. I'm a Teradata guy and the Data Types and Literals Manual says 'B' supports a blank space character in a DateTime value.

To start from a working position, I robbed this straight from the Teradata manual cited above.  My goal here is just to figure out what the format string is.

SELECT

CAST('1985-09-12 13:20:53.64+03:00' AS TIMESTAMP(2) WITH TIME ZONE) (FORMAT 'YYYY-MM-DDBHH:MI:SSDS(F)Z');

So, if I induce a blank, in the place Oracle puts it - right after the subsecond and before the TZ:

SELECT

CAST('1985-09-12 13:20:53.64 +03:00' AS TIMESTAMP(2) WITH TIME ZONE) (FORMAT 'YYYY-MM-DDBHH:MI:SSDS(F)BZ')

No matter where I put the B to recognize the blank space, I get either an invalid format or illegal timestamp.

As I toyed with this further, the only solution I've found is the unthinkable.  Brute force substrings.  Hack the Oracle given blank space.  Does Oracle do this just to mess with us?  I don't think so, we ought to be able to man up and do this. 

My example data has '2007-01-31 09:26:56.660000 +02:00' from Oracle stored in Teradata column "c1"

The normal format string (FORMAT 'YYYY-MM-DDBZBHH:MI:SS.S(6)') to handle Timestamp(6) apparently does not extend to Timestamp(6) with TimeZone.   

To get to "done", so I could go home, I had to extend the substring to also remove 4 digits of subsecond precision.  The product of the two substrings ruins the data, but it does put it into the known good format string from the manual.

Yuk.  I can't shrug off this in a live solution, but I am hell bent on going home.

SELECT

CAST(SUBSTRING(c1 FROM 1 FOR (INSTR(c1,'+')-6)) || SUBSTRING(c1 FROM INSTR(c1,'+') AS TIMESTAMP(2) WITH TIME ZONE) (FORMAT 'YYYY-MM-DDBHH:MI:SSBDS(F)Z')

I'd gladly put the substrings into a SQL UDF (great idea!), but now my problem has moved from help me with the Oracle blank to "why doesn't a timestamp(6) work in TS w TZ format string"?

Junior Contributor

Re: TIMESTAMP(6) WITH TIME ZONE, space between seconds and timezone, need to remove it

If you're on TD14 you might simply use one of the new built-in Oracle functions

TO_TIMESTAMP_TZ( '2007-01-31 09:26:56.660000 +02:00')

Btw, your SUBSTRING ... will fail if you got a negative time zone like -02:00 :-)

Enthusiast

Re: TIMESTAMP(6) WITH TIME ZONE, space between seconds and timezone, need to remove it

The loss of precision made the substring route a non-starter.

The documentation for TO_TIMESTAMP_TZ shows the format string that would have done the trick:

(FORMAT 'YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM')

However, this returns a  [3736] 'FORMAT string is longer than 30 characters.'  At this time, it appears the Oracle function is the only way to solve the requirement.

Junior Contributor

Re: TIMESTAMP(6) WITH TIME ZONE, space between seconds and timezone, need to remove it

No, you can simply use the typecast, but you must do it the correct way ;-)

When you write

CAST('1985-09-12 13:20:53.64 +03:00' AS TIMESTAMP(2) WITH TIME ZONE) (FORMAT 'YYYY-MM-DDBHH:MI:SSDS(F)BZ')

the CAST is done first and then a new FORMAT is applied to the resulting timestamp. Of course this fails due to the blank.

Remove ') (' and it works:

CAST('1985-09-12 13:20:53.64 +03:00' AS TIMESTAMP(2) WITH TIME ZONE FORMAT 'YYYY-MM-DDBHH:MI:SSDS(F)BZ')

This is better than using TO_TIMESTAMP_TZ which always returns a TIMESTAMP(6)

Enthusiast

Re: TIMESTAMP(6) WITH TIME ZONE, space between seconds and timezone, need to remove it

Thanks Dieter!  

The format string that supports the Timestamp(6) with a Blank (the equivalent to TO_TIMESTAMP_TZ)  is this:

CAST('1985-09-12 13:20:53.641234 +03:00' AS TIMESTAMP(6) WITH TIME ZONE FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)BZ')



The user only needs to change the Timestamp precision to any value between 2 and 6 by modifying the S(x) portion to match the data value.