Oracle Dates and Calendar – Complete reference for the new Oracle DBA





A new oracle dba can find complete information about oracle dates , oracle date formats , timezones etc.

Dates & Calendars – Frequently Asked Questions [ID 227334.1]  

 
  Modified 20-OCT-2009     Type FAQ     Status PUBLISHED  
       

In this Document
  Purpose
  Questions and Answers
     Why is my setting of NLS_DATE_FORMAT in the init.ora not used?
     How should the default date format be changed or set?
     What is the difference between the YY and RR format masks?
     What is the difference between the YYYY and RRRR format masks?
     Why does Oracle allows date who are not exactly the same as the format mask?
      Where can I find a list and explanation of all possible date format masks?
     How does Oracle store the DATE datatype internally?
     Why is my SYSDATE time not the same as my system clock on Windows ?
     Why is my SYSDATE time not the same as my system clock on Unix?
     Why is my SYSDATE time not the same as my system clock on VMS?
     How do I see & set the TZ environment variable in a RAC environment
     How do I set up 2 databases with a different SYSDATE time on the same server?
     My Alert.log has wrong time for some or all entry’s.
     How does week numbering work & why do (some) years have 53 weeks?
     How can I switch and convert between Calendars?
     Where to find (Oracle) information on non-western Calendars ?
     Where can I find more external information about calendars?
     A complete overview of the TZ (sysdate) behavior on Unix platforms:
     Sysdate and DST changes.
  References


Applies to:

Oracle Server – Enterprise Edition – Version: 8.0.3.0 to 11.2.0.2
Information in this document applies to any platform.

Purpose

This note covers the FAQ for DATEs and Calendars in the Oracle database.
Please note that TIMESTAMPs and timezones are covered separately in: Note:340512.1 Timestamps & timezones – Frequently Asked Questions

Please be aware that the influence of the OS timezone on functions like SYSDATE will be covered in this note below.

For the main Globalization (NLS) FAQ overview please see: Note 60134.1 Globalization (NLS) – Frequently Asked Questions

Questions and Answers

Why is my setting of NLS_DATE_FORMAT in the init.ora not used?

There are 3 levels at which you can set NLS parameters:

  • Database
  • Instance
  • Session

If a parameter is defined at more than one level then the following rules apply:

  1. NLS database settings are overwritten by NLS instance settings
  2. NLS database & NLS instance settings are overwritten by NLS session settings

This is fully explained in: Note:241047.1 The Priority of NLS Parameters Explained.
If you have NLS_LANG set on the client you implicitly also set all other NLS parameters through that. That means that any setting you have in the init.ora for NLS_DATE_FORMAT gets "overwritten" (following rule ‘2’ from above) by this setting from the client. See the following question on how to work around this.

How should the default date format be changed or set?

As stated above, if NLS_LANG is set in the client OS environment then setting parameters in the init.ora has no effect because the "instance" settings are overwritten by the session setting coming from the client environment..
Therefore, if NLS_LANG is set and you want a different NLS_DATE_FORMAT you must also set NLS_DATE_FORMAT in the client environment to override the default that is taken from the NLS_LANG.

What is the difference between the YY and RR format masks?

The RR format mask was introduced to deal with end-of-century issues for 2-digit format masks. A problem arises when using a 2-digit year. "12" could mean "2012"  or it could mean "1912" for example. The YY format mask always assumes that the 2 digits are the year in the "current" century. So "12" with a format mask of "YY" now means "2012" but if the same application ran in 1998 the same input would have been read as "1912".
The "RR" format mask shifts this cut-of point by 50 years. Obviously you can still only define a period of 100 years with a 2 digit number so there is no fool-proof case. When using the "RR" format mask every value 00 to 49 means this same century as long as we’re currently before year 50 as well, if we’re currently after year 50 a value of 00 to 49 is read as a year in the next century. The same is true the other way around. If we’re currently before year 50 then a value of 50 to 99 is read as the previous century and a value of 00 to 49 is read as the current century. So the four cases are like this:

  • in 1998 a value of "05" (with format mask "RR") means 2005
  • in 1998 a value of "95" (with format mask "RR") means 1995
  • in 2003 a value of "05" (with format mask "RR") means 2005
  • in 2003 a value of "95" (with format mask "RR") means 1995

So by using this format mask there are no problems when the century changes, however when RR is used as a format mask new problems will arise when the year 2050 comes closer so by then the format mask YY will have to be used again. As a consequence we advise to always use a 4-digit year to resolve these problems completely (or at least for the next 8000 years).
For some more information on this also see the following note: Note:30557.1 NLS_DATE_FORMAT and a Default Century

