Previous Next JavaScript must be enabled to correctly display this content

## parse_to_timestamp function

The `parse_to_timestamp` function converts a string into a timestamp, requiring the `pattern` parameter to match the format of the specified `string`.

Syntax:

    TIMESTAMP parse_to_timestamp(<string>[, pattern])

Semantics:

- string: The `string` argument takes a STRING data type of a TIMESTAMP value in the format of the specified `pattern`.

  Note:

  The string argument with the timestamp uses TimeZoneID (an identifier that represents a specific timezone). Except for UTC and GMT, use the well-defined names for the timezones instead of abbreviations (for example, PST, IST).
- 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]

  The table below displays what the `pattern` argument needs to contain to match a `string` argument containing timezone information.

  If `string` argument | Then `pattern` argument must include
  --- | ---
  ends with a 'Z' | zone symbols like, 'VV', 'X', 'zzzz'.
  ends with a full timezone name, for example, America/Los_Angeles, Europe/Paris, and so on. For more example, see List of TimeZoneID. | zone symbols like, 'VV', 'zzzz'.
  ends with GMT +/- some value | zone symbols like, 'VV', 'zzzz' or ZoneOffset 'O', 'OOOO'.

- Return Value: `TIMESTAMP(9)`
  - The function returns NULL if the `string` or `pattern` argument is NULL.

Example 12-24 Parse the streaming account's expiry date `string` for a user using a specified `pattern.`

    SELECT parse_to_timestamp(sa.account_expiry,"yyyy-MM-dd'T'hh:mm:ss.SSSSSSSSSzzzz")AS DAY
    FROM stream_acct sa
    WHERE acct_id=1

Explanation: In the query, you must supply a `pattern` that matches the format of the `string` argument, and the `parse_to_timestamp` function will convert the `account_expiry` date into a timestamp value.

Output:

    {"DAY":"2023-10-18T00:00:00.000000000Z"}

Example 12-25 Parse the given `string` with the specified `pattern`, which includes a zone symbol, into a timestamp.

    SELECT parse_to_timestamp('03/14/23 09:04:01 America/Los_Angeles', "MM/dd/yy HH:mm:ss VV") AS TIMESTAMP
    FROM BaggageInfo
    WHERE ticketNo=1762390789239

Explanation: In this query, use the zone symbol, 'VV', for any TimeZoneID in the `string` argument. Unless wrapped in a format_timestamp function, the output timestamp will be displayed relative to UTC.

Output:

    {"TIMESTAMP":"2023-03-14T16:04:01.000000000Z"}

Example 12-26 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"}