EXTRACT()

Documentation

VoltDB Home » Documentation » Using VoltDB

EXTRACT()

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

Synopsis

EXTRACT( selection-keyword FROM timestamp-expression )

EXTRACT( selection-keyword, timestamp-expression )

Description

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

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.

The timestamp expression is interpreted as a VoltDB timestamp; That is, time measured in microseconds.

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;