What is the difference between the YYYY and RRRR format masks?

When the RR format mask was introduced (see above), RRRR also became a valid format mask.
However, when a 4-digit year is used there can be no question what you mean. 2003 is always the year 2003, 1996 is always the year 1996. Therefore the format masks YYYY and RRRR are exactly the same.

Why does Oracle allows date who are not exactly the same as the format mask?

You need to use the FX (format exact) format modifier to require an exact match of the expression and the format string (see next section for doc link).

SQL> SELECT TO_CHAR (TO_DATE('02*2007','MM/YY'), 'MM/YY') FROM DUAL;

TO_CHAR(TO
----------
02/07

SQL> SELECT TO_CHAR (TO_DATE('02*2007','fxMM/YY'), 'MM/YY') FROM DUAL;
SELECT TO_CHAR (TO_DATE('02*2007','fxMM/YY'), 'MM/YY') FROM DUAL
                        *
ERROR at line 1:
ORA-01861: literal does not match format string

 Where can I find a list and explanation of all possible date format masks?

Oracle® Database SQL Language Reference 11g Release 2 (11.2) "Datetime Format Models"
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements004.htm#CDEHIFJA

How does Oracle store the DATE datatype internally?

Oracle stores DATE type data in a proprietary format. DATE values are always stored in 7 bytes. These bytes store the century, year, month, day, hour, minute, and second details respectively. For more detailed information please refer to: Note:69028.1 How does Oracle store the DATE datatype internally?

Why is my SYSDATE time not the same as my system clock on Windows ?

Please note that this problem does not seem to occur on recent (9i/10g) Oracle versions on recent (Win2K/XP/Win2K3) Windows versions, but in older product combinations it can happen (in certain circumstances) that after you change the timezone on a Windows machine you have to restart the Oracle service. Before restarting the service the SYSDATE will reflect the time as if it was still in the original timezone. This is important if you have the Windows setting "Automatically adjust clock for daylight saving changes" switched on because DST is implemented through a change in the timezone. For example a machine in "W. Europe Standard Time" (automatically) changes into "W. Europe Daylight Time" at the start of DST.
This means that when daylight savings time starts or ends you have to restart the service, the hour difference is not reflected in the SYSDATE until after a restart of the Oracle service (as stated, in recent product combinations this problem does not seem to occur and there is no need to restart the Oracle service). Third party information that might be useful:
Description of MS Date and Time Format Standards: http://support.microsoft.com/?kbid=247873
Where does Windows 2000 (and Windows NT) store time zone information: http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=14966&DisplayTab=Article

Please note that "sysdate" has *nothing* to with DB_TIMEZONE or other timezone related date formats like systimestamp. Note 340512.1 Timestamps & timezones – Frequently Asked Questions.

To debug situations in which you have a unexplained difference between the oracle sysdate and the system time you see on Windows, use the following method:

open a dos box
connect using sqlplus in the dos box:

1) once through the listener using a tnsnames alias
c:\>sqlplus user/password @ [tnsnames alias]
SQL>select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;

2) once trough a "local" ORACLE_SID connection
c:\>set oracle_sid [your oracle_sid]
c:\>sqlplus user/password
SQL>select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;

Check that the time in the banner of sqlplus ( SQL*Plus: Release 10.1.0.4.0 - Production on Wo Jan 11 15:05:46 2006 ) is reflecting the time based on the current time of the Windows server.

If the time is different then stop and starting the listener service.Make sure you double check what listener you are using when having multiple listeners.

Stopping and starting the listener will not be enough if:

a) If you are using MTS then you might see a correct result with a dedicated connection but not with a shared.

b) If you are using Automatic Listener registration (= happens always when listener runs on port 1521) or you have in the database the parameter "local_listener" set to the port the listener is running on.

In any of those 2 cases you will need to stop and start the database also.

C:\>time

C:\>lsnrctl

lsnrctl> stop <listenername>
lsnrctl> start <listenername>
lsnrctl> exit

when using MTS or automatic listener registration do also

c:\>sqlplus /nolog
SQL>conn / as sysdba
SQL>shutdown immediate
SQL>exit

c:\>net stop OracleService<sid>
c:\>net start OracleService<sid>

Note:130574.1 Disabling Automatic Registration of the Database with the Default Listener

Why is my SYSDATE time not the same as my system clock on Unix?

The SYSDATE function simply performs a system-call to the Operating System to get the time (a "gettimeofday" call).
The OS (Unix) TZ environment variable influences the time that the OS will pass on to Oracle. So even though sysdate itself does not use the timezones in the database, it is influenced by the (non-Oracle) TZ environment variable on the OS.

