This is a summary of the time formats supported by the TD_TIME_FORMAT_UDF udf.
Formats
Syntax | Date or Time Component | Presentation | Examples |
|---|---|---|---|
G | Era designator | Text | AD |
yyyy | Year | Year | 1996 |
yy | Year | Year (2 digits) | 96 |
MMMM | Month in year | Month long name | July |
MMM | Month in year | Month short name | Jul |
MM, M | Month in year | Month number | 07 |
ww, w | Week in year | Number | 06 |
DDD, DD, D | Day in year | Number | 189 |
dd, d | Day in month | Number | 10 |
EEEE | Day in week | Text | Tuesday |
E, EEE | Day in week | Text (short form) | Tue |
a | Am/pm marker | Text | PM |
HH, H | Hour in day (0-23) | Number | 00 |
kk, k | Hour in day (1-24) | Number | 24 |
KK, K | Hour in AM/PM (0-11) | Number | 00 |
hh, h | Hour in AM/PM (1-12) | Number | 12 |
mm, m | Minute in hour | Number | 30 |
ss, s | Second in minute | Number | 55 |
SSS, SS, S | Millisecond | Number | 978 |
wyear | Week of the year | Number | TD_TIME_FORMAT(1582859260, 'wyear') TD_TIME_FORMAT(1582859260, 'wyear', 'UTC') |
zzzz | Time zone | Zone long name | Pacific Standard Time, or GMT+01:00 |
z | Time zone | Zone short name | PST, or GMT+01:00 |
Z | Time zone | Zone offset | -0800 |
u | Day number of week (1-7) | Number | 1 (for Monday) |
Example
This example formats a UNIX timestamp into a date formatted string. You can use the sample_datasets database for example to try it out:
SQL
SELECT
time,
TD_TIME_FORMAT(time,
'yyyy-MM-dd HH:mm:ss z')
FROM
www_access LIMIT 1
;
Result
2012-01-01 00: 00: 00 UTC