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
Impress your boss and your end-users with the SINCE format mask!
6 comments:
Can I sort on this column ascending or descending without doing anything special?
Balaji,
Yes - the sorting will happen on the underlying data value and not the display value.
Joel
Very cool. Thanks for your post on SINCE! -Reid M.
I'm trying to filter column based on minutes or seconds, but the timestamp column only allows for filtering based on days. DO you have any suggestions?
@mnikizad,
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.
Joel
The API works in v3 too BTW. It iis a bit of a pain but can be reworked for forward forecasting such as "Due in 3 days" but would be good to have an APEX_UTIL.GET_UNTIL
Post a Comment