Please note that "sysdate" has *nothing* to with DB_TIMEZONE or other timezone related date formats like systimestamp. Note 340512<BR< a>

To debug situations in which you have a unexplained difference between the oracle sysdate and the system time you see on Unix, use the following method:

telnet to the Unix box
connect using sqlplus in the telnet session:

1) once through the listener using a tnsnames alias
$sqlplus user/password @ [tnsnames alias]
SQL>select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;

2) once trough a "local" ORACLE_SID connection
$env | egrep 'ORACLE_SID'
$sqlplus user/password
SQL>select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;

Check that the time in the banner of sqlplus ( SQL*Plus: Release 10.1.0.4.0 - Production on Wo Jan 11 15:05:46 2006  ) is reflecting the time based on the current TZ set in the Unix (!) session.

If the results are different this means that the listener is started with a different TZ then you current user environment. To resolve this simply stop and start listener with the TZ you want to use.More information about the TZ variable can be found here: http://www.twinsun.com/tz/tz-link.htm. Make sure you double check what listener you are using when having multiple listeners.

Stopping and starting the listener will not be enough if:

a) If you are using MTS then you might see a correct result with a dedicated connection but not with a shared.

b) If you are using Automatic Listener registration (= happens always when listener runs on port 1521) or you have in the database the parameter "local_listener" set to the port the listener is running on.

In any of those 2 cases you will need to stop and start the database also.

$ date
$ lsnrctl

lsnrctl> stop <listenername>
lsnrctl> start <listenername>
lsnrctl> exit

when using MTS or automatic listener registration do also

$ date
$ sqlplus /nolog
SQL>conn / as sysdba
SQL>shutdown immediate
SQL>startup
SQL>exit

Note:130574.1 Disabling Automatic Registration of the Database with the Default Listener

On Solaris you can use $ pargs -e <process id> to print the current environment of a running process
On Linux you can cat /proc/$PID/environ, where $PID is the process id to get the environment when the process was started.

Why is my SYSDATE time not the same as my system clock on VMS?

For VMS please see: Note:1017965.102 VMS: Time Displays Incorrectly in the Banners of Oracle Tools

How do I see & set the TZ environment variable in a RAC environment

If you are using RAC then use the following to see the environment settings:

$ srvctl getenv database -d <dbname>

and set it trough:

$ srvctl setenv database -d <dbname> -t TZ=<the TZ you want>
$ gsdctl stop
$ gsdctl start

Note that depending on the version you work with you might have to restart the CRS.

How do I set up 2 databases with a different SYSDATE time on the same server?

The SYSDATE function simply performs a system-call to the Operating System to get the time (a "gettimeofday" call). Because sysdate returns a DATE datatype, and timezone functionality is only available through the TIMESTAMP datatypes, sysdate does not use (or even know of) the timezone in the database. Therefore you can not influence the sysdate values by setting up databases with different timezones.
However, on Unix you can use the OS "TZ" environment variable to alter the time that the OS will pass on to Oracle (also see above). Therefore you can use this to influence the time that the OS passes to Oracle as the "current time", and therefore you can set up multiple databases on the same machine returning different times in the sysdate function.
To do this you have 2 options:

1) When NOT using automatic listener registration simply start a separate listener for each database, and set the TZ appropriate for the database before you start the corresponding listener. It is also a good idea to set the "correct" TZ in the Unix environment of the user who (re)starts the database
If you use port 1521 for one of the listeners, then you should also disable Automatic Service Registration by setting the following parameter to the init.ora file:

LOCAL_LISTENER=dummy

(in 9i you can do ALTER SYSTEM SET LOCAL_LISTENER=dummy SCOPE=SPFILE; )
For more information on the background of this please see:
Note:301420.1 Why does sysdate have the Wrong Time Stamp when Connecting via the Listener Note:399448.1 How To setup TNS listener to Show More Than one Timezone

2) Alternatively if the only listener registration is automatic listener registration then this can be used to have 2 databases with on listener and still have different timezones:

* start the listener on port 1521 WITHOUT any database configured in the listener.ora.
* start each database with the TZ variable you want to use.

PMON will register the database with the listener and the use TZ will be the one used to start the database, not listener. If the listener is not running on port 1521 then you need to specify in EACH database the LOCAL_LISTENER to enable automatic listener registration. This is also usefull when using MTS.

Note: Any of this can not be done on windows because Windows lacks the functionally of setting a different timezone for different processes. The timezone setting in Windows is "global" for all processes on the machine.

My Alert.log has wrong time for some or all entry’s.

