Section 24.6.  Date/Time

24.6. Date/Time Our globalization discussion thus far has been focused on strings. Date/time issues, however, can be every bit as troublesome when localizing an application. Users may be on the other side of the world from your database and web server, but they still require accurate information relating to their time zone, and the format of the date and time must be in a recognized structure. Consider the following issues related to date/time: There are different time zones around the world. Daylight savings time exists for some regions, and not for others. Certain locales use different calendars. Date/time formatting is not consistent throughout the world. 24.6.1. Timestamp Datatypes Until Oracle9i Database, working with dates and times was fairly straightforward. You had the DATE type and the TO_DATE function. The limited functionality of the DATE type made application development of global applications somewhat tedious, though. All time zone adjustments involved manual calculations. Sadly, if your application is to work with Oracle8i Database or earlier versions, I'm afraid you are still stuck with this as your only option. Those of us working with Oracle9i Database and higher, however, benefit greatly from the TIMESTAMP and INTERVAL datatypes that were discussed in detail inChapter 10. If you have not read that chapter yet, we'll provide a quick overview here, but I do recommend that you go back and read that chapter to obtain a thorough understanding of the topic. Lets take a look at an example of the TIMESTAMP, TIMESTAMP WITH TIME ZONE, AND TIMESTAMP WITH LOCAL TIME ZONE datatypes in action: SET SERVEROUTPUT ON DECLARE v_date_timestamp TIMESTAMP ( 3 ) := SYSDATE; v_date_timestamp_tz TIMESTAMP ( 3 ) WITH TIME ZONE := SYSDATE; v_date_timestamp_ltz TIMESTAMP ( 3 ) WITH LOCAL TIME ZONE := SYSDATE; BEGIN DBMS_OUTPUT.put_line ('TIMESTAMP: ' || v_date_timestamp); DBMS_OUTPUT.put_line ('TIMESTAMP WITH TIME ZONE: ' || v_date_timestamp_tz); DBMS_OUTPUT.put_line ( 'TIMESTAMP WITH LOCAL TIME ZONE: ' || v_date_timestamp_ltz ); END; / The following dates and times are returned: TIMESTAMP: 08-JAN-05 07.28.39.000 PM TIMESTAMP WITH TIME ZONE: 08-JAN-05 07.28.39.000 PM -07:00 TIMESTAMP WITH LOCAL TIME ZONE: 08-JAN-05 07.28.39.000 PM TIMEZONE and TIMEZONE WITH LOCAL TIMESTAMP are identical because the database time is in the same locale as my session. The value for TIMESTAMP WITH TIMEZONE shows that I am in the Mountain time zone. If I were in accessing my Colorado database via a session in California, the result would be slightly different. TIMESTAMP: 08-JAN-05 07.28.39.000 PM TIMESTAMP WITH TIME ZONE: 08-JAN-05 07.28.39.000 PM -07:00 TIMESTAMP WITH LOCAL TIME ZONE: 08-JAN-05 06.28.39.000 PM The value for TIMESTAMP WITH LOCAL TIMEZONE used the time zone of my session, which is now Pacific, or -08:00, and automatically converted the value. 24.6.2. Date/Time Formatting One localization challenge we face is related to date and time formatting . Japan, for example, may prefer the format yyyy/MM/dd hh:mi:ssxff AMwhile in the United States, you would expect to see dd-MON-yyyy hh:mi:ssxff AM. A common way to handle this situation is to include a list of format masks in a locale table that maps to the user. When the user logs in, his assigned locale maps to the correct date/time format for his region. The g11n schema has a USERS table and a LOCALE table, joined by a locale_id. Let's take a look at some examples using date/time functions (discussed in detail inChapter 10), and the format masks provided in the g11n.locale table. The registration_date column in the table uses the TIMESTAMP WITH TIME ZONE datatype. Using the TO_CHAR function and passing the format mask for each user's locale displays the date in the correct format. CREATE OR REPLACE FUNCTION date_format_func RETURN sys_refcursor IS v_date sys_refcursor; BEGIN OPEN v_date FOR SELECT locale.locale_desc "Locale Description", TO_CHAR (users.registration_date, locale.DATE_FORMAT ) "Registration Date" FROM users, locale WHERE users.locale_id = locale.locale_id;   RETURN v_date; END date_format_func; / To execute it, do the following: variable v_format refcursor CALL date_format_func( ) INTO :v_format; PRINT v_format This prints the following result set: Locale Description Registration Date ----------------------- ------------------ English 01-JAN-2005 11:34:21.000000 AM US/MOUNTAIN Japanese 2005/01/01 11:34:21.000000 AM JAPAN German 01 January 05 11:34:21.000000 AM EUROPE/WARSAW The three locales have different date format masks assigned. Using this method allows each user to see an appropriate date format for his locale based on his profile. If we now add NLS_DATE_FORMAT, the dates and times will use the appropriate locale language. We have this mapped in our tables to ensure that each locale is displayed correctly. CREATE OR REPLACE FUNCTION date_format_lang_func RETURN sys_refcursor IS v_date sys_refcursor; BEGIN OPEN v_date FOR SELECT locale.locale_desc "Locale Description", TO_CHAR (users.registration_date, locale.DATE_FORMAT, 'NLS_DATE_LANGUAGE= ' || locale_desc ) "Registration Date" FROM users, locale WHERE users.locale_id = locale.locale_id;   RETURN v_date; END date_format_lang_func; / Execute the function as follows: variable v_format refcursor CALL date_format_lang_func( ) INTO :v_format; PRINT v_format This prints the following: Locale Description Registration Date ----------------------- ------------------ English 01-JAN-2005 11:34:21.000000 AM US/MOUNTAIN Japanese 2005/01/01 11:34:21.000000 JAPAN German 01 Januar 05 11:34:21.000000 AM EUROPE/WARSAW The same data is stored in the USERS table, but the time is displayed in locale-specific format. You can modify the function in the same way to use the time zone and timestamp functions to distinguish between time zones for various locales. NLS_DATE_LANGUAGE is customized for each territory, so AM is in Japanese for the Japanese locale, and the month for the German locale is displayed in German. We can extend our function to include the session time zone either by converting the value to the TIMESTAMP WITH TIME ZONE datatype, or by converting the value to our session's local time zone with TIMESTAMP WITH LOCAL TIME ZONE. We do this with the CAST function (described inChapter 7), which will change the datatype of the value stored in our table. CREATE OR REPLACE FUNCTION date_ltz_lang_func RETURN sys_refcursor IS v_date sys_refcursor; BEGIN OPEN v_date FOR SELECT locale.locale_desc, TO_CHAR (CAST (users.registration_date AS TIMESTAMP WITH LOCAL TIME ZONE ), locale.DATE_FORMAT, 'NLS_DATE_LANGUAGE= ' || locale_desc ) "Registration Date" FROM users, locale WHERE users.locale_id = locale.locale_id;   RETURN v_date; END date_ltz_lang_func; / The function is executed by doing the following: variable v_format refcursor CALL date_ltz_lang_func( ) INTO :v_format; PRINT v_format The registration dates are returned as follows: Locale Description Registration Date ----------------------- ------------------ English 01-JAN-2005 11:34:21.000000 AM -07:00 Japanese 2004/12/31 07:34:21.000000 -07:00 German 01 Januar 05 03:34:21.000000 AM -07:00 There is a lot going on here: Date/time language is converted to the locale-specific terms. Formatting is locale-specific. We use CAST to convert the values stored as TIMESTAMP WITH TIMEZONE to TIMESTAMP WITH LOCAL TIMEZONE. The displayed time is relative to our session's time zone, which is U.S./Mountain in this example, or -07:00. Many of our examples thus far have shown the time zone as a UTC offset. This is not necessarily the easiest display for a user to understand. Oracle maintains a list of region names and abbreviations that can be substituted by modifying the format mask. In fact, the three records we have been working with were inserted using these region names rather than the UTC offset. For a complete list of time zones, query the V$TIMEZONE_NAMES view. Examine the INSERT statements into the USERS table in the g11n schema for more examples using region names. I want to discuss one more NLS parameter related to date/time. Our times are inserted into the table using the Gregorian calendar, which is the value for NLS_CALENDAR on our test system. Not all locales use the same calendar, however, and no amount of formatting will adjust the base calendar. With NLS_CALENDAR, we can change our default calendar from Gregorian to a number of other seeded calendarsJapanese Imperial for example. A simple SELECT of SYSDATE after setting the session results in the following: ALTER SESSION SET NLS_CALENDAR = 'JAPANESE IMPERIAL'; ALTER SESSION SET NLS_DATE_FORMAT = 'E RR-MM-DD'; After altering the session, run the following SELECT: SELECT sysdate FROM dual; The SELECT shows the modified SYSDATE: SYSDATE --------- H 17-02-08 Default values are controlled by your NLS settings. If you have a primary locale you are working with, you may find that setting your NLS parameters for your database is a much easier approach than explicitly stating them in your application. For applications in which these settings need to be dynamic, however, I recommend that you include NLS settings as part of your user/locale settings and store them with your application. This allows your code to function in any locale simply by setting a user's profile correctly.