format_timestamp function

The format_timestamp function converts a timestamp into a string according to the specified pattern and the timezone.

Syntax:
STRING format_timestamp(<timestamp>,[pattern [, timezone])
Semantics:
  • timestamp: The timestamp argument takes a TIMESTAMP value or a value that can be cast to a TIMESTAMP type.
  • pattern: The pattern argument is optional and takes STRING data type as an input. It supports all pattern symbols in Java DateTimeFormatter class, except the timezone symbols 'z', 'zz', 'zzz', and 'v'. For more details on which timezone symbols are supported, see the table below:
    Symbol Meaning Presentation Example
    V time-zone ID zone-id America/Los_Angeles; Z; -08:30
    O localized zone-offset Offset-O GMT+8; GMT+08:00; UTC-08:00
    X zone-offset 'Z' for zero offset-X Z; -08; -0830; -08:30; -083015; -08:30:15
    x zone-offset offset-x +0000; -08; -0830; -08:30; -083015; -08:30:15
    Z zone-offset offset-Z +0000; -0800; -08:00

    Note:

    The default pattern is ISO-8601 format: yyyy-MM-dd'T'HH:mm:ss[.S..S].
  • timezone: The timezone argument is optional and takes STRING data type as an input.

    The timezone argument uses TimeZoneID (an identifier that represents a specific timezone). For example, use well-defined name such as "Asia/Calcutta", or a custom ID such as "GMT-08:00". For more examples, see List of TimeZoneID. Default is UTC.

    Note:

    Except for UTC and GMT, use the well-defined names for the timezones instead of abbreviations (for example, PST, IST).
  • Return Value: STRING

    The function returns NULL in the following cases:

    • If the timestamp, pattern, or timezone argument is set to NULL.
    • If the input timestamp is not castable to TIMESTAMP type.

Example 12-22 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 12-23 Display the formatted timestamp.

SELECT format_timestamp('2024-05-08T09:41:00',"dd MMM, uuuu HH:mm:ss","Asia/Calcutta") AS TIMESTAMP1
FROM BaggageInfo 
WHERE ticketNo=1762399766476

Explanation: In this query, you use the format_timestamp function to print the given timestamp in the specified pattern. During formatting, the function adjust the timestamp to the "Asia/Calcutta" timezone and displays the resulting timestamp in the "dd MMM, uuuu HH:mm:ss" pattern.

Output: 
{"TIMESTAMP1":"08 May, 2024 15:11:00"}