Solved

Will the sysdate value automatically update with DST time changes

Posted on 2013-06-13
18
1,002 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
  • +3
18 Comments
 
LVL 23

Expert Comment

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

Author Comment

by:alcsoft
ID: 39246028
How I can prove it, any script I can run?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39246050
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
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 

Author Comment

by:alcsoft
ID: 39246078
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
ID: 39246099
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
ID: 39246395
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 49

Expert Comment

by:PortletPaul
ID: 39246420
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 74

Expert Comment

by:sdstuber
ID: 39246488
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 49

Expert Comment

by:PortletPaul
ID: 39246504
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39246528
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 49

Expert Comment

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

Expert Comment

by:Geert Gruwez
ID: 39246956
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
ID: 39247686
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
ID: 39247725
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 74

Accepted Solution

by:
sdstuber earned 400 total points
ID: 39247755
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 49

Expert Comment

by:PortletPaul
ID: 39247775
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
ID: 39247797
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 49

Expert Comment

by:PortletPaul
ID: 39247846
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

630 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