Date extract functions

These functions return the corresponding year/month/day/hour/minute/second/millisecond/microsecond/nanosecond from a timestamp. The timestamp must be supplied as an argument to the function.

Syntax:

INTEGER year(<timestamp>)

INTEGER month(<timestamp>)

INTEGER day(<timestamp>)

INTEGER hour(<timestamp>)

INTEGER minute(<timestamp>)

INTEGER second(<timestamp>)

INTEGER millisecond(<timestamp>)

INTEGER microsecond(<timestamp>)

INTEGER nanosecond(<timestamp>)

Semantics:

• timestamp: These functions expects a timestamp as the input argument.

• Return Value: INTEGER

  Table 3 Timestamp date extract functions

  Function	Return Value
  year	Returns the year for the given timestamp. The returned value is in the range -6383 to 9999.
  month	Returns the month for the given 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.

Note: If the argument is NULL or empty, the result is also NULL or empty.

Example 1 - Get consolidated travel details of the passengers from airline baggage tracking data

In an airline application, it is beneficial to the passengers to have a quick summary of their upcoming travel details. You can use miscellaneous time functions to get consolidated travel details of the passengers from the BaggageInfo table.

SELECT DISTINCT
$s.fullName,
$s.bagInfo[].flightLegs[].flightNo AS flightnumbers,
$s.bagInfo[].flightLegs[].fltRouteSrc AS From,
concat ($t1,":", $t2,":", $t3) AS Traveldate
FROM baggageinfo $s, $s.bagInfo[].flightLegs[].flightDate AS $bagInfo,
day(CAST($bagInfo AS Timestamp(0))) $t1,
month(CAST($bagInfo AS Timestamp(0))) $t2,
year(CAST($bagInfo AS Timestamp(0))) $t3

Explanation:

You can use the time functions to retrieve the travel date, month, and year. The concat function is used to concatenate the retrieved travel records to display them in the desired format on the application. You first use CAST expression to convert the flightDates to a TIMESTAMP and then fetch the date, month, and year details from the timestamp.

Output:

{"fullName":"Adam Phillips","flightnumbers":["BM604","BM667"],"From":["MIA","LAX"],"Traveldate":"1:2:2019"}

{"fullName":"Adelaide Willard","flightnumbers":["BM79","BM907"],"From":["GRU","ORD"],"Traveldate":"15:2:2019"}

The query returns the flight details which can serve as a quick look-up for the passengers.