Previous Next JavaScript must be enabled to correctly display this content

## Timestamp Round Functions

You can use `timestamp_ceil`, `timestamp_floor`, `timestamp_trunc`, `timestamp_round`, and `timestamp_bucket` functions to round the timestamp values.

For `timestamp_ceil`, `timestamp_floor`, `timestamp_trunc`, and `timestamp_round` functions, you must supply a `unit` as the second argument. The `unit` specifies the precision to be considered while rounding the input timestamp.

The following units are supported in either singular or plural format: `YEAR, IYEAR, QUARTER, MONTH, WEEK, IWEEK, DAY, HOUR, MINUTE, SECOND`.

You can use the `timestamp_bucket` function to round the given timestamp value to the beginning of the specified interval (bucket). The interval starts at a specified origin on the timeline.

The `timestamp_bucket` supports the following intervals in either singular or plural format: `WEEK, DAY, HOUR, MINUTE, SECOND`.

Example 1: From airline baggage tracking data, print the bag arrival date and the bag auction date for a passenger with ticket number 1762344493810, considering 90 days as the luggage retention period.

    SELECT $b.bagArrivalDate AS BagArrival,
    timestamp_ceil(timestamp_add($b.bagArrivalDate, "90 Days"), 'day') AS BagCollection
    FROM BaggageInfo bag, bag.bagInfo AS $b
    WHERE ticketNo=1762344493810

Explanation: This query shows how to nest the timestamp functions. To determine the date an unclaimed bag is retained, add 90 days to the `bagArrivalDate` using the `timestamp_add` function. The `timestamp_ceil` function rounds up the value to the beginning of the next day.

Output:

    {"BagArrival":"2019-02-01T16:13:00Z","BagCollection":"2019-05-03T00:00:00Z"}

Example 2: Print the name, flight number, and travel date for all the passengers who boarded at originating airport JFK in the month of March 2019.

    SELECT bag.fullName, $f.flightNo, $f.flightDate
    FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[0] AS $f
    WHERE $f.fltRouteSrc = "JFK" AND timestamp_floor($f.flightDate, 'MONTH') = '2019-03-01'

Explanation: You use the `timestamp_floor` function with the unit value as MONTH to round down the travel dates to the beginning of the month. You then compare the resulting timestamp value with the string "2019-03-01" to select the desired passengers. This query does not consider the passengers in transit.

This example supplies the date in an ISO-8601 formatted string, which gets implicitly CAST into a TIMESTAMP value.

To avoid the duplication of results due to multiple checked bags by a passenger, you consider only the first element of the `bagInfo` array in this query.

Output:

    {"fullName":"Kendal Biddle","flightNo":"BM127","flightDate":"2019-03-04T06:00:00Z"}
    {"fullName":"Dierdre Amador","flightNo":"BM495","flightDate":"2019-03-07T07:00:00Z"}

Example 3: From the airline baggage tracking data, print all the activities performed on the checked bags in the originating station MEL. Align the actions to one minute interval.

    SELECT $b.actionAt,
           $b.actionCode,
           timestamp_round($b.actionTime, 'MINUTE') as actionTime
    FROM baggageInfo bag, bag.bagInfo[0].flightLegs[0].actions[] AS $b
    WHERE bag.bagInfo[0].flightLegs[0].fltRouteSrc = "MEL"

Explanation: In this query, you use the `timestamp_round` function with unit as MINUTE to round the `actionTime` to the nearest minute.

To avoid the duplication of results due to multiple checked baggage by a passenger, you consider only the first element of the `bagInfo` array in this query.

Output:

    {"actionAt":"MEL","actionCode":"ONLOAD to LAX","actionTime":"2019-03-01T12:20:00Z"}
    {"actionAt":"MEL","actionCode":"BagTag Scan at MEL","actionTime":"2019-03-01T11:52:00Z"}
    {"actionAt":"MEL","actionCode":"Checkin at MEL","actionTime":"2019-03-01T11:43:00Z"}

Example 4: Fetch the statistics of the number of passengers departing from the IST airport every 12 hrs with buckets starting from January 1st, 2019. Consider data only for the month of February 2019.

    SELECT $t AS DATE,
    count($t) AS FLIGHTCOUNT
    FROM BaggageInfo bag, bag.bagInfo[0].flightLegs[] $f,
    timestamp_bucket($f.flightDate, '12 HOURS', '2019-01-01T00') $t
    WHERE $f.fltRouteSrc =any "IST" AND timestamp_floor($f.flightDate, 'MONTH') = '2019-02-01T00:00:00Z'
    GROUP BY $t
    ORDER BY $t

Explanation: To consider passengers traveling in February 2019, use the `timestamp_floor` function and round down the `flightDate` to the beginning of the month. Compare the result with the string "2019-02-01T00:00:00Z". This example supplies the date in an ISO-8601 formatted string, which gets implicitly CAST into a TIMESTAMP value.

To include the transit flights from the IST airport, use the array constructor [ ] to indicate that the `flightLegs` is an array and consider each `fltRouteSrc` array element in the search.

Use the `timsestamp_bucket` function on the `flightDate` fields with interval as 12 hours and origin as 1st of January 2019.

Output:

    {"DATE":"2019-02-02T12:00:00.000000000Z","FLIGHTCOUNT":1}
    {"DATE":"2019-02-04T00:00:00.000000000Z","FLIGHTCOUNT":1}
    {"DATE":"2019-02-04T12:00:00.000000000Z","FLIGHTCOUNT":2}
    {"DATE":"2019-02-07T12:00:00.000000000Z","FLIGHTCOUNT":1}
    {"DATE":"2019-02-11T12:00:00.000000000Z","FLIGHTCOUNT":1}
    {"DATE":"2019-02-12T00:00:00.000000000Z","FLIGHTCOUNT":2}
    {"DATE":"2019-02-12T12:00:00.000000000Z","FLIGHTCOUNT":1}