Solved

Will the sysdate value automatically update with DST time changes

Posted on 2013-06-13
18
967 Views
Last Modified: 2013-06-14
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
Comment
Question by:alcsoft
  • 5
  • 4
  • 4
  • +3
18 Comments
 
LVL 23

Expert Comment

by:nemws1
Comment Utility
I'm 99.999% certain that it will (assuming your host operating system is configured to follow DST rules).
0
 

Author Comment

by:alcsoft
Comment Utility
How I can prove it, any script I can run?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 

Author Comment

by:alcsoft
Comment Utility
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
 
LVL 23

Expert Comment

by:nemws1
Comment Utility
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
 
LVL 21

Expert Comment

by:Mazdajai
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
thanks, interesting. (all dbs I've seen recently are using an offset, hence my interest)
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
 

Author Comment

by:alcsoft
Comment Utility
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
 
LVL 23

Expert Comment

by:nemws1
Comment Utility
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 
LVL 23

Expert Comment

by:nemws1
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now