sql-tests/inputs/datetime.sql

0001 -- date time functions
0002
0003 -- [SPARK-16836] current_date and current_timestamp literals
0004 select current_date = current_date(), current_timestamp = current_timestamp();
0005
0006 select to_date(null), to_date('2016-12-31'), to_date('2016-12-31', 'yyyy-MM-dd');
0007
0008 select to_timestamp(null), to_timestamp('2016-12-31 00:12:00'), to_timestamp('2016-12-31', 'yyyy-MM-dd');
0009
0010 select dayofweek('2007-02-03'), dayofweek('2009-07-30'), dayofweek('2017-05-27'), dayofweek(null), dayofweek('1582-10-15 13:10:15');
0011
0012 -- [SPARK-22333]: timeFunctionCall has conflicts with columnReference
0013 create temporary view ttf1 as select * from values
0014   (1, 2),
0015   (2, 3)
0016   as ttf1(current_date, current_timestamp);
0017
0018 select current_date, current_timestamp from ttf1;
0019
0020 create temporary view ttf2 as select * from values
0021   (1, 2),
0022   (2, 3)
0023   as ttf2(a, b);
0024
0025 select current_date = current_date(), current_timestamp = current_timestamp(), a, b from ttf2;
0026
0027 select a, b from ttf2 order by a, current_date;
0028
0029 select weekday('2007-02-03'), weekday('2009-07-30'), weekday('2017-05-27'), weekday(null), weekday('1582-10-15 13:10:15');
0030
0031 select year('1500-01-01'), month('1500-01-01'), dayOfYear('1500-01-01');
0032
0033
0034 select date '2019-01-01\t';
0035 select timestamp '2019-01-01\t';
0036
0037 -- time add/sub
0038 select timestamp'2011-11-11 11:11:11' + interval '2' day;
0039 select timestamp'2011-11-11 11:11:11' - interval '2' day;
0040 select date'2011-11-11 11:11:11' + interval '2' second;
0041 select date'2011-11-11 11:11:11' - interval '2' second;
0042 select '2011-11-11' - interval '2' day;
0043 select '2011-11-11 11:11:11' - interval '2' second;
0044 select '1' - interval '2' second;
0045 select 1 - interval '2' second;
0046
0047 -- subtract timestamps
0048 select date'2020-01-01' - timestamp'2019-10-06 10:11:12.345678';
0049 select timestamp'2019-10-06 10:11:12.345678' - date'2020-01-01';
0050 select timestamp'2019-10-06 10:11:12.345678' - null;
0051 select null - timestamp'2019-10-06 10:11:12.345678';
0052
0053 -- date add/sub
0054 select date_add('2011-11-11', 1Y);
0055 select date_add('2011-11-11', 1S);
0056 select date_add('2011-11-11', 1);
0057 select date_add('2011-11-11', 1L);
0058 select date_add('2011-11-11', 1.0);
0059 select date_add('2011-11-11', 1E1);
0060 select date_add('2011-11-11', '1');
0061 select date_add('2011-11-11', '1.2');
0062 select date_add(date'2011-11-11', 1);
0063 select date_add(timestamp'2011-11-11', 1);
0064 select date_sub(date'2011-11-11', 1);
0065 select date_sub(date'2011-11-11', '1');
0066 select date_sub(date'2011-11-11', '1.2');
0067 select date_sub(timestamp'2011-11-11', 1);
0068 select date_sub(null, 1);
0069 select date_sub(date'2011-11-11', null);
0070 select date'2011-11-11' + 1E1;
0071 select date'2011-11-11' + '1';
0072 select null + date '2001-09-28';
0073 select date '2001-09-28' + 7Y;
0074 select 7S + date '2001-09-28';
0075 select date '2001-10-01' - 7;
0076 select date '2001-10-01' - '7';
0077 select date '2001-09-28' + null;
0078 select date '2001-09-28' - null;
0079
0080 -- date add/sub with non-literal string column
0081 create temp view v as select '1' str;
0082 select date_add('2011-11-11', str) from v;
0083 select date_sub('2011-11-11', str) from v;
0084
0085 -- subtract dates
0086 select null - date '2019-10-06';
0087 select date '2001-10-01' - date '2001-09-28';
0088
0089 -- variable-length second fraction tests
0090 select to_timestamp('2019-10-06 10:11:12.', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]');
0091 select to_timestamp('2019-10-06 10:11:12.0', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]');
0092 select to_timestamp('2019-10-06 10:11:12.1', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]');
0093 select to_timestamp('2019-10-06 10:11:12.12', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]');
0094 select to_timestamp('2019-10-06 10:11:12.123UTC', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]');
0095 select to_timestamp('2019-10-06 10:11:12.1234', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]');
0096 select to_timestamp('2019-10-06 10:11:12.12345CST', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]');
0097 select to_timestamp('2019-10-06 10:11:12.123456PST', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]');
0098 -- second fraction exceeded max variable length
0099 select to_timestamp('2019-10-06 10:11:12.1234567PST', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]');
0100 -- special cases
0101 select to_timestamp('123456 2019-10-06 10:11:12.123456PST', 'SSSSSS yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]');
0102 select to_timestamp('223456 2019-10-06 10:11:12.123456PST', 'SSSSSS yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]');
0103 select to_timestamp('2019-10-06 10:11:12.1234', 'yyyy-MM-dd HH:mm:ss.[SSSSSS]');
0104 select to_timestamp('2019-10-06 10:11:12.123', 'yyyy-MM-dd HH:mm:ss[.SSSSSS]');
0105 select to_timestamp('2019-10-06 10:11:12', 'yyyy-MM-dd HH:mm:ss[.SSSSSS]');
0106 select to_timestamp('2019-10-06 10:11:12.12', 'yyyy-MM-dd HH:mm[:ss.SSSSSS]');
0107 select to_timestamp('2019-10-06 10:11', 'yyyy-MM-dd HH:mm[:ss.SSSSSS]');
0108 select to_timestamp("2019-10-06S10:11:12.12345", "yyyy-MM-dd'S'HH:mm:ss.SSSSSS");
0109 select to_timestamp("12.12342019-10-06S10:11", "ss.SSSSyyyy-MM-dd'S'HH:mm");
0110 select to_timestamp("12.1232019-10-06S10:11", "ss.SSSSyyyy-MM-dd'S'HH:mm");
0111 select to_timestamp("12.1232019-10-06S10:11", "ss.SSSSyy-MM-dd'S'HH:mm");
0112 select to_timestamp("12.1234019-10-06S10:11", "ss.SSSSy-MM-dd'S'HH:mm");
0113
0114 select to_timestamp("2019-10-06S", "yyyy-MM-dd'S'");
0115 select to_timestamp("S2019-10-06", "'S'yyyy-MM-dd");
0116
0117 select to_timestamp("2019-10-06T10:11:12'12", "yyyy-MM-dd'T'HH:mm:ss''SSSS"); -- middle
0118 select to_timestamp("2019-10-06T10:11:12'", "yyyy-MM-dd'T'HH:mm:ss''"); -- tail
0119 select to_timestamp("'2019-10-06T10:11:12", "''yyyy-MM-dd'T'HH:mm:ss"); -- head
0120 select to_timestamp("P2019-10-06T10:11:12", "'P'yyyy-MM-dd'T'HH:mm:ss"); -- head but as single quote
0121
0122 -- missing fields
0123 select to_timestamp("16", "dd");
0124 select to_timestamp("02-29", "MM-dd");
0125 select to_date("16", "dd");
0126 select to_date("02-29", "MM-dd");
0127 select to_timestamp("2019 40", "yyyy mm");
0128 select to_timestamp("2019 10:10:10", "yyyy hh:mm:ss");
0129
0130 -- Unsupported narrow text style
0131 select date_format(date '2020-05-23', 'GGGGG');
0132 select date_format(date '2020-05-23', 'MMMMM');
0133 select date_format(date '2020-05-23', 'LLLLL');
0134 select date_format(timestamp '2020-05-23', 'EEEEE');
0135 select date_format(timestamp '2020-05-23', 'uuuuu');
0136 select date_format('2020-05-23', 'QQQQQ');
0137 select date_format('2020-05-23', 'qqqqq');
0138 select to_timestamp('2019-10-06 A', 'yyyy-MM-dd GGGGG');
0139 select to_timestamp('22 05 2020 Friday', 'dd MM yyyy EEEEEE');
0140 select to_timestamp('22 05 2020 Friday', 'dd MM yyyy EEEEE');
0141 select unix_timestamp('22 05 2020 Friday', 'dd MM yyyy EEEEE');
0142 select from_unixtime(12345, 'MMMMM');
0143 select from_unixtime(54321, 'QQQQQ');
0144 select from_unixtime(23456, 'aaaaa');
0145 select from_json('{"time":"26/October/2015"}', 'time Timestamp', map('timestampFormat', 'dd/MMMMM/yyyy'));
0146 select from_json('{"date":"26/October/2015"}', 'date Date', map('dateFormat', 'dd/MMMMM/yyyy'));
0147 select from_csv('26/October/2015', 'time Timestamp', map('timestampFormat', 'dd/MMMMM/yyyy'));
0148 select from_csv('26/October/2015', 'date Date', map('dateFormat', 'dd/MMMMM/yyyy'));
0149
0150 select from_unixtime(1, 'yyyyyyyyyyy-MM-dd');
0151 select date_format(timestamp '2018-11-17 13:33:33', 'yyyyyyyyyy-MM-dd HH:mm:ss');
0152 select date_format(date '2018-11-17', 'yyyyyyyyyyy-MM-dd');