DATE, TIMESTAMP y mascaras de formato en Apex

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.

....

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.

...

APEX

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.

....

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.

.....

Hay ejemplos en el articulo original.

Fuentes.

Artículo:  "DATE, TIMESTAMP and format masks" Publicado en https://apex.oracle.com/ por ORACLE Application Express: tips, tricks and best practice. Consultado el 05/11/2021.

URL: https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/6381/index-en.html