This discussion is archived
7 Replies Latest reply: May 23, 2013 11:51 AM by ji li RSS

getting my DBs timezone in oracle 11.2.0.3

Guess2 Newbie
Currently Being Moderated
I need the timezone of the server that the DB sits on. I don't want the offset. I need the region name. This is because some regions use daylight savings time and some do not. Plus an SA can change the time of the OS. Another company manages our servers and DBs in production and we don't have contact with them. We could use a lookup talbe and just populate it, then update it when we find out what it is in production. The problem is that I have seen cases where the timezone on servers change. Considering the lack of contact between the teams, we really need a reliable way to get the timezone out of the DB.

We tried several ways. My list is below and I explain why this is not working.

examples:
sessiontimezone: this is the timezone of my server. In theory it should be the same as the DB. We cannot take the risk that this will be out of sync.
dbtimezone: This gives the offset. Such as -5:00 for US EST. There are multiple regions that have this. Some do not use daylight savings time and some do. We would need America/New York instead.

sessiontimezone gives the timezone setting for the client. This can be altered.
dbtimezone just gives the offset such as -5:00

We get data feeds from different parts of the world. We get some data based data that is local to that regions timezone. We need to partition on this field. So we need to add a field to the DB and normalize it to the time local to our DB Server.
So if we get a record from california and the DB is on a server in US EAST, we add 3 hours. The offset won't help...

1. a timezone that we are getting from may not be in daylight savings time. We are partitioning by hour.
2. We would hit daylight savings time in New York before we hit it in California, so we would need to account for that in the math.

This hourly partition is a fixed and hard requirement. We need this to be absolutely accurate.


Here is what we tried:
What I want (pseudo-code): “Select XXX as timezone_region_name” to return “America/New_York” or “UTC”. It may be that the timezone was not set for the database at install time, and if it were, these queuries would work.

-- FAILED
SELECT DBTIMEZONE FROM dual;

--FAILED
select systimestamp, to_char(systimestamp, 'TZR'), extract (timezone_region from systimestamp) from dual;

--FAILED
SELECT systimestamp
     AT TIME ZONE DBTIMEZONE "DB Time"
FROM DUAL;

--FAILED
select to_char(systimestamp, 'TZR') from dual;
  • 1. Re: getting my DBs timezone in oracle 11.2.0.3
    ji li Pro
    Currently Being Moderated
    That's strange. Have you checked to see if TZ is set? (echo $TZ)

    At the command line, when you type 'date', you will see the timezone setting.
    If you want to modify it for your session, you can export TZ=America/Los_Angeles (for example), and then type 'date' again and you will see it changed.

    BTW: Which operating system are you on?
  • 2. Re: getting my DBs timezone in oracle 11.2.0.3
    Guess2 Newbie
    Currently Being Moderated
    I dont want to modify my session. I want to know the timezone that the OS clock is set at. When you select sysdate from dual, oracle uses the OS time. I need the active 'region', not the -05:00 offset.
    I do not want to have to rely on my OS's timezone to be set correctly. This code will run off of application servers on sesparate servers. This is managed by a completely different company who I have no contact with. So I need to be able to tell by looking in the database what timezone the DB is in.
  • 3. Re: getting my DBs timezone in oracle 11.2.0.3
    sb92075 Guru
    Currently Being Moderated
    Guess2 wrote:
    I dont want to modify my session. I want to know the timezone that the OS clock is set at. When you select sysdate from dual, oracle uses the OS time. I need the active 'region', not the -05:00 offset.
    I do not want to have to rely on my OS's timezone to be set correctly. This code will run off of application servers on sesparate servers. This is managed by a completely different company who I have no contact with. So I need to be able to tell by looking in the database what timezone the DB is in.
    Oracle DB has NO capability for determining or maintaining date, time, or timezone details; independent of the OS.
    Oracle DB relies on the OS for date, time, & timezone details;
    just as it relies upon the OS for file system operations.
  • 4. Re: getting my DBs timezone in oracle 11.2.0.3
    sybrand_b Guru
    Currently Being Moderated
    When you select sysdate from dual, oracle uses the OS time
    Incorrect.
    Oracle uses the time of the listener, when you use tns and only the time of the OS when you use bequeath or OS authentication.

    ------------
    Sybrand Bakker
    Senior Oracle DBA
  • 5. Re: getting my DBs timezone in oracle 11.2.0.3
    ji li Pro
    Currently Being Moderated
    You still didn't say which operating system the database is on.

    To check Timezone: date +%Z

    On linux, you can run strings again /etc/localtime, or as root, you can run hwclock.

    DESCRIPTION
    hwclock is a tool for accessing the Hardware Clock. You can display the current time, set the Hardware Clock to a specified
    time, set the Hardware Clock to the System Time, and set the System Time from the Hardware Clock.

    You might also want to look at tzset command.
  • 6. Re: getting my DBs timezone in oracle 11.2.0.3
    jgarry Guru
    Currently Being Moderated
    I don't think that does it, because TZ can be set by session.

    It can be worse than Sybrand described, I once saw a system where if the DBA started things manually, it would be set to the proper TZ under sh, but someone set an autoreboot script to start oracle under csh with no TZ setting - which defaults to central time. Or something like that, it bewildered a lot of people.
  • 7. Re: getting my DBs timezone in oracle 11.2.0.3
    ji li Pro
    Currently Being Moderated
    I see. Well, best thing is to set the server to use NTP to sync time with some other server (such as with atomic clock).
      someone set an autoreboot script to start oracle under csh with no TZ setting - which defaults to central time.  
    Well, we have to have some controls in place to prevent "the misguided" from doing stupid things. :-)

    For what it is worth, I believe default sets to GMT, but your point is taken.
    Need better controls and don't give out root password.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points