Using EXTRACT to extract a portion of a dateTime value

The EXTRACT function extracts a portion of a dateTime value, such as the day of the week or month of the year. This can be useful in situations where the data must be filtered or grouped by a slice of its timestamps, for example to compute the total sales that occurred on any Monday.

The syntax of the EXTRACT function is: 
<ExtractExpr>   ::=  EXTRACT(<expr>,<DateTimeUnit>)
<DateTimeUnit>  ::=  SECOND | MINUTE | HOUR | DAY_OF_WEEK |
                     DAY_OF_MONTH | DAY_OF_YEAR | DATE | WEEK |
                     MONTH | QUARTER | YEAR | JULIAN_DAY_NUMBER
Date Time Unit Range of Returned Values Notes
SECOND (0 - 59)
MINUTE (0 - 59)
HOUR (0 - 23)
DAY_OF_WEEK (1 - 7) Returns the rank of the day within the week, where Sunday is 1.
DAY_OF_MONTH (DATE) (1 - 31)
DAY_OF_YEAR (1 - 366)
WEEK (1 - 53) Returns the rank of the week in the year, where the first week starts on the first day of the year.
MONTH (1 - 12)
QUARTER (1 - 4) Quarters start in January, April, July, and October.
YEAR (-9999 - 9999)
JULIAN_DAY_NUMBER (0 - 5373484) Returns the integral number of whole days between the timestamp and midnight, 24 November -4713.
For example, the dateTime attribute TimeStamp has a value representing 10/13/2011 11:35:12.000. The following list shows the results of using the EXTRACT operator to extract each component of that value: 
EXTRACT("TimeStamp", SECOND)               = 12
EXTRACT("TimeStamp", MINUTE)               = 35
EXTRACT("TimeStamp", HOUR)                 = 11
EXTRACT("TimeStamp", DATE)                 = 13
EXTRACT("TimeStamp", WEEK)                 = 41   
EXTRACT("TimeStamp", MONTH)                = 10
EXTRACT("TimeStamp", QUARTER)              = 4   
EXTRACT("TimeStamp", YEAR)                 = 2011
EXTRACT("TimeStamp", DAY_OF_WEEK)          = 5    
EXTRACT("TimeStamp", DAY_OF_MONTH)         = 13
EXTRACT("TimeStamp", DAY_OF_YEAR)          = 286 
EXTRACT("TimeStamp", JULIAN_DAY_NUMBER)    = 2455848
Here is a simple example of using this functionality. The following statement groups the total value of the Amount attribute by quarter, and for each quarter computes the total sales that occurred on a Monday (DAY_OF_WEEK=2): 
RETURN Quarters AS
SELECT SUM(Amount) AS Total
       ARB(TRUNC(TimeStamp, QUARTER)) AS Qtr
WHERE EXTRACT(TimeStamp,DAY_OF_WEEK) = 2
GROUP BY Qtr
The following example allows you to sort claims in buckets by age: 
DEFINE ClaimsWithAge AS
SELECT
	ARB(FLOOR((EXTRACT(TO_TZ(CURRENT_TIMESTAMP,claim_tz),JULIAN_DAY_NUMBER)-EXTRACT(TO_TZ(claim_ts,claim_tz),JULIAN_DAY_NUMBER))/7)) AS AgeInWeeks,
	COUNT(1) AS Count
GROUP BY AgeInWeeks
HAVING AgeInWeeks < 2
ORDER BY AgeInWeeks;

RETURN Result AS
SELECT
	CASE AgeInWeeks
		WHEN 0 THEN 'Past 7 Days'
		WHEN 1 THEN 'Prior 7 Days'
		       ELSE 'Other'
		END
	AS Label, Count
FROM ReviewsWithAge