Pushdown functions
try_parse_monotonic_iso8601_timestamp parses a subset of ISO 8601
timestamps that matches the 24 character length output
of the javascript Date.toISOString() function.
Unlike other parsing functions, inputs that fail to parse return NULL
instead of error.
This allows try_parse_monotonic_iso8601_timestamp to be used with
the temporal filter pushdown feature on text timestamps.
This is particularly useful when working with
JSON sources,
or other external data sources that store timestamps as strings.
Specifically, the accepted format is YYYY-MM-DDThh:mm:ss.sssZ:
- A 4-digit positive year, left-padded with zeros followed by
- A literal
-followed by - A 2-digit month, left-padded with zeros followed by
- A literal
-followed by - A 2-digit day, left-padded with zeros followed by
- A literal
Tfollowed by - A 2-digit hour, left-padded with zeros followed by
- A literal
:followed by - A 2-digit minute, left-padded with zeros followed by
- A literal
:followed by - A 2-digit second, left-padded with zeros followed by
- A literal
. - A 3-digit millisecond, left-padded with zeros followed by
- A literal
Z, indicating the UTC time zone.
Ordinary text-to-timestamp casts will prevent a filter from being pushed down.
Replacing those casts with try_parse_monotonic_iso8601_timestamp can unblock that
optimization for your query.
Examples
SELECT try_parse_monotonic_iso8601_timestamp('2015-09-18T23:56:04.123Z') AS ts;
ts
--------
2015-09-18 23:56:04.123
SELECT try_parse_monotonic_iso8601_timestamp('nope') AS ts;
ts
--------
NULL