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"}