Previous Next JavaScript must be enabled to correctly display this content

## Timestamp Format Functions

You can use `format_timestamp` and `parse_to_timestamp` functions to format timestamp values. Also, you can use the `to_last_day_of_month` function to fetch the last day of the month from a given timestamp.

Example 1: For a passenger with a specific ticket number, print the estimated arrival time on the first leg according to the `pattern` and the `timezone` entered.

    SELECT $info.estimatedArrival,
    format_timestamp($info.estimatedArrival, "MMM dd, yyyy HH:mm:ss O", "America/Vancouver") AS FormattedTimestamp
    FROM BaggageInfo bag, bag.bagInfo.flightLegs[0] AS $info
    WHERE ticketNo= 1762399766476

Explanation: In this query, you specify the `estimatedArrival` field, `pattern`, and full name of the `timezone` as arguments to the `format_timestamp` function to convert the `timestamp` string to the specified "MMM dd, yyyy HH:mm:ss" pattern.

Note:

The letter 'O' in the `pattern` argument represents the ZoneOffset, which prints the amount of time that differs from Greenwich/UTC in the resulting string.
Output:

    {"estimatedArrival":"2019-02-03T06:00:00Z","FormattedTimestamp":"Feb 02, 2019 22:00:00 GMT-8"}

Example 2: Parse the given `string` with the specified `pattern`, which includes a zone offset, into a timestamp.

    SELECT format_timestamp(parse_to_timestamp('2024/02/12 18:30:54 GMT+02:00', "yyyy/dd/MM HH:mm:ss OOOO"),"yyyy-MM-dd HH:mm:ss OOOO","GMT+02:00")AS TIMESTAMP
    FROM BaggageInfo
    WHERE ticketNo=1762390789239

Explanation: In this query, the `string` argument has a TimeZoneID, GMT+02:00, so the `pattern` argument must include a zone symbol or a ZoneOffset. When wrapped in the `format_timestamp` function, the output timestamp will display in the GMT+02:00 timezone.

Output:

    {"TIMESTAMP":"2024-12-02 18:30:54 GMT+02:00"}

Example 3: For a subscriber, print the last day of the month in which the account subscription expires.

    SELECT sa.acct_id, to_last_day_of_month(sa.account_expiry) AS lastday FROM stream_acct sa WHERE profile_name="DM"

Output:

    {"acct_id":4,"lastday":"2024-03-31T00:00:00Z"}