The time used for the message in the alert.log is the same as "sysdate" for the process that is writing to the alert.log. Alert.log messages are not written by "Oracle" they are written by the Oracle proces that is responsible for that error/message (including user sessions). Most messages in the Alert.log are written by background processes like Pmon, the used "sysdate" for the background processes is defined (on unix) by the TZ variable set when the database was started.
If however a other process (like a user process/session encountering an error or doing a shutdown of the database) writes to the Alert.log and that user session uses a other TZ setting then the time for the error or parts of the shutdown sequence will be using a different time compared to the entry’s written by the background processes (like log switches).

Note that:
a)if you do a "select to_char(sysdate,’DD-MON-YY HH24:MI:SS’) from dual;" will give a correct result, even with a ORACLE_SID connection because here the environment is set by the executable that is spawning the process and that is sqlplus (or the listener if it’s a listener connection), not the database kernel.
You need to see what is actually "off", if this is the shutdown time for example or the time for a application error then simply use the correct TZ for the session (which can be batch job) that does the shutdown. If entry’s like logswitches are "off" from the expected time then you need to restart the database and listener with the required TZ setting.
b) This has nothing to do with the database timezone,session time etc.

How does week numbering work & why do (some) years have 53 weeks?

As can be seen in the link above, Oracle has 2 format masks that return a (different) week number for a input date.
The WW format mask very simply calculates week 1 starting on the first day of the year and continues to the seventh day of the year, etc. etc. Because a year consists of 365 or 366 days this means that 31-DEC (and in leap years 30-DEC) falls in week 53.
The IW format mask follows ISO-8601 to assign a number to a week of the year. The ISO week always starts on a Monday (this is the international standard, different than the US standard!), the week that contains the first Thursday in January is week nr 1. All previous days of that week (the last days of the preceding year) also belong to "week 1" of that year.
This means that in the ISO calendar there is only a week 53 if the 1-JAN of that year was between Thursday and Sunday (or Wednesday in leap years). For example 1-JAN-2009 falls on a Thursday, making that whole week "week 1 of year 2009", 1-JAN-2010 falls on a Friday, which means that that week is still part of the previous year 2009, making that the 53rd week of 2009. http://download.oracle.com/docs/cd/B28359_01/server.111/b28298/ch9sql.htm#CIHGFJEI

How can I switch and convert between Calendars?

Conversion between calendars is completely transparent. As an example:

drop table dates;
create table dates ( datecol date);
alter session set nls_calendar = 'Gregorian';
insert into dates values(to_date('20031023', 'yyyymmdd'));
select datecol from dates;
alter session set nls_calendar = 'Persian';
select datecol from dates;
alter session set nls_calendar = 'English Hijrah';
select datecol from dates;

You will see that you get the same date but converted to the different calendars.

Where to find (Oracle) information on non-western Calendars ?

The list of calendars Oracle supports can be found in the documentation:
http://download-west.oracle.com/docs/cd/B12037_01/server.101/b10749/applocal.htm#637657
Also see Note:121061.1 FAQ on the Arabic Hijrah Calendar

Note that
* the current SQL functions sensitive to NLS_CALENDAR are ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, interval, TO_CHAR and TO_DATE. All other functions (like Trunc) are using the Gregorian calendar and ignore the NLS_CALENDAR setting.
* known issue: BUG 8921336 MONTHS_BETWEEN RETURNING WRONG RESULTS WHEN NLS_CALENDAR = ‘PERSIAN’

Where can I find more external information about calendars?

There are quite a few websites that give very good and interesting background information about calendars. The information on these websites often goes a lot further than the oracle scope and these websites are in no way linked with Oracle. We have only included them for your information in case you’re interested in reading more background information about these subjects:
http://www.calendarzone.com/
http://na-tzul.pauahtun.org/CalendarFAQ/
http://webexhibits.org/calendars/

A good summary of calendars, dates, and times from past to the present, including details on using the ISO 8601 standard: http://www.exit109.com/~ghealton/y2k/yrexamples.html

A complete overview of the TZ (sysdate) behavior on Unix platforms:

* When using a ORACLE_SID connection then the TZ used is the one set in the user environment that starts sqlplus seen sqlplus is spawning the session.

Please Note:

* We refer here always to the Unix TZ variable, on some Unix platforms / versions it’s not needed to define this and other means can be used like /etc/TIMEZONE for example. The idea is however the same, the time is defined on Unix level and Oracle RDBMS uses this setting without further influencing it.  The Unix TZ variable should however on every Unix platform override any other setting for non-daemon sessions. Please consult you OS vendor to know the exact behavior on your Unix platform and version if needed.

