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:
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:
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
Impress your boss and your end-users with the SINCE format mask!