TO_TIMESTAMP function in Amazon QLDB

In Amazon QLDB, given a string that represents a timestamp, use the TO_TIMESTAMP function to convert the string to a timestamp data type. This is the inverse operation of TO_STRING.

Syntax

TO_TIMESTAMP ( string [, 'format' ] )

Arguments

string

The field name or expression of data type string that the function converts to a timestamp.

format

(Optional) The string literal that defines the format pattern of the input string, in terms of its date parts. For valid formats, see Timestamp format strings.

If this argument is omitted, the function assumes that the string is in the format of a standard Ion timestamp. This is the recommended way to parse an Ion timestamp using this function.

Zero padding is optional when using a single-character format symbol (such as y, M, d, H, h, m, s) but is required for their zero-padded variants (such as yyyy, MM, dd, HH, hh, mm, ss).

Special treatment is given to two-digit years (format symbol yy). 1900 is added to values greater than or equal to 70, and 2000 is added to values less than 70.

Month names and AM or PM indicators are case insensitive.

Return type

timestamp

Examples

TO_TIMESTAMP('2007T')                         -- `2007T`
TO_TIMESTAMP('2007-02-23T12:14:33.079-08:00') -- `2007-02-23T12:14:33.079-08:00`
TO_TIMESTAMP('2016', 'y')                     -- `2016T`
TO_TIMESTAMP('2016', 'yyyy')                  -- `2016T`
TO_TIMESTAMP('02-2016', 'MM-yyyy')            -- `2016-02T`
TO_TIMESTAMP('Feb 2016', 'MMM yyyy')          -- `2016-02T`
TO_TIMESTAMP('February 2016', 'MMMM yyyy')     -- `2016-02T`

-- Runnable statements
SELECT TO_TIMESTAMP('2007T') FROM << 0 >>              -- 2007T
SELECT TO_TIMESTAMP('02-2016', 'MM-yyyy') FROM << 0 >> -- 2016-02T

Related functions

• CAST

• DATE_ADD

• DATE_DIFF

• EXTRACT

• TO_STRING

• UTCNOW