對這文章發表回應

Developers are working with DATE or TIMESTAMP data types more or less all the time; they're being used in almost all APEX applications. This posting contains some information about the differences between DATE and TIMESTAMP and about format masks and how they work in Application Express.

DATE or TIMESTAMP?

Actually, there is not one, not two but four data types for datetime values in the Oracle database:

• DATE is the oldest and most widely used data type. Though the name is "DATE", it stores also information about the time. Internally, DATE stores year, month, day, hour, minute and second as explicit values. To get the current timestamp as an instance of DATE, use the SYSDATE SQL function.
  

  
  SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
  Session altered.
  SQL> select sysdate, dump(sysdate) as date_bytes from dual;
  SYSDATE DATE_BYTES
  -------------------- -----------------------------------
  2017-11-23 23:41:08 Typ=13 Len=8: 225,7,11,23,23,41,8,0
  

  
  
• TIMESTAMP extends DATE by fractional seconds. Internally, time zone information is also contained, but in order to work with time zones, one of the other two data types, TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE must be used. To get the current timestamp as a TIMESTAMP instance, use the LOCALTIMESTAMP SQL function. As the DUMP result below shows, TIMESTAMP stores year, month, day, hour, minute and second similar to DATE. The other bytes are there for fractional seconds and time zone information. TIMESTAMP instances consume more space on disk than DATE instances, which is natural - they contain more information.
  

  
  SQL> alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF6';
  Session altered.
  SQL> select localtimestamp, dump(localtimestamp) ts_bytes from dual;
  LOCALTIMESTAMP TS_BYTES
  --------------------------- ---------------------------------------------------------------------
  2017-11-24 08:45:41.434175 Typ=187 Len=20: 225,7,11,24,8,45,41,0,24,252,224,25,1,0,3,0,127,1,0,0
  

  
  
• TIMESTAMP WITH TIME ZONE allows to explicitly work with time zone information. A time zone can be used to create a TIMESTAMP WITH TIME ZONE instance and it is explicitly contained in the output (use the right format mask). When the output of a TIMESTAMP WITH TIME ZONE value does not contain a time zone, you have incomplete data.
  Oracle does not implicitly convert instances of this data type between time zones; this can be done explicitly with the AT TIME ZONE clause. To get the current time as a TIMESTAMP WITH TIME ZONE instance, we can use two functions: SYSTIMESTAMP returns the current time in the database time zone and CURRENT_TIMESTAMP returns it in the current session time zone. The following example uses SYSTIMESTAMP.
  

  
  SQL> alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FF6 TZR';
  Session altered.
  SQL> select systimestamp as ts_bytes from dual;
  SYSTIMESTAMP
  ----------------------------------
  2017-11-23 23:57:04.609608 -08:00
  

  
  
• TIMESTAMP WITH LOCAL TIME ZONE does not expose the time zone information (to the application, it looks like a TIMESTAMP), but it uses tome zones implicitly: An instance is created without time zone information, the session time zone is assumed. Stored instances are normalized to the database time zone. When the data is retrieved, users see it (again) in the session time zone.
  The following example illustrates how TIMESTAMP WITH LOCAL TIME ZONE works: First, a table is created. Then the current time is retrieved and stored into the table ( SYSTIMESTAMP returns TIMESTAMP WITH TIME ZONE, but this is automatically converted to TIMESTAMP WITH LOCAL TIME ZONE ). When the table is then being selected, the returned data changes with the session time zone.
  

  
  SQL> create table mytimestamps( ts timestamp with local time zone );
  Table created.
  SQL> insert into mytimestamps values ( systimestamp );
  1 row created.
  SQL> alter session set time_zone='Europe/Berlin';
  Session altered.
  SQL> select * from mytimestamps;
  TS
  ---------------------------------------------------------------------------
  2017-11-24 12:55:39.761283
  SQL> alter session set time_zone='EST';
  Session altered.
  SQL> select * from mytimestamps;
  TS
  ---------------------------------------------------------------------------
  2017-11-24 06:55:39.761283
  

  
  

