Tony’s Oracle Tips

February 1, 2010

Session Time Zone Versus Database Time Zone

Filed under: Uncategorized — tonyhasler @ 1:19 pm

It is funny how you start to learn lots of new things about Oracle when you start working in an environment that is different from ones that you have worked with before. I am currently working with a Windows laptop with UK regional settings and am connecting to databases in New York via a standard Oracle client. I have a colleague in Singapore accessing the same databases and that is how I discovered the weird NLS_TERRITORY issue in this earlier post.

Now here is another time zone related issue that can cause different results with clients in different time zones. What do you think the result of this query is?


SELECT SYSTIMESTAMP-(SYSTIMESTAMP-1) FROM DUAL;

First let us work out the type of the result. SYSTIMESTAMP is of type TIMESTAMP WITH TIME ZONE which I will abbreviate to TTZ for the rest of this post. The documentation tells us that when we subtract a numeric value from a TIMESTAMP (no distinction is made with TTZ) the resultant type is a TIMESTAMP. This appears to be either a documentation error or a bug as the actual type of the expression SYSTIMESTAMP-1 is DATE. If it is a bug then it can never be fixed (for compatibility reasons) so it becomes a documentation error anyway! The same table also tells us that the difference between a TIMESTAMP and a DATE is an INTERVAL [DAY TO SECOND]. And that is indeed the type of the result our query.

Now let us turn our attention to the value of this interval. The numeric ‘1’ is treated as meaning ‘1 day’ so you would imagine that SYSTIMESTAMP-1 would yield the same result as SYSDATE-1 wouldn’t you? Well if that is what you thought you would be right. You get a DATE that specifies the date and time 24 hours ago specified in the time zone of the database (but see update below) as opposed to your session: the time zone information has just been dropped. Now if we consult that same table again we can see that when we subtract a DATE from a TTZ we get an interval. But this note says that “[When] one operand is a DATE value[…] and the other operand has a time zone value the Oracle uses the session time zone in the returned value.” The wording is a little ambiguous but the upshot is that the DATE is now interpreted as being associated with the session time zone. This means that the result of the query is “one day plus (the session time zone minus the database time zone)”. So if the database and the session time zone are the same then you get a fairly predictable result: an INTERVAL DAY TO SECOND of one day (ignoring the fraction of a second error that gets introduced in the conversions) but otherwise you get something else. Try this script:


ALTER SESSION SET TIME_ZONE='-11:00'  -- Something other than the time zone of your database server
                                      -- But see update note below
/
SELECT SYSTIMESTAMP-(SYSTIMESTAMP-1) FROM DUAL;

Note that there is no such problem if you use CURRENT_TIMESTAMP (a TTZ based on the session timestamp) or LOCALTIMESTAMP (a TIMESTAMP with no time zone also based on the session time zone).

UPDATE:  I mention the “time zone of the database” above but this should not be confused with the “database time zone” that you see by using the built-in DBTIMEZONE function.  The latter is only used for TIMESTAMP WITH LOCAL TIME ZONE data types.  The time zone used by the function SYSTIMESTAMP is the time zone taken from the operating system of the server on which the database resides.

Advertisements

1 Comment »

  1. […] an understanding of why this works see my other blog entry here. This issue is specific to the TIMESTAMP WITH TIME ZONE datatype.  But there are far worse issues […]

    Pingback by Tony’s Tirade against TIMESTAMP WITH TIME ZONE « Tony’s Oracle Tips — September 4, 2010 @ 11:34 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: