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 1 - 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 2 - 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 3 - 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"}