A lovely way to group dates
WednesdayJul 16,2008
In my current Flex project I need to create an interface that will replace the email client of the users. Many of them are using Outlook, so I would like to reproduce the same look and behavior. The new advanced datagrid in Flex 3 will come in handy to achieve the task, but there is one specific behavior that I would like to reproduce, it’s the way Outlook is grouping the dates : it creates the following groups :
-
Today
-
Yesterday
-
<NAME OF DAY> - if in the same week as the current date
-
Last Week
-
Two Weeks ago
-
Last Month
-
Older
It’s also easily expendable to the Future (Tomorrow, In two days…). So I created an Oracle function which generate the correct group label for a given date.
CREATE OR REPLACE PACKAGE TRIS_UTILS AUTHID CURRENT_USER AS /******************************************************************* NAME: TRIS_UTILS PURPOSE: Utilities functions. REVISION: Ver Date Author Description ----- ------- --------------- ------------------ 1.0 15/07/2008 Cyril Hanquez Creation *******************************************************************/ /* compare a_date with sysdate and return the corresponding group label */ FUNCTION get_grouping_date_name(a_date IN DATE) RETURN VARCHAR2; END TRIS_UTILS; / CREATE OR REPLACE PACKAGE BODY TRIS_UTILS AS /******************************************************************* NAME: TRIS_UTILS PURPOSE: Utilities functions. REVISION: Ver Date Author Description ----- ------- --------------- ------------------ 1.0 15/07/2008 Cyril Hanquez Creation *******************************************************************/ FUNCTION get_grouping_date_name(a_date IN DATE) RETURN VARCHAR2 IS return_var VARCHAR2(50); BEGIN CASE /* In Oracle date1-date2 returns a number of days It's equivalent to DATEDIFF(day, a_date, getdate()) */ WHEN TRUNC(SYSDATE - a_date) = 0 THEN return_var := 'Today'; /* parameter 'IW' is returning the Week of year but we must also verify that we are in a valid period */ WHEN ((TO_CHAR(SYSDATE,'IW')-TO_CHAR(a_date,'IW')) = 0 AND /* between -6 AND +6 would be enough 7 days a week -1 because today returns 0 equivalent to ABS(TRUNC(SYSDATE - a_date)) <= 8 */ TRUNC(SYSDATE - a_date) BETWEEN -8 AND 8) THEN CASE WHEN TRUNC(SYSDATE - a_date) = 1 THEN return_var := 'Yesterday'; WHEN TRUNC(SYSDATE - a_date) = -1 THEN return_var := 'Tomorrow'; WHEN ABS(SYSDATE - a_date) > 1 /* We are returning the Name of day here */ THEN return_var := INITCAP(TO_CHAR(a_date,'DAY')); END CASE; WHEN ((TO_CHAR(SYSDATE,'IW')-TO_CHAR(a_date,'IW')) = 1 AND /* <=13 would be enough */ ABS(SYSDATE - a_date) <= 15) THEN return_var := 'Last Week'; WHEN ((TO_CHAR(SYSDATE,'IW')-TO_CHAR(a_date,'IW')) = -1 AND /* <=13 would be enough */ ABS(SYSDATE - a_date) <= 15) THEN return_var := 'Next Week'; WHEN ((TO_CHAR(SYSDATE,'IW')-TO_CHAR(a_date,'IW')) = 2 AND /* <=20 would be enough */ ABS(SYSDATE - a_date) <= 22) THEN return_var := 'Two Weeks Ago'; WHEN ((TO_CHAR(SYSDATE,'IW')-TO_CHAR(a_date,'IW')) = -2 AND /* <=20 would be enough */ ABS(SYSDATE - a_date) <= 22) THEN return_var := 'In Two Weeks'; WHEN ((TO_CHAR(SYSDATE,'IW')-TO_CHAR(a_date,'IW')) = 3 AND /* <=27 would be enough */ ABS(SYSDATE - a_date) <= 29) THEN return_var := 'Three Weeks Ago' ; WHEN ((TO_CHAR(SYSDATE,'IW')-TO_CHAR(a_date,'IW')) = -3 AND /* <=27 would be enough */ ABS(SYSDATE - a_date) <= 29) THEN return_var := 'In Three Weeks'; WHEN ((TO_CHAR(SYSDATE,'MM')-TO_CHAR(a_date,'MM') = 1 AND /* current month + 1 month so a maximum of 62 days */ ABS(SYSDATE - a_date) <= 62)) THEN return_var := 'Last Month' ; WHEN ((TO_CHAR(SYSDATE,'MM')-TO_CHAR(a_date,'MM') = -1 AND /* current month + 1 month so a maximum of 62 days */ ABS(SYSDATE - a_date) <= 62)) THEN return_var := 'Next Month'; /* in other cases just check if the date is in the future... */ WHEN SIGN(SYSDATE - a_date) = -1 THEN return_var := 'Further'; /* ...or in the past */ WHEN SIGN(SYSDATE - a_date) = 1 THEN return_var := 'Older'; /* if the date is null, do something */ ELSE return_var := '---'; END CASE; RETURN return_var; END; END TRIS_UTILS; /
Now that we have our group labels generated, we should take care about the sorting. Its quite easy in fact, just use the DATEDIFF. To test it just use the following SQL :
SELECT <your_date> AS MY_DATE, TRIS_UTILS.(get_grouping_date_name,<your_date>) AS GROUP LABEL, (SYSDATE-<your_date>) AS DATE_SORT FROM DUAL
In a next blog post I will share the Flex code that I wrote to generate the “Outlook like” interface. Feel free to comment and suggest any enhancement 
Posted in Database, Oracle, User Interface
