Archive
Archive
EXTRACT() — Returns the value of a selected portion of a timestamp.
EXTRACT( selection-keyword FROM timestamp-expression )
EXTRACT( selection-keyword, timestamp-expression )
The EXTRACT() function returns the value of the selected portion of a timestamp. Table C.1, “Selectable Values for the EXTRACT Function” lists the supported keywords, the datatype of the value returned by the function, and a description of its contents.
Table C.1. Selectable Values for the EXTRACT Function
| Keyword | Datatype | Description |
|---|---|---|
| YEAR | INTEGER | The year as a numeric value. |
| QUARTER | TINYINT | The quarter of the year as a single numeric value between 1 and 4. |
| MONTH | TINYINT | The month of the year as a numeric value between 1 and 12. |
| DAY | TINYINT | The day of the month as a numeric value between 1 and 31. |
| DAY_OF_MONTH | TINYINT | The day of the month as a numeric value between 1 and 31 (same as DAY). |
| DAY_OF_WEEK | TINYINT | The day of the week as a numeric value between 1 and 7, starting with Sunday. |
| DAY_OF_YEAR | SMALLINT | The day of the year as a numeric value between 1 and 366. |
| WEEK | TINYINT | The week of the year as a numeric value between 1 and 52. |
| WEEK_OF_YEAR | TINYINT | The week of the year as a numeric value between 1 and 52 (same as WEEK). |
| WEEKDAY | TINYINT | The day of the week as a numeric value between 0 and 6, starting with Monday. |
| HOUR | TINYINT | The hour of the day as a numeric value between 0 and 23. |
| MINUTE | TINYINT | The minute of the hour as a numeric value between 0 and 59. |
| SECOND | DECIMAL | The whole and fractional part of the number of seconds within the minute as a floating point value between 0 and 60. |
The timestamp expression is interpreted as a VoltDB timestamp; That is, time measured in microseconds.
The following example lists all the contacts by name and birthday, listing the birthday as three separate fields for month, day, and year.
V12.x DocumentationSELECT Last_name, first_name, EXTRACT(MONTH FROM dateofbirth),
EXTRACT(DAY FROM dateofbirth), EXTRACT(YEAR FROM dateofbirth)
FROM contact_list
ORDER BY last_name, first_name;