Parsing Time Strings in OPAL¶
Time in Observability data is a complicated subject, and there are a variety of tools for solving specific problems. See Time Functions for an overview of these tools. As a rule, when Observe recognizes a datetime column data type in data, the datetime is set appropriately. However, we often will need to extract dates and times from string columns in the data payload, which is where parse_timestamp and format_time come in.
The parse_timestamp function produces a timestamp column from a formatted string. The format_time function produces a formatted string from a timestamp column.
Differences from other popular datetime formats¶
Observe uses Snowflake’s datetime formats. This table illustrates high level equivalences from other datetime formatting languages you may be familiar with. Be sure to follow the links to see details of acceptable inputs and separators, which differ between environments.
Concept |
|||||
|---|---|---|---|---|---|
4-digit year |
YYYY |
%Y |
yyyy |
yyyy or YYYY |
YYYY |
4-digit year (ISO) |
UUUU |
uuuu |
|||
2-digit year |
YY |
%y |
yy |
yy or YY or uu |
|
2-digit month |
MM |
%m |
MM |
MM |
MM |
Month name (Abbr) |
MON |
%b |
LLL or MMM |
||
Month name (Full) |
MMMM |
%B |
LLLL or MMMM |
||
2-digit day of month |
DD |
%d |
dd |
dd |
DD |
3-digit Day of year |
%j |
%j |
DDD |
||
Weekday name (Abbr) |
DY |
%a |
EEE |
||
2-digit hour (24h) |
HH24 |
%H |
HH |
kk |
HH |
2-digit hour (12h) |
HH12 |
%I |
hh |
hh |
|
AM / PM indication |
AM or PM |
%p |
tt |
aa |
|
2-digit minute |
MI |
%M |
mm |
mm |
mm |
2-digit second |
SS |
%S |
ss |
ss |
ss |
fractional seconds |
FF[0-9] |
%f |
f or F (0-7) |
S (0-3) or n |
sss |
Time zone offset |
TZH:TZM or TZHTZM or TZH |
%z |
x or X or Z |
Z[HH:mm] |