EXTRACT()

Documentation

VoltDB Home » Documentation » Using VoltDB

EXTRACT()

EXTRACT() — Returns the value of a selected portion of a timestamp or date.

Synopsis

EXTRACT( selection-keyword FROM timestamp-or-date )

EXTRACT( selection-keyword, timestamp-or-date )

Description

The EXTRACT() function returns the value of the selected portion of a timestamp or date. Both timestamp and date expressions are allowable input for date selections. However, only timestamp values are valid for selecting the HOUR, MINUTE, or SECOND portions of a time value. 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

KeywordDatatypeDescription
YEARINTEGERThe year as a numeric value.
QUARTERTINYINTThe quarter of the year as a single numeric value between 1 and 4.
MONTHTINYINTThe month of the year as a numeric value between 1 and 12.
DAYTINYINTThe day of the month as a numeric value between 1 and 31.
DAY_OF_MONTHTINYINTThe day of the month as a numeric value between 1 and 31 (same as DAY).
DAY_OF_WEEKTINYINTThe day of the week as a numeric value between 1 and 7, starting with Sunday.
DAY_OF_YEARSMALLINTThe day of the year as a numeric value between 1 and 366.
WEEKTINYINTThe week of the year as a numeric value between 1 and 52.
WEEK_OF_YEARTINYINTThe week of the year as a numeric value between 1 and 52 (same as WEEK).
WEEKDAYTINYINTThe day of the week as a numeric value between 0 and 6, starting with Monday.
HOURTINYINTThe hour of the day as a numeric value between 0 and 23.
MINUTETINYINTThe minute of the hour as a numeric value between 0 and 59.
SECONDDECIMALThe whole and fractional part of the number of seconds within the minute as a floating point value between 0 and 60.

Example

The following example lists all the contacts by name and birthday, listing the birthday as three separate fields for month, day, and year.

SELECT 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;