Oracle timezone offset differs when run in sqlplus vs sql developer

Question

Here's a puzzler. Take the following query and execute in oracle sqldeveloper:

select to_char(CAST (sysdate AS  TIMESTAMP WITH TIME ZONE) ,'TZH:TZM') dst,    
           to_char(CAST (sysdate-160 AS  TIMESTAMP WITH TIME ZONE) ,'TZH:TZM') nodst    
 from dual;

you will get the results of "-04:00", "-05:00". This gives the correct dst-adjusted timezone offset (eastern) with one date in dst, and the other not. Running the same query from sqlplus gives both values as "-4:00". This is causing a problem for a package that when called from sqlplus shows the incorrect value also.

What are session time zones in both clients? This is the same effect - not saying you have exactly those values, of course, but they appear to be different. (Why are you converting sysdate, rather than just using systimestamp? Maybe just as an example date?) — Alex Poole
– Alex Poole, Commented Jul 13, 2021 at 22:30

Alex Poole · Accepted Answer · 2021-07-14 09:59:13Z

3

When you cast to a timestamp with time zone, Oracle has to choose which time zone to use; and it uses the current session time zone. It's equivalent to doing:

select to_char(FROM_TZ(CAST (sysdate AS  TIMESTAMP), SESSIONTIMEZONE) ,'TZH:TZM') dst,    
           to_char(FROM_TZ(CAST(sysdate-160 AS  TIMESTAMP), SESSIONTIMEZONE) ,'TZH:TZM') nodst    
 from dual;

As you are getting different results in SQL Developer and SQL*Plus, you seem to have different session time zones in those two clients. You can check by querying sessiontimezone in each. SQL Developer sets the session time zone from the Java time zone (picked up from the operating system by default; you can override that by passing a user.timezone value at start-up). SQL*Plus uses the ORA_SDTZ environment variable, so you can set that to match your locale if you don't want to set it from within the database with alter session (manually or via login.sql); and if unset it defaults to 'OS_TZ':

The default value of the ORA_SDTZ variable, which is used when the variable is not set or it is set to an invalid value, is 'OS_TZ'.

... and it's picking that up as an offset (-04:00) rather than a region.

If you always want the result to be in a particular time zone regardless of any user's session settings, then you can state which one to use instead, e.g.:

select to_char(FROM_TZ(CAST (sysdate AS  TIMESTAMP), 'America/New_York') ,'TZH:TZM') dst,    
           to_char(FROM_TZ(CAST(sysdate-160 AS  TIMESTAMP), 'America/New_York') ,'TZH:TZM') nodst    
 from dual;

db<>fiddle with several session settings to demonstrate.

Presumably your real query is working from a variable date value, not sysdate; otherwise you could use systimestamp instead, with at time zone to adjust to a different zone if necessary.

edited Jul 14, 2021 at 9:59

answered Jul 13, 2021 at 22:45

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MT0 Over a year ago

If you are going to use the date/time component (rather than just the time zone component) then you want to use CURRENT_DATE rather than SYSDATE since you want the session time and not the system time when you are also using SESSIONTIMEZONE.

Alex Poole Over a year ago

True; also true if you do only want time zone really, in case you happen to be looking at a date/time around a DST switch. (Though I still think sydate/current_date is probably just a placeholder for a real date in the question...)

user5683823 Over a year ago

The answer would be clearer if it also explained what the OP observed. You hinted at that in your question below the original post. Very likely, the session time zone (saved in settings somewhere) for the SQL Developer session is 'America/New_York' (which then is DST-aware), while the default session time zone for the SQL*Plus session is a hard-coded '-04:00'. I use the same interfaces, and I don't recall ever messing with these settings, so I must have the out-of-the-box defaults; and indeed they are similar to what I described (except for Los Angeles rather than New York).

MT0 · Accepted Answer · 2021-07-13 22:44:13Z

2

This gives the correct dst-adjusted timezone offset (eastern) with one date in dst, and the other not.

No, it gives you a manually adjusted value that appears to be correct; however, when DST ends then your query will be incorrect and it will require adjusting ... then next spring it will require adjusting again ... and next autumn.

If you want the correct values then let Oracle adjust the time zones (and you don't need to cast SYSDATE to a TIMESTAMP WITH TIME ZONE, you can just use SYSTIMESTAMP or CURRENT_TIMESTAMP):

SELECT TO_CHAR(SYSTIMESTAMP AT TIME ZONE 'US/Eastern', 'TZH:TZM')
         AS timezone
FROM   DUAL

answered Jul 13, 2021 at 22:44

MT0

173k12 gold badges70 silver badges136 bronze badges

Collectives™ on Stack Overflow

Oracle timezone offset differs when run in sqlplus vs sql developer

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related