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
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)
select EXTRACT(TIMEZONE_HOUR FROM CAST(EVENT_DTSZ AS TIMESTAMP(6) WITH TIME ZONE ))
EXTRACT(TIMEZONE_HOUR FROM CAST('2013-04-09 10:00:00-05:00' AS TIMESTAMP(6) WITH TIME ZONE
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.
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?
I use Teradata SQL Assistant via either Teradata.Net or ODBC, and Nexus
Teradate.Net gives me:
‘4/10/2013 7:50:00 PM +00:00’
ODBC gives me:
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
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:
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.
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.
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"?
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 :-)
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  'FORMAT string is longer than 30 characters.' At this time, it appears the Oracle function is the only way to solve the requirement.
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)
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.