Subject: CAP: Functions to manipulate time in CAP-QL

From: Patrice Lapierre <[email-masked]>
Date: 15 Feb 2002 18:09:19 -0500

The CAP-QL seems to be missing operations to manipulate 
values of type DATE, TIME and DATE-TIME.

The operations currently supported are:

(1) =, !=, <, <=, >, >=  which seems required.

(2) LIKE operator. Which does add some functionality, but was 
   designed for TEXT and doesn't seem adequate for DATE-TIME 
   operations.
    
   i.e.,
    - As shown in the proposed text, it can be used to 
      test for exact match on a portion of a DATE-TIME, but is not
      well suited for other operations (e.g. search for all VEVENTs
      that starts before 8h30).
         
    - Cannot extract information that is not directly visible
      from a DATE-TIME (e.g., WEEKDAY, timezone conversion, ...).

I propose to add new functions to CAP-QL to extract information
from a DATE-TIME value.

Each function would have the following format:

   FUNCTIONNAME( VALUE, TIMEZONE )

  Where VALUE must be of type DATE, TIME or DATE-TIME.
And TIMEZONE of type TZID or NULL to indicate UTC.  The type 
of the returned value is dependent on the function, but NULL
is returned when the information is not available (e.g, when 
trying to extract the hour of a value of type DATE).

Here the proposed functions:

Function name: TIME
Returned type: TIME
Function description: 
   When available returns the TIME portion of VALUE in the 
   given TIMEZONE, returns NULL if not available.

Function name: WEEKDAY
Returned type: TEXT
Function description:
   When available returns the weekday ("SU" / "MO" / "TU" / "WE" / 
   "TH" / "FR" / "SA") of VALUE in the given TIMEZONE. 
   Returns NULL if the information if not available.

Function name: WEEKNO
Type of returned value: INTEGER
Function description:
   When available returns the WEEKNO (1 to 51) of VALUE in the given 
   TIMEZONE, returns NULL otherwise.

Function name: MONTH
Returned type: INTEGER
Function description: 
   When available returns the month (1 to 12) of VALUE in the given
   TIMEZONE, returns NULL otherwise.

Function name: YEAR
Return type: INTEGER
Function description:
Function description:
   When available returns the year (0 to 9999) of VALUE in the given
   TIMEZONE, returns NULL otherwise.

Function name: HOUR
Return type: INTEGER
Function description:
   When available returns the hour (0 to 23) of VALUE in the given
   TIMEZONE, returns NULL otherwise.

Function name: MINUTE
Return type: INTEGER
Function description:
   When available returns the minute (0 to 59) of VALUE in the 
   given TIMEZONE, returns NULL otherwise.

Function name: SECONDS
Return type: INTEGER
Function description:
   When available returns the second (0 to 59) of VALUE in the 
   given TIMEZONE, returns NULL otherwise.

Function name: MONTHDAY
Return type: INTEGER
Function description:
   When available returns the day of the month (1 to 31) of VALUE 
   in the given TIMEZONE. Returns NULL if the information is not
   available.

Function name: NEGMONTHDAY
Return type: INTEGER
Function description:
   When available returns the day of the month (1 to 31) starting 
   from the end. Returns NULL if not available. 

Examples:

 Query for VEVENTs starting outside office hours.

         SELECT * FROM VEVENT WHERE
          WEEKDAY(DTSTART,'US-Eastern') = 'SA' OR
          WEEKDAY(DTSTART,'US-Eastern') = 'SU' OR
          TIME(DTSTART,'US-Eastern') > '170000' OR
          TIME(DTSTART,'US-Eastern') < '080000'
          
 Query all VEVENTs that intersect with lunch time 
 (say 12h-13h) in the timezone of the starttime of 
 the event:
                
         SELECT * FROM VEVENT WHERE
          HOUR(DTSTART, PARAM(DTSTART,TZID)) <= '13' AND
          HOUR(DTEND, PARAM(DSTART,TZID)) >= '12'

--
Patrice.

---

From: Doug Royer <[email-masked]>
Date: Fri, 15 Feb 2002 19:39:56 -0700

> values of type DATE, TIME and DATE-TIME.

Sounds like a great post-CAP is RFC add on, but we don't NEED it
to get CAP out the door.