Format Masks

Whether we work within an application or a tool like SQL Plus or SQL Developer; whenever we output a DATE or TIMESTAMP instance, we're converting it to VARCHAR2. We can do this explicitly using TO_CHAR or Oracle will do it implcitly. And now NLS format masks kick in - the format mask decides how a DATE is converted to a VARCHAR2 and back. The are defaults for NLS format masks at the database level, we can set them at the session level or we can pass a format mask to the individual TO_CHAR call.

Oracle provides the following session parameters to set default format masks for the different DATE or TIMESTAMP data types:

• NLS_DATE_FORMAT for DATE
• NLS_TIMESTAMP_FORMAT for TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE
• NLS_TIMESTAMP_TZ_FORMAT for TIMESTAMP WITH TIME ZONE

For explicit conversions with TO_DATE, TO_TIMESTAMP or TO_CHAR, the format mask is passed in as the second parameter. The Oracle documentation - SQL Reference contains detailed imformation about format masks elements.

SQL> select to_char( sysdate, 'DD/MM/YYYY HH24-MI-SS' ) as sysdate_format from dual;
SYSDATE_FORMAT
-------------------
24/11/2017 00-05-11
SQL> select to_date( '01/01/2000 08-00-00', 'DD/MM/YYYY HH24-MI-SS' ) as mydate from dual;
MYDATE
-------------------
2000-01-01 08:00:00

Actaully, the second example will lead to two conversions! First, the VARCHAR2 literal 01/01/2000 08-00-00 is converted to a DATE instance using an explicit format mask. Then the result is printed to the console - thus it must become a text again. So Oracle implicitly converts the DATE instance using the default format mask for the DATE data type (NLS_DATE_FORMAT). It's important to be aware of these conversions when working with DATE or TIMESTAMP values and when printing results out.

When an explicit format mask is not used in a TO_CHAR or TO_DATE call, the session default is used (which might have been set before or not). Thus different sessions can work with different format masks and a TO_CHAR call might work in one session and not in another one. In many cases it is best to use explicit format masks with TO_CHAR, TO_DATE or TO_TIMESTAMP.

SQL> select DATE'2017-08-01' from dual;
DATE'2017-08-01'
-------------------
01.08.2017 00:00:00

We have seen that DATE instances contain a time component as well. For many purposes, we have DATE columns in tables which are populated with SYSDATE values (in a trigger, PL/SQL procedure or directly in the SQL INSERT statement). When the session format mask does not contain a time component, that can lead to confusing behavior ...

SQL> alter session set nls_date_format='DD-MON-RR';
Session altered.
SQL> create table mydates (datecol date);
SQL> insert into mydates (datecol) values (sysdate);
1 row inserted.
SQL> select * from mydates;
DATUM
---------
24-NOV-17
1 row selected.
SQL> select * from mydates where datecol = to_date('24-NOV-17');
no rows selected.
SQL> select * from mydates where datecol = to_date('24-NOV-17', 'DD-MON-RR');
no rows selected.

Changing the date format masks clears everything up ...

SQL> alter session set nls_date_format='DD-MON-RR HH24.MI.SS';
Session altered.
SQL> select * from mydates;
DATUM
------------------
24-NOV-17 00.16.19
1 row selected.
SQL> select to_date('24-NOV-17', 'DD-MON-RR') from dual;
TO_DATE('24-NOV-17','DD-MON-RR')
--------------------------------
24-NOV-17 00.00.00

Now it's abvious - the two values are different and the query above cannot return rows. So, when only the date portion is needed within the application, it's good practive to consistently set the time component to midnight. For the current day, this is easy by usin the TRUNC SQL function together with SYSDATE.

SQL> select sysdate, trunc(sysdate) from dual;
SYSDATE TRUNC(SYSDATE)
------------------- ----------------------
24-NOV-17 02.36.30 24-NOV-17 00.00.00

And Application Express ...?

