The other day, I was creating a procedure that compares the column NTIMESTAMP# from SYS.AUD$ with the column EXTENDED_TIMESTAMP from SYS.DBA_COMMON_AUDIT_TRAIL. The problem I had was that AUD$.NTIMESTAMP# is of type "timestamp" while DBA_COMMON_AUDIT_TRAIL.EXTENDED_TIMESTAMP is of type "timestamp with timezone". My goal was how to compare these 2 columns, but in order to compare I had to apply the "timezone" part of EXTENDED_TIMESTAMP to NTIMESTAMP# and then compare both as "Timestamp with timezone" or to remove the timezone on EXTENDED_TIMESTAMP and compare both values as only "timestamp" values.
I found in the documentations the following functions:
But I had the following problems:
to_timestamp_tz: This function adds the timezone to the timestamp, but it doesn't apply that timezone on the timestamp data.
For example:If I have the timestamp "03/01/2016 07:00:10" And I apply "to_timestamp_tz" with the timezone as -05:00 my result is "03/01/2016 07:00:10 -05:00"
What I wanted in this case is that after to apply "-5:00" the result I should get is: "03/01/2016 02:00:10 -05:00". The function cast (AS TIMESTAMP WITH TIME ZONE ) works almost the same.
The function "cast" works as the word says: "cast". it cuts the timezone in a type "timezone with timezone",
For example:If I have the timestamp "03/01/2016 02:00:10 -05:00" and I apply the function "cast" on it the result is "03/01/2016 02:00:10", the function just remove the timezone, that's it.
Again what I was expecting is something like "03/01/2016 07:00:10". Without the timezone but applied on the timestamp.
I was looking for something that apply the timezone on the timestamp and return a timestamp as the result. "03/01/2016 02:00:10 -05:00" -> "03/01/2016 07:00:10"
Or something that adds a timezone on a timestamp, apply the timezone on the timestamp and return a "timestamp with timezone". "03/01/2016 07:00:10" & -05:00 -> "03/01/2016 02:00:10 -05:00"
I am not a developer, so I am publishing this in case someone has a better way to do this. I had to create a procedure where I add an interval or hours to the AUD$.NTIMESTAMP# which is the timezone from DBA_COMMON_AUDIT_TRAIL.EXTENDED_TIMESTAMP. And then I compared the timestamps. Something like the following:
ntimestamp#+ INTERVAL '''|| EXTRACT(TIMEZONE_HOUR FROM DBA_COMMON_AUDIT_TRAIL.EXTENDED_TIMESTAMP) ||''' HOUR=cast (DBA_COMMON_AUDIT_TRAIL.EXTENDED_TIMESTAMP as timestamp )
NOTE: You have to use "INTERVAL X HOURS" because if you do something like "AUD$.NTIMESTAMP#" + "A date type" the result will be another DATE and you will lose the precision of the timestamp. The right way to add hours or days to a timestamp is with INTERVAL, and then you will get another timestamp data with its precision.
If you are someone that is trying to do the same, here you got an idead how to do it, if you are an expert developer and you have a better way to do it, leave a comment and share your knowledge.
I might have the wrong end of the stick here, but you have two timestamps to compare, one has a time zone, the other has not. So far so good.
You would like to see both of them shoing the date and time as it would be had they both been in the same time zone?
So, for a time zone of -5:00 you could see one showing 02:00 Am and the other showing 02:00 -05:00 AM. (As it were!)
What do you get if you do this on each:
select ts_column at time zone DBTIMEZONE from table;
select ts_tz_column at time zone DBTIMEZONE from table;
I get the following for a UTC timestamp (without time zone) but the database time zone is UTC, and a PST timezone.
SQL> desc norm
Name Null? Type
---------------- -------- ----------------------------
TSTZ TIMESTAMP(6) WITH TIME ZONE
SQL> select * from norm;
01-MAR-16 10.34.26.318000 01-MAR-16 02.34.26.318000 PST
You can see a 8 hour difference. However:
SQL> select ts at time zone dbtimezone as ts,
2 tstz at time zone dbtimezone as tstz from norm;
01-MAR-16 10.34.26.318000 01-MAR-16 10.34.26.318000 +00:00
Now, hopefully, you can see that they are identical as both are in the same time zone and can now be easily compared.
I think that might help. (At least if the formatting remains accurate!)
Thank yo to take some minutes to comment.
Firstable when you have a column with "timestamp with timezone" the value stored as the timezone is the timezone of the session so it is not necessary the timezone of the database. This works in case all the values uses the same timezone of the database.
Again, than you for your comment. I was reading about "at timezone" and is very useful.
I'm aware that the time zone stored is the time zone for the session, yes. This is how, for example, a database I used to support in the UK had dates and times in various time zones as there were users across the globe. (Ok, in Europe, the US and Australia!)
Trying to compare dates in those situations can be difficult without the "at time zone" clause, which is, as you mention, very useful. We used to simply convert each timestamp to the DBTIMEZONE and comparing dates was simple.
Thank you Norm! I really appreciate your time to comment the article ;)