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.
<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. |
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
RETURN Quarters AS SELECT SUM(Amount) AS Total TRUNC(TimeStamp, QUARTER) AS Qtr WHERE EXTRACT(TimeStamp,DAY_OF_WEEK) = 2 GROUP BY Qtr
DEFINE ClaimsWithAge AS SELECT 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