Previous Next JavaScript must be enabled to correctly display this content

## Extract Expressions

Syntax

    extract_expression ::= EXTRACT "(" id FROM expression ")"

Semantics

The extract expression extract a component from a timestamp. Specifically, the expression after the FROM keyword must return at most one timestamp or NULL. If the result of this expression is NULL or empty, the results of EXTRACT is also NULL or empty, respectively. Otherwise, the component specified by the id is returned. This id must be one of the following keywords:

YEAR
  Returns the year for the timestamp, in the range -6383 ~ 9999.
MONTH
  Returns the month for the timestamp, in the range 1 ~ 12.
DAY
  Returns the day of month for the timestamp, in the range 1 ~ 31.
HOUR
  Returns the hour of day for the timestamp, in the range 0 ~ 23.
MINUTE
  Returns the minute for the timestamp, in the range 0 ~ 59.
SECOND
  Returns the second for the timestamp, in the range 0 ~ 59.
MILLISECOND
  Returns the fractional second in millisecond for the timestamp, in the range 0 ~ 999.
MICROSECOND
  Returns the fractional second in microsecond for the timestamp, in the range 0 ~ 999999.
NANOSECOND
  Returns the fractional second in nanosecond for the timestamp, in the range 0 ~ 999999999.
WEEK
  Returns the week number within the year where a week starts on Sunday and the first week has a minimum of 1 day in this year, in the range 1 ~ 54.
ISOWEEK
  Returns the week number within the year based on IS0-8601, where a week starts on Monday and the first week has a minimum of 4 days in this year, in range 0 ~ 53.

There are specific built-in functions to extract each of the above components from a time stamp. For example, EXTRACT(YEAR from expr) is equivalent to year(expr). These and other built-in functions are described in Built-in Functions.

Example 6-97 Create a message that displays the bag arrival date, time, and destination airport details for a passenger from the airline baggage tracking application data

    SELECT concat("Hi ",bag.fullname,", Please collect your bags from ",$station," airport on ",$t2,"/", $t3,"/", $t1," at ",$t4,":",$t5," Hrs") AS MESSAGE
    FROM BaggageInfo bag, bag.bagInfo[0].bagArrivalDate AS $bagDate, bag.bagInfo[0].lastSeenStation AS $station,
    EXTRACT(YEAR from (CAST($bagDate AS Timestamp(0)))) $t1,
    EXTRACT(MONTH from (CAST($bagDate AS Timestamp(0)))) $t2,
    EXTRACT(DAY from (CAST($bagDate AS Timestamp(0)))) $t3,
    EXTRACT(HOUR from (CAST($bagDate AS Timestamp(0)))) $t4,
    EXTRACT(MINUTE from (CAST($bagDate AS Timestamp(0)))) $t5
    WHERE ticketNo = 1762344493810

Explanation: The `bagArrivalDate` field in the `BaggageInfo` table stores the date on which the checked bags are scheduled to arrive at the destination airport. You can use the EXTRACT expression to extract the date (day, month, and year) and time (hour and minutes) from the `bagArrivalDate` field for the specified passenger. The date is stored as a string in the JSON data in the `bagInfo` field. You first use the CAST expression to convert the `bagArrivalDate` to a timestamp and then extract the date, month, year, and so forth from the timestamp.

To display as a message, you use the concat function.

Output:

    {"MESSAGE":"Hi Adam Phillips, Please collect your bags from MEL airport on 2/1/2019 at
          16:13 Hrs"}