sql-tests/inputs/datetime-formatting.sql

0001 --- TESTS FOR DATETIME FORMATTING FUNCTIONS ---
0002
0003 create temporary view v as select col from values
0004  (timestamp '1582-06-01 11:33:33.123UTC+080000'),
0005  (timestamp '1970-01-01 00:00:00.000Europe/Paris'),
0006  (timestamp '1970-12-31 23:59:59.999Asia/Srednekolymsk'),
0007  (timestamp '1996-04-01 00:33:33.123Australia/Darwin'),
0008  (timestamp '2018-11-17 13:33:33.123Z'),
0009  (timestamp '2020-01-01 01:33:33.123Asia/Shanghai'),
0010  (timestamp '2100-01-01 01:33:33.123America/Los_Angeles') t(col);
0011
0012 select col, date_format(col, 'G GG GGG GGGG') from v;
0013
0014 select col, date_format(col, 'y yy yyy yyyy yyyyy yyyyyy yyyyyyy yyyyyyyy yyyyyyyyy yyyyyyyyyy') from v;
0015
0016 select col, date_format(col, 'q qq') from v;
0017
0018 select col, date_format(col, 'Q QQ QQQ QQQQ') from v;
0019
0020 select col, date_format(col, 'M MM MMM MMMM') from v;
0021
0022 select col, date_format(col, 'L LL') from v;
0023
0024 select col, date_format(col, 'E EE EEE EEEE') from v;
0025
0026 select col, date_format(col, 'F') from v;
0027
0028 select col, date_format(col, 'd dd') from v;
0029
0030 select col, date_format(col, 'DD') from v where col = timestamp '2100-01-01 01:33:33.123America/Los_Angeles';
0031 select col, date_format(col, 'D DDD') from v;
0032
0033 select col, date_format(col, 'H HH') from v;
0034
0035 select col, date_format(col, 'h hh') from v;
0036
0037 select col, date_format(col, 'k kk') from v;
0038
0039 select col, date_format(col, 'K KK') from v;
0040
0041 select col, date_format(col, 'm mm') from v;
0042
0043 select col, date_format(col, 's ss') from v;
0044
0045 select col, date_format(col, 'S SS SSS SSSS SSSSS SSSSSS SSSSSSS SSSSSSSS SSSSSSSSS') from v;
0046
0047 select col, date_format(col, 'a') from v;
0048
0049 select col, date_format(col, 'VV') from v;
0050
0051 select col, date_format(col, 'z zz zzz zzzz') from v;
0052
0053 select col, date_format(col, 'X XX XXX') from v;
0054 select col, date_format(col, 'XXXX XXXXX') from v;
0055
0056 select col, date_format(col, 'Z ZZ ZZZ ZZZZ ZZZZZ') from v;
0057
0058 select col, date_format(col, 'O OOOO') from v;
0059
0060 select col, date_format(col, 'x xx xxx xxxx xxxx xxxxx') from v;
0061
0062 -- optional pattern, but the results won't be optional for formatting
0063 select col, date_format(col, '[yyyy-MM-dd HH:mm:ss]') from v;
0064
0065 -- literals
0066 select col, date_format(col, "姚123'GyYqQMLwWuEFDdhHmsSaVzZxXOV'") from v;
0067 select col, date_format(col, "''") from v;
0068 select col, date_format(col, '') from v;