Turning customers into rockstars, with the Oracle Database, PL/SQL and Oracle Application Express.

Tuesday, September 14, 2010

SINCE when?

This will not be new information for long-time Oracle Application Express users, but it's worth mentioning for those new to Oracle Application Express.

In APEX page items (those using Automatic DML) and columns in SQL Reports & Interactive Reports, you have the ability to format date and timestamp values using a variety of format masks. Every report column and form page item has a format attribute. For date and timestamp columns, you can specify any valid Oracle date or timestamp format mask, with a full listing provided here. Some examples include:

MM/DD/YYYY

DD.MM.RRRR

DS FMHH24:MI:SS

DL

RRRR-MM-DD"T"hh24:mi:ss.xff

In APEX applications, you can also employ the SINCE format mask on date and timestamp columns. This is specific to APEX applications in the context of the format mask for report columns and Automatic DML page items. Instead of showing your end-users values like:

09/11/2010

you can present a value of:

3 days ago

The time period is relative to the difference between the time it is rendered and the value itself. Values can include 'seconds ago', 'minutes ago', 'hours ago', 'days ago', 'weeks ago', 'months ago', and 'years ago'. The benefit of using SINCE is:

It's time zone agnostic. Regardless of the time zone of your end user, 3 minutes ago is always 3 minutes ago.

It's locale agnostic. Some locales interpret 09/11 as September 11, other locales interpret 09/11 as November 9. Everyone interprets '3 days ago' as 3 days ago.

It's translated into the 10 languages provided for Application Express 4.0. The output will be correctly translated based upon your user's language preference.

New in Application Express 4.0:

Support for future dates and timestamps. You can now have values like '3 weeks from now', '2 days from now'.

Support for the SINCE format mask against columns of type TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.

And an API (should be documented, but I find that it's not right now) to compute this yourself:

APEX_UTIL.GET_SINCE( p_date IN DATE ) RETURN VARCHAR2

APEX_UTIL.GET_SINCE_TSWTZ( p_timestamp IN TIMESTAMP WITH TIME ZONE ) RETURN VARCHAR2

APEX_UTIL.GET_SINCE_TSWLTZ( p_ltimestamp IN TIMESTAMP WITH LOCAL TIME ZONE ) RETURN VARCHAR2

The format mask of the calendar in an Interactive Report filter is driven by the default date format. You can override this by specifying a value for the application attribute Application Date Format. You can include a time component there.

About Me

My name is Joel Kallman. I am the Director of Software Development at Oracle, and I manage the development and product management of Application Express (APEX) at Oracle. I've been at Oracle since 1996, and my VP Mike Hichwa and I created Application Express in 1999. My passion is to make customers extraordinarily successful with the Oracle Database, PL/SQL and Oracle Application Express.