OSCL-LXR |
|
|
|
|||
0001 -- 0002 -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group 0003 -- 0004 -- 0005 -- DATE 0006 -- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/date.sql 0007 0008 CREATE TABLE DATE_TBL (f1 date) USING parquet; 0009 0010 -- PostgreSQL implicitly casts string literals to data with date types, but 0011 -- Spark does not support that kind of implicit casts. 0012 INSERT INTO DATE_TBL VALUES (date('1957-04-09')); 0013 INSERT INTO DATE_TBL VALUES (date('1957-06-13')); 0014 INSERT INTO DATE_TBL VALUES (date('1996-02-28')); 0015 INSERT INTO DATE_TBL VALUES (date('1996-02-29')); 0016 INSERT INTO DATE_TBL VALUES (date('1996-03-01')); 0017 INSERT INTO DATE_TBL VALUES (date('1996-03-02')); 0018 INSERT INTO DATE_TBL VALUES (date('1997-02-28')); 0019 -- [SPARK-27923] Skip invalid date: 1997-02-29 0020 -- INSERT INTO DATE_TBL VALUES ('1997-02-29')); 0021 INSERT INTO DATE_TBL VALUES (date('1997-03-01')); 0022 INSERT INTO DATE_TBL VALUES (date('1997-03-02')); 0023 INSERT INTO DATE_TBL VALUES (date('2000-04-01')); 0024 INSERT INTO DATE_TBL VALUES (date('2000-04-02')); 0025 INSERT INTO DATE_TBL VALUES (date('2000-04-03')); 0026 INSERT INTO DATE_TBL VALUES (date('2038-04-08')); 0027 INSERT INTO DATE_TBL VALUES (date('2039-04-09')); 0028 INSERT INTO DATE_TBL VALUES (date('2040-04-10')); 0029 0030 SELECT f1 AS `Fifteen` FROM DATE_TBL; 0031 0032 SELECT f1 AS `Nine` FROM DATE_TBL WHERE f1 < '2000-01-01'; 0033 0034 SELECT f1 AS `Three` FROM DATE_TBL 0035 WHERE f1 BETWEEN '2000-01-01' AND '2001-01-01'; 0036 0037 -- Skip the formats that we do not supported. Please check [SPARK-8995] for all supported formats 0038 -- 0039 -- Check all the documented input formats 0040 -- 0041 -- [SPARK-28259] Date/Time Output Styles and Date Order Conventions 0042 -- SET datestyle TO iso; -- display results in ISO 0043 0044 -- SET datestyle TO ymd; 0045 0046 -- SELECT date 'January 8, 1999'; 0047 SELECT date '1999-01-08'; 0048 SELECT date '1999-01-18'; 0049 -- SELECT date '1/8/1999'; 0050 -- SELECT date '1/18/1999'; 0051 -- SELECT date '18/1/1999'; 0052 -- SELECT date '01/02/03'; 0053 -- SELECT date '19990108'; 0054 -- SELECT date '990108'; 0055 -- SELECT date '1999.008'; 0056 -- SELECT date 'J2451187'; 0057 -- SELECT date 'January 8, 99 BC'; 0058 0059 -- SELECT date '99-Jan-08'; 0060 -- SELECT date '1999-Jan-08'; 0061 -- SELECT date '08-Jan-99'; 0062 -- SELECT date '08-Jan-1999'; 0063 -- SELECT date 'Jan-08-99'; 0064 -- SELECT date 'Jan-08-1999'; 0065 -- SELECT date '99-08-Jan'; 0066 -- SELECT date '1999-08-Jan'; 0067 0068 -- SELECT date '99 Jan 08'; 0069 SELECT date '1999 Jan 08'; 0070 -- SELECT date '08 Jan 99'; 0071 -- SELECT date '08 Jan 1999'; 0072 -- SELECT date 'Jan 08 99'; 0073 -- SELECT date 'Jan 08 1999'; 0074 -- SELECT date '99 08 Jan'; 0075 SELECT date '1999 08 Jan'; 0076 0077 -- SELECT date '99-01-08'; 0078 SELECT date '1999-01-08'; 0079 -- SELECT date '08-01-99'; 0080 -- SELECT date '08-01-1999'; 0081 -- SELECT date '01-08-99'; 0082 -- SELECT date '01-08-1999'; 0083 -- SELECT date '99-08-01'; 0084 SELECT date '1999-08-01'; 0085 0086 -- SELECT date '99 01 08'; 0087 SELECT date '1999 01 08'; 0088 -- SELECT date '08 01 99'; 0089 -- SELECT date '08 01 1999'; 0090 -- SELECT date '01 08 99'; 0091 -- SELECT date '01 08 1999'; 0092 -- SELECT date '99 08 01'; 0093 SELECT date '1999 08 01'; 0094 0095 -- SET datestyle TO dmy; 0096 0097 -- SELECT date 'January 8, 1999'; 0098 SELECT date '1999-01-08'; 0099 -- SELECT date '1999-01-18'; 0100 -- SELECT date '1/8/1999'; 0101 -- SELECT date '1/18/1999'; 0102 -- SELECT date '18/1/1999'; 0103 -- SELECT date '01/02/03'; 0104 -- SELECT date '19990108'; 0105 -- SELECT date '990108'; 0106 -- SELECT date '1999.008'; 0107 -- SELECT date 'J2451187'; 0108 -- SELECT date 'January 8, 99 BC'; 0109 0110 -- SELECT date '99-Jan-08'; 0111 -- SELECT date '1999-Jan-08'; 0112 -- SELECT date '08-Jan-99'; 0113 -- SELECT date '08-Jan-1999'; 0114 -- SELECT date 'Jan-08-99'; 0115 -- SELECT date 'Jan-08-1999'; 0116 -- SELECT date '99-08-Jan'; 0117 -- SELECT date '1999-08-Jan'; 0118 0119 -- SELECT date '99 Jan 08'; 0120 SELECT date '1999 Jan 08'; 0121 -- SELECT date '08 Jan 99'; 0122 -- SELECT date '08 Jan 1999'; 0123 -- SELECT date 'Jan 08 99'; 0124 -- SELECT date 'Jan 08 1999'; 0125 -- SELECT date '99 08 Jan'; 0126 SELECT date '1999 08 Jan'; 0127 0128 -- SELECT date '99-01-08'; 0129 SELECT date '1999-01-08'; 0130 -- SELECT date '08-01-99'; 0131 -- SELECT date '08-01-1999'; 0132 -- SELECT date '01-08-99'; 0133 -- SELECT date '01-08-1999'; 0134 -- SELECT date '99-08-01'; 0135 SELECT date '1999-08-01'; 0136 0137 -- SELECT date '99 01 08'; 0138 SELECT date '1999 01 08'; 0139 -- SELECT date '08 01 99'; 0140 -- SELECT date '08 01 1999'; 0141 -- SELECT date '01 08 99'; 0142 -- SELECT date '01 08 1999'; 0143 -- SELECT date '99 08 01'; 0144 SELECT date '1999 08 01'; 0145 0146 -- SET datestyle TO mdy; 0147 0148 -- SELECT date 'January 8, 1999'; 0149 SELECT date '1999-01-08'; 0150 SELECT date '1999-01-18'; 0151 -- SELECT date '1/8/1999'; 0152 -- SELECT date '1/18/1999'; 0153 -- SELECT date '18/1/1999'; 0154 -- SELECT date '01/02/03'; 0155 -- SELECT date '19990108'; 0156 -- SELECT date '990108'; 0157 -- SELECT date '1999.008'; 0158 -- SELECT date 'J2451187'; 0159 -- SELECT date 'January 8, 99 BC'; 0160 0161 -- SELECT date '99-Jan-08'; 0162 -- SELECT date '1999-Jan-08'; 0163 -- SELECT date '08-Jan-99'; 0164 -- SELECT date '08-Jan-1999'; 0165 -- SELECT date 'Jan-08-99'; 0166 -- SELECT date 'Jan-08-1999'; 0167 -- SELECT date '99-08-Jan'; 0168 -- SELECT date '1999-08-Jan'; 0169 0170 -- SELECT date '99 Jan 08'; 0171 SELECT date '1999 Jan 08'; 0172 -- SELECT date '08 Jan 99'; 0173 -- SELECT date '08 Jan 1999'; 0174 -- SELECT date 'Jan 08 99'; 0175 -- SELECT date 'Jan 08 1999'; 0176 -- SELECT date '99 08 Jan'; 0177 SELECT date '1999 08 Jan'; 0178 0179 -- SELECT date '99-01-08'; 0180 SELECT date '1999-01-08'; 0181 -- SELECT date '08-01-99'; 0182 -- SELECT date '08-01-1999'; 0183 -- SELECT date '01-08-99'; 0184 -- SELECT date '01-08-1999'; 0185 -- SELECT date '99-08-01'; 0186 SELECT date '1999-08-01'; 0187 0188 -- SELECT date '99 01 08'; 0189 SELECT date '1999 01 08'; 0190 -- SELECT date '08 01 99'; 0191 -- SELECT date '08 01 1999'; 0192 -- SELECT date '01 08 99'; 0193 -- SELECT date '01 08 1999'; 0194 -- SELECT date '99 08 01'; 0195 SELECT date '1999 08 01'; 0196 0197 -- [SPARK-28253] Date type have different low value and high value 0198 -- Check upper and lower limits of date range 0199 SELECT date '4714-11-24 BC'; 0200 SELECT date '4714-11-23 BC'; -- out of range 0201 SELECT date '5874897-12-31'; 0202 SELECT date '5874898-01-01'; -- out of range 0203 0204 -- RESET datestyle; 0205 0206 -- 0207 -- Simple math 0208 -- Leave most of it for the horology tests 0209 -- 0210 0211 SELECT f1 - date '2000-01-01' AS `Days From 2K` FROM DATE_TBL; 0212 0213 SELECT f1 - date 'epoch' AS `Days From Epoch` FROM DATE_TBL; 0214 0215 SELECT date 'yesterday' - date 'today' AS `One day`; 0216 0217 SELECT date 'today' - date 'tomorrow' AS `One day`; 0218 0219 SELECT date 'yesterday' - date 'tomorrow' AS `Two days`; 0220 0221 SELECT date 'tomorrow' - date 'today' AS `One day`; 0222 0223 SELECT date 'today' - date 'yesterday' AS `One day`; 0224 0225 SELECT date 'tomorrow' - date 'yesterday' AS `Two days`; 0226 0227 -- [SPARK-28017] Enhance date EXTRACT 0228 -- 0229 -- test extract! 0230 -- 0231 -- epoch 0232 -- 0233 -- SELECT EXTRACT(EPOCH FROM DATE '1970-01-01'); -- 0 0234 -- SELECT EXTRACT(EPOCH FROM TIMESTAMP '1970-01-01'); -- 0 0235 -- SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '1970-01-01+00'); -- 0 0236 -- 0237 -- century 0238 -- 0239 -- SELECT EXTRACT(CENTURY FROM TO_DATE('0101-12-31 BC', 'yyyy-MM-dd G')); -- -2 0240 -- SELECT EXTRACT(CENTURY FROM TO_DATE('0100-12-31 BC', 'yyyy-MM-dd G')); -- -1 0241 -- SELECT EXTRACT(CENTURY FROM TO_DATE('0001-12-31 BC', 'yyyy-MM-dd G')); -- -1 0242 -- SELECT EXTRACT(CENTURY FROM DATE '0001-01-01'); -- 1 0243 -- SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD'); -- 1 0244 -- SELECT EXTRACT(CENTURY FROM DATE '1900-12-31'); -- 19 0245 -- SELECT EXTRACT(CENTURY FROM DATE '1901-01-01'); -- 20 0246 -- SELECT EXTRACT(CENTURY FROM DATE '2000-12-31'); -- 20 0247 -- SELECT EXTRACT(CENTURY FROM DATE '2001-01-01'); -- 21 0248 -- SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True; -- true 0249 -- 0250 -- millennium 0251 -- 0252 -- SELECT EXTRACT(MILLENNIUM FROM TO_DATE('0001-12-31 BC', 'yyyy-MM-dd G')); -- -1 0253 -- SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 AD'); -- 1 0254 -- SELECT EXTRACT(MILLENNIUM FROM DATE '1000-12-31'); -- 1 0255 -- SELECT EXTRACT(MILLENNIUM FROM DATE '1001-01-01'); -- 2 0256 -- SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31'); -- 2 0257 -- SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01'); -- 3 0258 -- next test to be fixed on the turn of the next millennium;-) 0259 -- SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE); -- 3 0260 -- 0261 -- decade 0262 -- 0263 -- SELECT EXTRACT(DECADE FROM DATE '1994-12-25'); -- 199 0264 -- SELECT EXTRACT(DECADE FROM DATE '0010-01-01'); -- 1 0265 -- SELECT EXTRACT(DECADE FROM DATE '0009-12-31'); -- 0 0266 -- SELECT EXTRACT(DECADE FROM TO_DATE('0001-01-01 BC', 'yyyy-MM-dd G')); -- 0 0267 -- SELECT EXTRACT(DECADE FROM TO_DATE('0002-12-31 BC', 'yyyy-MM-dd G')); -- -1 0268 -- SELECT EXTRACT(DECADE FROM TO_DATE('0011-01-01 BC', 'yyyy-MM-dd G')); -- -1 0269 -- SELECT EXTRACT(DECADE FROM TO_DATE('0012-12-31 BC', 'yyyy-MM-dd G')); -- -2 0270 -- 0271 -- some other types: 0272 -- 0273 -- on a timestamp. 0274 -- SELECT EXTRACT(CENTURY FROM NOW())>=21 AS True; -- true 0275 -- SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20 0276 -- on an interval 0277 -- SELECT EXTRACT(CENTURY FROM INTERVAL '100 y'); -- 1 0278 -- SELECT EXTRACT(CENTURY FROM INTERVAL '99 y'); -- 0 0279 -- SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y'); -- 0 0280 -- SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y'); -- -1 0281 -- 0282 -- test trunc function! 0283 -- SELECT DATE_TRUNC('MILLENNIUM', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1001 0284 -- SELECT DATE_TRUNC('MILLENNIUM', DATE '1970-03-20'); -- 1001-01-01 0285 -- SELECT DATE_TRUNC('CENTURY', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1901 0286 -- SELECT DATE_TRUNC('CENTURY', DATE '1970-03-20'); -- 1901 0287 -- SELECT DATE_TRUNC('CENTURY', DATE '2004-08-10'); -- 2001-01-01 0288 -- SELECT DATE_TRUNC('CENTURY', DATE '0002-02-04'); -- 0001-01-01 0289 -- SELECT DATE_TRUNC('CENTURY', TO_DATE('0055-08-10 BC', 'yyyy-MM-dd G')); -- 0100-01-01 BC 0290 -- SELECT DATE_TRUNC('DECADE', DATE '1993-12-25'); -- 1990-01-01 0291 -- SELECT DATE_TRUNC('DECADE', DATE '0004-12-25'); -- 0001-01-01 BC 0292 -- SELECT DATE_TRUNC('DECADE', TO_DATE('0002-12-31 BC', 'yyyy-MM-dd G')); -- 0011-01-01 BC 0293 0294 -- [SPARK-29006] Support special date/timestamp values `infinity`/`-infinity` 0295 -- 0296 -- test infinity 0297 -- 0298 -- select 'infinity'::date, '-infinity'::date; 0299 -- select 'infinity'::date > 'today'::date as t; 0300 -- select '-infinity'::date < 'today'::date as t; 0301 -- select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'::date); 0302 -- 0303 -- oscillating fields from non-finite date/timestamptz: 0304 -- 0305 -- SELECT EXTRACT(HOUR FROM DATE 'infinity'); -- NULL 0306 -- SELECT EXTRACT(HOUR FROM DATE '-infinity'); -- NULL 0307 -- SELECT EXTRACT(HOUR FROM TIMESTAMP 'infinity'); -- NULL 0308 -- SELECT EXTRACT(HOUR FROM TIMESTAMP '-infinity'); -- NULL 0309 -- SELECT EXTRACT(HOUR FROM TIMESTAMPTZ 'infinity'); -- NULL 0310 -- SELECT EXTRACT(HOUR FROM TIMESTAMPTZ '-infinity'); -- NULL 0311 -- all possible fields 0312 -- SELECT EXTRACT(MICROSECONDS FROM DATE 'infinity'); -- NULL 0313 -- SELECT EXTRACT(MILLISECONDS FROM DATE 'infinity'); -- NULL 0314 -- SELECT EXTRACT(SECOND FROM DATE 'infinity'); -- NULL 0315 -- SELECT EXTRACT(MINUTE FROM DATE 'infinity'); -- NULL 0316 -- SELECT EXTRACT(HOUR FROM DATE 'infinity'); -- NULL 0317 -- SELECT EXTRACT(DAY FROM DATE 'infinity'); -- NULL 0318 -- SELECT EXTRACT(MONTH FROM DATE 'infinity'); -- NULL 0319 -- SELECT EXTRACT(QUARTER FROM DATE 'infinity'); -- NULL 0320 -- SELECT EXTRACT(WEEK FROM DATE 'infinity'); -- NULL 0321 -- SELECT EXTRACT(DOW FROM DATE 'infinity'); -- NULL 0322 -- SELECT EXTRACT(ISODOW FROM DATE 'infinity'); -- NULL 0323 -- SELECT EXTRACT(DOY FROM DATE 'infinity'); -- NULL 0324 -- SELECT EXTRACT(TIMEZONE FROM DATE 'infinity'); -- NULL 0325 -- SELECT EXTRACT(TIMEZONE_M FROM DATE 'infinity'); -- NULL 0326 -- SELECT EXTRACT(TIMEZONE_H FROM DATE 'infinity'); -- NULL 0327 -- 0328 -- monotonic fields from non-finite date/timestamptz: 0329 -- 0330 -- SELECT EXTRACT(EPOCH FROM DATE 'infinity'); -- Infinity 0331 -- SELECT EXTRACT(EPOCH FROM DATE '-infinity'); -- -Infinity 0332 -- SELECT EXTRACT(EPOCH FROM TIMESTAMP 'infinity'); -- Infinity 0333 -- SELECT EXTRACT(EPOCH FROM TIMESTAMP '-infinity'); -- -Infinity 0334 -- SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ 'infinity'); -- Infinity 0335 -- SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '-infinity'); -- -Infinity 0336 -- all possible fields 0337 -- SELECT EXTRACT(YEAR FROM DATE 'infinity'); -- Infinity 0338 -- SELECT EXTRACT(DECADE FROM DATE 'infinity'); -- Infinity 0339 -- SELECT EXTRACT(CENTURY FROM DATE 'infinity'); -- Infinity 0340 -- SELECT EXTRACT(MILLENNIUM FROM DATE 'infinity'); -- Infinity 0341 -- SELECT EXTRACT(JULIAN FROM DATE 'infinity'); -- Infinity 0342 -- SELECT EXTRACT(ISOYEAR FROM DATE 'infinity'); -- Infinity 0343 -- SELECT EXTRACT(EPOCH FROM DATE 'infinity'); -- Infinity 0344 -- 0345 -- wrong fields from non-finite date: 0346 -- 0347 -- SELECT EXTRACT(MICROSEC FROM DATE 'infinity'); -- ERROR: timestamp units "microsec" not recognized 0348 -- SELECT EXTRACT(UNDEFINED FROM DATE 'infinity'); -- ERROR: timestamp units "undefined" not supported 0349 0350 -- test constructors 0351 select make_date(2013, 7, 15); 0352 -- [SPARK-28471] Formatting dates with negative years 0353 select make_date(-44, 3, 15); 0354 -- select make_time(8, 20, 0.0); 0355 -- should fail 0356 select make_date(2013, 2, 30); 0357 select make_date(2013, 13, 1); 0358 select make_date(2013, 11, -1); 0359 -- select make_time(10, 55, 100.1); 0360 -- select make_time(24, 0, 2.1); 0361 0362 DROP TABLE DATE_TBL;
| [ Source navigation ] | [ Diff markup ] | [ Identifier search ] | [ general search ] |
|
This page was automatically generated by the 2.1.0 LXR engine. The LXR team |
|