* On some Unix OS’s daemon processes and their children get their timezone not from any user TZ setting, but rather from the machine defaults as set in /etc/default/tz or /etc/TIMEZONE. Please consult you OS vendor to know the exact behavior on your Unix platform and version if needed.

On Solaris you can use $ pargs -e <process id> to print the current environment of a running process
On Linux you can cat /proc/$PID/environ, where $PID is the process id to get the environment when the process was started.
* When using a dedicated listener connection and you are *not* using dynamic database service registration then the TZ used is the one set in the user environment that starts the listener (= the listener is responsible for the database registration) seen the listener registration environment is spawning the session. 

* When using a dedicated listener connection and you are using dynamic database service registration then the TZ used is the one set in the user environment that starts the database seen PMON is responsible for the registration with the listener and the listener registration environment is spawning the session.

* When using MTS then the TZ used is the one set in the user environment that starts the database seen the dispatchers are spawning the session and they are started in the database context.

* When using RAC with srvctl then the used TZ is the TZ defined in the gsdctl environment. If you stop and start RAC "manual" with sqlplus or lsnrctl then the above behavior is used. (Note that RAC is using dynamic database service registration). In most circumstances it will therefore be needed to restart the CRS after changing the TZ environment variable.

Sysdate and DST changes.

"sysdate" (and "systimestamp") do not use any Oracle provided timezone information. None of these are using in any way Oracle DST patches , the DB_TIMEZONE (database timezone) or Oracle provided DST information. "Sysdate" is purely dependent on the operating system clock, hence it IS depending on the timezone information of this operating system and/or the operating system "TZ" variable settings when the database and listener where started (!!!).
Please note that this, while it may affect Oracle, is NOT dependent on an Oracle setting or "Oracle DST patch" but a pure OS setting. The "TZ" variable is NOT an Oracle variable.

For "sysdate" 2 things are possible:

A timezone can be defined in 2 way’s: by a OFFSET (numeric value) like -08:00 or by a NAMED timezone like ‘Iran’ or ‘America/Vancouver’ or ‘CET’. Only an NAMED timezone can be affected by or "follow" DST.
1*The listener and database are now started using an OS level TZ setting to a "named timezone" (for example ‘America/Vancouver’ or ‘CET’ – check your OS vendor documentation for possible timezone names) and the OS knows the correct DST information for this named timezone. In this case the OS will change the OS time during the DST transition and Oracle will also recieve the correct "sysdate".

2* your listener and database is now started using an OS level TZ OFFSET ( for example +06.00 ) then, to have a correct "sysdate" you will need to:

– OR set the OS "TZ" variable correctly to reflect the new OFFSET (for example +07:00) and then restart the database and listener during the DST transition to use the new setting
– OR update the OS DST information, set the OS "TZ" variable correctly to reflect the named timezone for your region and then restart the database and listener using this new TZ setting.

Again, Oracle will use the OS "TZ" setting when Oracle was *started* (like any other OS process / program) this MAY be different then the setting you have now set in the OS environment.

For any question on how to see for an already started process the used OS environment settings we advise you to check with your OS vendor. On Solaris you can use $ pargs -e <process id> to print the current environment of a running process. On Linux you can cat /proc/$PID/environ, where $PID is the process id to get the environment when the process was started. Do not forget to check both the listener and oracle process.

For RAC systems the above is also true when the instances/listeners are started manually (sqplus/lsnrctl), when using srvctl the information stored in the GSD is used, see section "How do I see & set the TZ environment variable in a RAC environment".

if you are using DBMS_JOB /DBMS_SCHEDULER you may want to check Note.467722.1 DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained.

References

NOTE:121061.1 – FAQ on the Arabic Hijrah Calendar
NOTE:130574.1 – Disabling Automatic Registration of the Database with the Default Listener
NOTE:1811.1 – HOW TO SET UP TIMEZONES ON UNIX
NOTE:241047.1 – The Priority of NLS Parameters Explained
NOTE:301420.1 – Why does sysdate have the Wrong Time Stamp when Connecting via the Listener
NOTE:30557.1 – NLS_DATE_FORMAT and a Default Century
NOTE:338832.1 – How to debug date related problems like (but not limited to) ORA-01843, ORA-01821, ORA-1801
NOTE:340512.1 – Timestamps & time zones – Frequently Asked Questions
NOTE:399448.1 – How To setup TNS listener to Show More Than one Timezone
NOTE:60134.1 – Globalization (NLS) – Frequently Asked Questions
NOTE:69028.1 – How does Oracle store the DATE datatype internally?


Author: admin