Previous Next JavaScript must be enabled to correctly display this content

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