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