The above paragraphs describe general database behavior - this is true for all applications working with the Oracle database. So, this all applies to Application Express as well. Application Express just contains some declarative settings in order to set the format masks and a bit of additional functionality.

For every application, we can set the application default format masks. For this, navigate to Shared Components then to Application Definition Attributes and then to Globalization.

Set DATE or TIMESTAMP format masks for an APEX application

The format masks for Application Date Format, Application Timestamp Format and Application Timestamp Time Zone Format
are used by Application Express in order to set the NLS session parameters NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT for this application. If nothing is provided, the Oracle database defaults will apply. The Application Date Time Format is only used in specific APEX components like the Calendar.

So, whenever DATE or TIMESTAMP without an explicit format mask is used in the APEX application, these attributes control their conversion to text and back. But APEX allows to set explicit format masks, for instancer in report columns or page items. These format masks supersede the default settings.

There is one special format mask which is only available in Application Express: SINCE. It's being used within the application builder at several places.

The SINCE format mask - in action

SINCE formats the DATE or TIMESTAMP value as text - the text indicates how much time has passed since that very timestamp. The language is determined by the application language and messages are available for all languages which the Application Builder has been translated to. Of course, the translation must be installed as well. SINCE is also available to PL/SQL programmers - APEX_UTIL.GET_SINCE can be used to format DATE or TIMESTAMP instances within a PL/SQL block or stored procedure.

Within an APEX component, SINCE is simply being used as a format mask, as illustrated below.

Use SINCE in your own APEX report

DATE and TIMESTAMP instances are also important for APEX forms. And the same rules apply here as well, since APEX maintains all session state as VARCHAR2, independent from the page items' display type.

That means, when the form is being loaded and the current row values are selected, APEX converts DATE or TIMESTAMP values to VARCHAR2 - according to the rules described above. If the developer provided an explicit format mask with the page item, it will be used. If not, the above mentioned application default format masks will be used - and if we don't even have these, database defaults apply.

An example: We have a form item of the date picker type. It's based on a DATE column in a table. We have provided an explicit format mask: DD.MM.YYYY.

Date Picker page item with a format mask

• The page is being loaded and the
  Automatic Row Fetch process selects the table row. APEX selects the DATE column using the TO_CHAR function and it uses the items' format mask: TO_CHAR(HIREDATE, 'DD.MM.YYYY'). The value for the P22_HIREDATE item in the emitted HTML is, for instance, 14.02.1982.
• The format mask is passed the the javascript library for the date picker after translation to the date picker format mask syntax (which is different to the Oracle database). The javascript code renders the date picker dialogs.
• After the user picked a date, the data picker javascript code formats the choice as text (again) and sets the item value within the browser.
• When the page is submitted, the formatted date is sent to Application Express and stored as VARCHAR2 in session state. When the Automatic Row Processing page process then inserts the row into the table, it uses a TO_DATE( {Datum}, 'DD.MM.YYYY')
  expression in provide an instance of DATE for the table column.

Again: All Application Express items are maintained as VARCHAR2 - we only have String variables in Application Express. Whenever a DATE or TIMESTAMP value is selected from a table, inserted into a table or a table is updated, conversions between VARCHAR2 and DATE or TIMESTAMP happen. For these conversions, format masks are important.

What about your own PL/SQL page (or application) processes? The same rules apply. If you use PL/SQL code to fetch DATE instances from a table into a page item, make sure to handle format masks correctly. Either rely on application defaults or use TO_CHAR and TO_DATE expressions to control the conversions between DATE and VARCHAR2.

declare
l_date date;
begin
l_date := to_date( :P22_HIREDATE, 'DD.MM.YYYY' );
:
-- processing here
end;

This concludes the article about DATE and TIMESTAMP data types and format masks. Of course, there is much more to say about working with DATE and TIMESTAMP in Application Express: Interval types, calculations with DATE and TIMESTAMP, converting between DATE and TIMESTAMP, and so on. These topics will be covered in a future blog posting - until then: have fun while working with Application Express.