• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1158
  • Last Modified:

Will the sysdate value automatically update with DST time changes

Hi experts,

How to find out in the Oracle 11g database which I have, if sysdate value automatically update with DST time changes?


Thanks
0
alcsoft
Asked:
alcsoft
  • 5
  • 4
  • 4
  • +3
1 Solution
 
nemws1Database AdministratorCommented:
I'm 99.999% certain that it will (assuming your host operating system is configured to follow DST rules).
0
 
alcsoftAuthor Commented:
How I can prove it, any script I can run?
0
 
sdstuberCommented:
if you're on unix and have root privileges you could set the system clock

if you're on windows you can change the system clock


each invocation of sysdate makes a system call to the operating system to request the time, so if you change the time at the os level you'll see the change immediately on the query of sysdate

rather than changing the time you could change your operating system timezone and get a different time reported too
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
alcsoftAuthor Commented:
This is a prod environment, so I don’t want to mess up with the time.

Is there any configuration file or can I query the setting inside Oracle?
0
 
nemws1Database AdministratorCommented:
There's nothing to set in Oracle that I know of.  It relies on the operating system that its running on for the date/time.

If your OS changes/follows DST, Oracle will as well.
0
 
MazdajaiCommented:
As nemws1 mention - Sysdate returns the date and time set for the operating system.

You can double check if you are paranoid -

SELECT TO_CHAR
    (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"
     FROM DUAL;

Open in new window

0
 
PortletPaulfreelancerCommented:
SYSDATE returns the current date and time set for the operating system on which the database resides. The datatype of the returned value is DATE, and the format returned depends on the value of the NLS_DATE_FORMAT initialization parameter. The function requires no arguments. In distributed SQL statements, this function returns the date and time set for the operating system of your local database.
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions172.htm

Two time zone files are included in the Oracle Database home directory. The default time zone file is $ORACLE_HOME/oracore/zoneinfo/timezonelrg.dat, which contains all the time zones defined in the database. $ORACLE_HOME/oracore/zoneinfo/timezone.dat contains only the most commonly used time zones.
http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch4datetime.htm#i1006667 - also from that reference:
Oracle Database time zone data is derived from the public domain information available at ftp://elsie.nci.nih.gov/pub/. Oracle Database time zone data may not reflect the most recent data available at this site.

SELECT DBTIMEZONE FROM DUAL;

also see tz_offest

bottom line: check the O/S date/time and locale settings
0
 
sdstuberCommented:
Don't rely on DBTIMEZONE

That value is completely meaningless

It's odd that Oracle even exposes it.

It's only purpose is to give a baseline for TIMESTAMP WITH LOCAL TIME ZONE data types.
And, the value could be anything,  the default is +0:00, which is what Oracle recommends to set it to.  Changing it doesn't provide any functionality at all.
0
 
PortletPaulfreelancerCommented:
mmm, really the purpose of raising timezone was to indicate the reliance on o/s settings

is tz_offset(dbtimezone) also affected by that statement?
0
 
sdstuberCommented:
tz_offset(dbtimezone)

is not helpful since it produces no meaningful information either.

That will simply return the hh:mm  offset  for whatever the dbtimezone is set to.
But, since the dbtimezone doesn't have any real significance, deriving the offset of that value doesn't impart value.

Also, if the Oracle recommendation is followed, dbtimezone will be +00:00,  so tz_offset will be +00:00 as well.  So it's redundant
0
 
PortletPaulfreelancerCommented:
thanks, interesting. (all dbs I've seen recently are using an offset, hence my interest)
0
 
Geert GOracle dbaCommented:
proof ?
what happened during the previous DST change ?

testing is always possible in a non production environment
if need be, setup XE on your own PC, the XE db behaves the same as the Entreprise version
0
 
alcsoftAuthor Commented:
Thanks for the comments, Experts!
 
I want someone tell me how to prove that the current setting of the Linux/Oracle on the PROD environment is reflecting an automatic update for DST.
I can't use "oh yeah the time now is correct!", or "Let's change the time zone on the server and see what will happen". Recall what happened in the last DST is not an option, also I can't build a new machine to test the DST coz this will not reflect any changes to the time setting had applied to prod recently, if any!
So, How to prove/see that the current setting of Linux/Oracle is set to DST and automatically update the time twice a year?

PortletPaul and sdstuber, I can’t understand how the time zone offset will reflect the DST setting! Would you please explain more?
0
 
nemws1Database AdministratorCommented:
Again, does your OS automatically update for DST (it might not - if you have it set to Arizona time, which doesn't do DST).

*IF* your OS automatically updates for DST, then your Oracle instance will update for DST.  It's that simple.

Oracle does NOT try to do any time synchronization or try to keep track of what timezone you are in are even *if* you are doing DST.  It provides functionality for you to convert from one timezone to another, but SYSDATE always returns the date and time for underlying operating system installation.
0
 
sdstuberCommented:
if you can't/won't change the date or the timezone on the prod environment then you can't prove it until it happens naturally on the calendar.

however, it's easy to test how the software works just use a different machine.

Changing the timezone (not just the offset) can affect DST because that's where DST observation is defined.

On windows you select the timezone and choose dst observation with a check box

On linux the TZ environment variable for the oracle processes determines how the system time is returned.  If you use tzselect it will walk you through the timezone selection and you'll get choices that reflect the timezone offset as well as the day light saving observation.

For example

Mountain Time
vs
Mountain Standard Time - Arizona

because Mountain Time does, by default, observe DST
Arizona is in Mountain time zone but does not observe DST

so, depending on which time zone you select you'll get different DST results
0
 
PortletPaulfreelancerCommented:
seems my specifics regarding timezone inside Oracle may be irrelevant - it's just that there is a J2EE app I work with  that appears to rely on that - so it most likely does not apply to you.

However, Oracle is a "slave" to the O/S with respect to date and time - hopefully those Oracle doc references regarding sysdate make that part clear.

So in my opinion you should concentrate on the Linux settings (which I'm not the guy for by the way). Don't forget that it's the "regional settings"* that establish the DST pattern - so if that is wrong your DST would be wrong.

*I've forgotten so much about 'nix I don't recall what term it uses for this - hopefully you know what I mean.

Oh: regarding "proof" - my suggestion
You have production servers you can't touch, but you can 'audit'
So 'audit' the regional settings and date/times on the production servers
THEN
replicate those settings in a test environment
THEN test

and, also during the "audit" figure out how servers time gets synched to some authoritative source

If you identify problems as a result of the audit/test - then follow your change management process to fix issues in prod.

not much else I can suggest
0
 
nemws1Database AdministratorCommented:
PortletPaul - it's just a timezone file (/etc/timezone) typically. ;-)  This used to be rather simplistic, like just containing 'CST2CDT'.  Most *NIX systems get a little more specific nowadays, like "America/Chicago", so they can handle the odd cases out there.

Anyways, alcsoft - has this server been running for more than a 3 months?  If so, you probably already experienced a daylight savings shift.  Any chance of finding some old log files to make sure the OS changed time the way you expected to on March 10th of this year?
0
 
PortletPaulfreelancerCommented:
btw, as I'm southern hemisphere and UTC + 10 I'm about as useful as a kangaroo in a dingy when it comes to Northern DST settings.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

  • 5
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now