Previous Next JavaScript must be enabled to correctly display this content

## Timestamp index extract functions

These functions return the corresponding quarter/week/month/year index from a timestamp. The `timestamp` must be supplied as an argument to the function.

Syntax:

    INTEGER quarter(<timestamp>)

    INTEGER day_of_week(<timestamp>)

    INTEGER day_of_month(<timestamp>)

    INTEGER day_of_year(<timestamp>)

Semantics:

- timestamp: The `timestamp` argument takes a TIMESTAMP value or a value that can be cast to TIMESTAMP type. If you supply an unsupported timestamp format, the functions return an error.
- Return Value: INTEGER

  The functions adhere to the ISO-8601 calendar system.

  Table 12-5 Timestamp index extraction functions

  Function | Return Value
  --- | ---
  `quarter` | 1 to 4 1 indicates Quarter 1, and 4 indicates Quarter 4.
  `day_of_week` | 1 to 7 1 indicates the first day of the week (Monday) and 7 is the last day of the week (Sunday).
  `day_of_month` | 1 to 31 1 indicates the first day of the month and 31 is the last day of the month.
  `day_of_year` | 1 to 366 1 indicates the first day of the year and 366 is the last day of the year.

Note:

The functions return NULL in the following cases:

- If the timestamp argument is set to NULL.
- If the input `timestamp` is not castable to TIMESTAMP type.

Example 12-30 Print the quarter in which a subscriber viewed the given episode from a series

    SELECT quarter(show.acct_data.contentStreamed[1].seriesInfo[0].episodes[1].date) AS quarter
    FROM stream_acct show
    WHERE profile_name="AP"

Output:

    {"quarter":1}

Example 12-31 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
    }

Example 12-32 From the airline baggage tracking data, print the flight numbers, bag arrival date, and the day on which the checked bags arrive at MEL airport

    SELECT DISTINCT $b.flightLegs[].flightNo,
    $b.bagArrivalDate,
    day_of_week($b.bagArrivalDate) AS DAY
    FROM BaggageInfo bag, bag.bagInfo[0] AS $b
    WHERE regex_like($b.routing,".*/MEL")

Explanation: You use the `day_of_week` function to determine the day index corresponding to the timestamp value in the `bagArrivalDate` field.

To display the results for the MEL airport, use the regular expression condition to match the pattern of the destination airport with the string "MEL".

Output:

    {"flightNo":["BM604","BM667"],"bagArrivalDate":"2019-02-25T20:15:00Z","DAY":1}
    {"flightNo":["BM604","BM667"],"bagArrivalDate":"2019-02-04T10:08:00Z","DAY":1}
    {"flightNo":["BM604","BM667"],"bagArrivalDate":"2019-02-16T16:13:00Z","DAY":6}
    {"flightNo":["BM604","BM667"],"bagArrivalDate":"2019-02-01T16:13:00Z","DAY":5}

Example 12-33 From the airline baggage tracking data, print the flight numbers, departure date, and the day of the month for all the flights starting from SFO airport

    SELECT DISTINCT $f.flightNo AS FLIGHT,
    $f.flightDate AS DEPARTURE,
    day_of_month($f.flightDate) AS DAY
    FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[0] AS $f
    WHERE $f.fltRouteSrc =any "SFO"

Output:

    {"FLIGHT":"BM318","DEPARTURE":"2019-02-02T12:00:00Z","DAY":2}
    {"FLIGHT":"BM318","DEPARTURE":"2019-03-12T03:00:00Z","DAY":12}
    {"FLIGHT":"BM572","DEPARTURE":"2019-03-02T05:00:00Z","DAY":2}
    {"FLIGHT":"BM318","DEPARTURE":"2019-03-07T04:00:00Z","DAY":7}

Example 12-34 From the airline baggage tracking data, find the passengers who traveled on the 60th day of the launch of flight "BM114"

    SELECT fullName, $f.fltRouteSrc AS BOARDING,
    $f.flightNo AS FLIGHT,
    $f.flightDate AS DEPARTURE
    FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[0] AS $f
    WHERE day_of_year($f.flightDate)= 60 AND $f.flightNo =any "BM114"

Output:

    {"fullName":"Omar Harvey","BOARDING":"MEL","FLIGHT":"BM114","DEPARTURE":"2019-03-01T12:00:00Z"}