Previous Next JavaScript must be enabled to correctly display this content

## Timestamp Extract Functions

Timestamp extract functions fetch the corresponding date, week, or the index value from a given timestamp.

Date extract functions return the corresponding year/month/day/hour/minute/second/millisecond/microsecond/nanosecond from a timestamp.

Example: 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` string function is used to concatenate the retrieved travel records to display them in the desired format on the application. You first use the 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.

Week extract functions return the corresponding week/isoweek from a timestamp.

Example: Determine the week and ISO week number from a passenger's travel date.

    SELECT
    $s.fullName,
    $s.contactPhone,
    week(CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0))) AS TravelWeek,
    isoweek(CAST($bagInfo.flightLegs[1].flightDate AS Timestamp(0))) AS ISO_TravelWeek
    FROM baggageinfo $s, $s.bagInfo[] AS $bagInfo

Explanation: You first use the CAST expression to convert the `flightDate` to a TIMESTAMP and then fetch the week and isoweek from the timestamp.

Output:

    {"fullName":"Adelaide Willard","contactPhone":"421-272-8082","TravelWeek":7,"ISO_TravelWeek":7}

    {"fullName":"Adam Phillips","contactPhone":"893-324-1064","TravelWeek":5,"ISO_TravelWeek":5}

Timestamp index extract functions return the corresponding quarter/week/month/year index from a timestamp.

Example: Find the day of the week for given timestamps.

    SELECT day_of_week("2024-06-19") AS DAYVAL1,
    day_of_week(parse_to_timestamp('06/19/24', 'MM/dd/yy')) AS DAYVAL2
    FROM BaggageInfo
    WHERE ticketNo=1762344493810

Explanation: The second timestamp in the query is in an unsupported format '06/19/24' by itself, so wrap it in the `parse_to_timestamp` function to make it valid.

Output:

    {
      "DAYVAL1" : 3,
      "DAYVAL2" : 3
    }