sql-tests/inputs/extract.sql

0001 CREATE TEMPORARY VIEW t AS select '2011-05-06 07:08:09.1234567' as c, interval 10 year 20 month 30 day 40 hour 50 minute 6.7890 second as i;
0002
0003 select extract(year from c), extract(year from i) from t;
0004 select extract(y from c), extract(y from i) from t;
0005 select extract(years from c), extract(years from i) from t;
0006 select extract(yr from c), extract(yr from i) from t;
0007 select extract(yrs from c), extract(yrs from i) from t;
0008
0009 select extract(yearofweek from c) from t;
0010
0011 select extract(quarter from c) from t;
0012 select extract(qtr from c) from t;
0013
0014 select extract(month from c), extract(month from i) from t;
0015 select extract(mon from c), extract(mon from i) from t;
0016 select extract(mons from c), extract(mons from i) from t;
0017 select extract(months from c), extract(months from i) from t;
0018
0019 select extract(week from c) from t;
0020 select extract(w from c) from t;
0021 select extract(weeks from c) from t;
0022
0023 select extract(day from c), extract(day from i) from t;
0024 select extract(d from c), extract(d from i) from t;
0025 select extract(days from c), extract(days from i) from t;
0026
0027 select extract(dayofweek from c) from t;
0028 select extract(dow from c) from t;
0029
0030 select extract(dayofweek_iso from c) from t;
0031 select extract(dow_iso from c) from t;
0032
0033 select extract(doy from c) from t;
0034
0035 select extract(hour from c), extract(hour from i) from t;
0036 select extract(h from c), extract(h from i) from t;
0037 select extract(hours from c), extract(hours from i) from t;
0038 select extract(hr from c), extract(hr from i) from t;
0039 select extract(hrs from c), extract(hrs from i) from t;
0040
0041 select extract(minute from c), extract(minute from i) from t;
0042 select extract(m from c), extract(m from i) from t;
0043 select extract(min from c), extract(min from i) from t;
0044 select extract(mins from c), extract(mins from i) from t;
0045 select extract(minutes from c), extract(minutes from i) from t;
0046
0047 select extract(second from c), extract(second from i) from t;
0048 select extract(s from c), extract(s from i) from t;
0049 select extract(sec from c), extract(sec from i) from t;
0050 select extract(seconds from c), extract(seconds from i) from t;
0051 select extract(secs from c), extract(secs from i) from t;
0052
0053 select extract(not_supported from c) from t;
0054 select extract(not_supported from i) from t;
0055
0056 select date_part('year', c), date_part('year', i) from t;
0057 select date_part('y', c), date_part('y', i) from t;
0058 select date_part('years', c), date_part('years', i) from t;
0059 select date_part('yr', c), date_part('yr', i) from t;
0060 select date_part('yrs', c), date_part('yrs', i) from t;
0061
0062 select date_part('yearofweek', c) from t;
0063
0064 select date_part('quarter', c) from t;
0065 select date_part('qtr', c) from t;
0066
0067 select date_part('month', c), date_part('month', i) from t;
0068 select date_part('mon', c), date_part('mon', i) from t;
0069 select date_part('mons', c), date_part('mons', i) from t;
0070 select date_part('months', c), date_part('months', i) from t;
0071
0072 select date_part('week', c) from t;
0073 select date_part('w', c) from t;
0074 select date_part('weeks', c) from t;
0075
0076 select date_part('day', c), date_part('day', i) from t;
0077 select date_part('d', c), date_part('d', i) from t;
0078 select date_part('days', c), date_part('days', i) from t;
0079
0080 select date_part('dayofweek', c) from t;
0081 select date_part('dow', c) from t;
0082
0083 select date_part('dayofweek_iso', c) from t;
0084 select date_part('dow_iso', c) from t;
0085
0086 select date_part('doy', c) from t;
0087
0088 select date_part('hour', c), date_part('hour', i) from t;
0089 select date_part('h', c), date_part('h', i) from t;
0090 select date_part('hours', c), date_part('hours', i) from t;
0091 select date_part('hr', c), date_part('hr', i) from t;
0092 select date_part('hrs', c), date_part('hrs', i) from t;
0093
0094 select date_part('minute', c), date_part('minute', i) from t;
0095 select date_part('m', c), date_part('m', i) from t;
0096 select date_part('min', c), date_part('min', i) from t;
0097 select date_part('mins', c), date_part('mins', i) from t;
0098 select date_part('minutes', c), date_part('minutes', i) from t;
0099
0100 select date_part('second', c), date_part('second', i) from t;
0101 select date_part('s', c), date_part('s', i) from t;
0102 select date_part('sec', c), date_part('sec', i) from t;
0103 select date_part('seconds', c), date_part('seconds', i) from t;
0104 select date_part('secs', c), date_part('secs', i) from t;
0105
0106 select date_part('not_supported', c) from t;
0107 select date_part(c, c) from t;
0108 select date_part(null, c) from t;
0109
0110 select date_part(i, i) from t;
0111 select date_part(null, i) from t;
0112
0113 -- In SPARK-31476, we've supported extract('field', source), too
0114 select extract('year', c) from t;
0115 select extract('quarter', c) from t;
0116 select extract('month', c) from t;
0117 select extract('week', c) from t;
0118 select extract('day', c) from t;
0119 select extract('days', c) from t;
0120 select extract('dayofweek', c) from t;
0121 select extract('dow', c) from t;
0122 select extract('doy', c) from t;
0123 select extract('hour', c) from t;
0124 select extract('minute', c) from t;
0125 select extract('second', c) from t;