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>  ::=  MILLISECOND | 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
`MILLISECOND`	(0 - 999)
`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/2015 11:35:12.104. The following list shows the results of using the EXTRACT operator to extract each component of that value:

EXTRACT("TimeStamp", MILLISECOND)          = 104
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)                 = 2015
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
FROM SaleState
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
FROM SaleState
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 ClaimsWithAge