1-- 2-- HOROLOGY 3-- 4SET DateStyle = 'Postgres, MDY'; 5 6-- 7-- Test various input formats 8-- 9SELECT timestamp with time zone '20011227 040506+08'; 10SELECT timestamp with time zone '20011227 040506-08'; 11SELECT timestamp with time zone '20011227 040506.789+08'; 12SELECT timestamp with time zone '20011227 040506.789-08'; 13SELECT timestamp with time zone '20011227T040506+08'; 14SELECT timestamp with time zone '20011227T040506-08'; 15SELECT timestamp with time zone '20011227T040506.789+08'; 16SELECT timestamp with time zone '20011227T040506.789-08'; 17SELECT timestamp with time zone '2001-12-27 04:05:06.789-08'; 18SELECT timestamp with time zone '2001.12.27 04:05:06.789-08'; 19SELECT timestamp with time zone '2001/12/27 04:05:06.789-08'; 20SELECT timestamp with time zone '12/27/2001 04:05:06.789-08'; 21-- should fail in mdy mode: 22SELECT timestamp with time zone '27/12/2001 04:05:06.789-08'; 23set datestyle to dmy; 24SELECT timestamp with time zone '27/12/2001 04:05:06.789-08'; 25reset datestyle; 26SELECT timestamp with time zone 'Y2001M12D27H04M05S06.789+08'; 27SELECT timestamp with time zone 'Y2001M12D27H04M05S06.789-08'; 28SELECT timestamp with time zone 'Y2001M12D27H04MM05S06.789+08'; 29SELECT timestamp with time zone 'Y2001M12D27H04MM05S06.789-08'; 30SELECT timestamp with time zone 'J2452271+08'; 31SELECT timestamp with time zone 'J2452271-08'; 32SELECT timestamp with time zone 'J2452271.5+08'; 33SELECT timestamp with time zone 'J2452271.5-08'; 34SELECT timestamp with time zone 'J2452271 04:05:06+08'; 35SELECT timestamp with time zone 'J2452271 04:05:06-08'; 36SELECT timestamp with time zone 'J2452271T040506+08'; 37SELECT timestamp with time zone 'J2452271T040506-08'; 38SELECT timestamp with time zone 'J2452271T040506.789+08'; 39SELECT timestamp with time zone 'J2452271T040506.789-08'; 40-- German/European-style dates with periods as delimiters 41SELECT timestamp with time zone '12.27.2001 04:05:06.789+08'; 42SELECT timestamp with time zone '12.27.2001 04:05:06.789-08'; 43SET DateStyle = 'German'; 44SELECT timestamp with time zone '27.12.2001 04:05:06.789+08'; 45SELECT timestamp with time zone '27.12.2001 04:05:06.789-08'; 46SET DateStyle = 'ISO'; 47-- As of 7.4, allow time without time zone having a time zone specified 48SELECT time without time zone '040506.789+08'; 49SELECT time without time zone '040506.789-08'; 50SELECT time without time zone 'T040506.789+08'; 51SELECT time without time zone 'T040506.789-08'; 52SELECT time with time zone '040506.789+08'; 53SELECT time with time zone '040506.789-08'; 54SELECT time with time zone 'T040506.789+08'; 55SELECT time with time zone 'T040506.789-08'; 56SELECT time with time zone 'T040506.789 +08'; 57SELECT time with time zone 'T040506.789 -08'; 58SET DateStyle = 'Postgres, MDY'; 59-- Check Julian dates BC 60SELECT date 'J1520447' AS "Confucius' Birthday"; 61SELECT date 'J0' AS "Julian Epoch"; 62 63-- 64-- date, time arithmetic 65-- 66 67SELECT date '1981-02-03' + time '04:05:06' AS "Date + Time"; 68SELECT date '1991-02-03' + time with time zone '04:05:06 PST' AS "Date + Time PST"; 69SELECT date '2001-02-03' + time with time zone '04:05:06 UTC' AS "Date + Time UTC"; 70SELECT date '1991-02-03' + interval '2 years' AS "Add Two Years"; 71SELECT date '2001-12-13' - interval '2 years' AS "Subtract Two Years"; 72-- subtract time from date should not make sense; use interval instead 73SELECT date '1991-02-03' - time '04:05:06' AS "Subtract Time"; 74SELECT date '1991-02-03' - time with time zone '04:05:06 UTC' AS "Subtract Time UTC"; 75 76-- 77-- timestamp, interval arithmetic 78-- 79 80SELECT timestamp without time zone '1996-03-01' - interval '1 second' AS "Feb 29"; 81SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28"; 82SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29"; 83SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31"; 84SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506"; 85SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244"; 86SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276"; 87SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days"; 88 89-- Shorthand values 90-- Not directly usable for regression testing since these are not constants. 91-- So, just try to test parser and hope for the best - thomas 97/04/26 92SELECT (timestamp without time zone 'today' = (timestamp without time zone 'yesterday' + interval '1 day')) as "True"; 93SELECT (timestamp without time zone 'today' = (timestamp without time zone 'tomorrow' - interval '1 day')) as "True"; 94SELECT (timestamp without time zone 'today 10:30' = (timestamp without time zone 'yesterday' + interval '1 day 10 hr 30 min')) as "True"; 95SELECT (timestamp without time zone '10:30 today' = (timestamp without time zone 'yesterday' + interval '1 day 10 hr 30 min')) as "True"; 96SELECT (timestamp without time zone 'tomorrow' = (timestamp without time zone 'yesterday' + interval '2 days')) as "True"; 97SELECT (timestamp without time zone 'tomorrow 16:00:00' = (timestamp without time zone 'today' + interval '1 day 16 hours')) as "True"; 98SELECT (timestamp without time zone '16:00:00 tomorrow' = (timestamp without time zone 'today' + interval '1 day 16 hours')) as "True"; 99SELECT (timestamp without time zone 'yesterday 12:34:56' = (timestamp without time zone 'tomorrow' - interval '2 days - 12:34:56')) as "True"; 100SELECT (timestamp without time zone '12:34:56 yesterday' = (timestamp without time zone 'tomorrow' - interval '2 days - 12:34:56')) as "True"; 101SELECT (timestamp without time zone 'tomorrow' > 'now') as "True"; 102 103-- Convert from date and time to timestamp 104-- This test used to be timestamp(date,time) but no longer allowed by grammar 105-- to enable support for SQL99 timestamp type syntax. 106SELECT date '1994-01-01' + time '11:00' AS "Jan_01_1994_11am"; 107SELECT date '1994-01-01' + time '10:00' AS "Jan_01_1994_10am"; 108SELECT date '1994-01-01' + timetz '11:00-5' AS "Jan_01_1994_8am"; 109SELECT timestamptz(date '1994-01-01', time with time zone '11:00-5') AS "Jan_01_1994_8am"; 110 111SELECT '' AS "64", d1 + interval '1 year' AS one_year FROM TIMESTAMP_TBL; 112SELECT '' AS "64", d1 - interval '1 year' AS one_year FROM TIMESTAMP_TBL; 113 114SELECT timestamp with time zone '1996-03-01' - interval '1 second' AS "Feb 29"; 115SELECT timestamp with time zone '1999-03-01' - interval '1 second' AS "Feb 28"; 116SELECT timestamp with time zone '2000-03-01' - interval '1 second' AS "Feb 29"; 117SELECT timestamp with time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31"; 118 119SELECT (timestamp with time zone 'today' = (timestamp with time zone 'yesterday' + interval '1 day')) as "True"; 120SELECT (timestamp with time zone 'today' = (timestamp with time zone 'tomorrow' - interval '1 day')) as "True"; 121SELECT (timestamp with time zone 'tomorrow' = (timestamp with time zone 'yesterday' + interval '2 days')) as "True"; 122SELECT (timestamp with time zone 'tomorrow' > 'now') as "True"; 123 124-- timestamp with time zone, interval arithmetic around DST change 125SET TIME ZONE 'CST7CDT'; 126SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' as "Apr 3, 12:00"; 127SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '24 hours' as "Apr 3, 13:00"; 128SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '1 day' as "Apr 2, 12:00"; 129SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '24 hours' as "Apr 2, 11:00"; 130RESET TIME ZONE; 131 132 133SELECT timestamptz(date '1994-01-01', time '11:00') AS "Jan_01_1994_10am"; 134SELECT timestamptz(date '1994-01-01', time '10:00') AS "Jan_01_1994_9am"; 135SELECT timestamptz(date '1994-01-01', time with time zone '11:00-8') AS "Jan_01_1994_11am"; 136SELECT timestamptz(date '1994-01-01', time with time zone '10:00-8') AS "Jan_01_1994_10am"; 137SELECT timestamptz(date '1994-01-01', time with time zone '11:00-5') AS "Jan_01_1994_8am"; 138 139SELECT '' AS "64", d1 + interval '1 year' AS one_year FROM TIMESTAMPTZ_TBL; 140SELECT '' AS "64", d1 - interval '1 year' AS one_year FROM TIMESTAMPTZ_TBL; 141 142-- 143-- time, interval arithmetic 144-- 145 146SELECT CAST(time '01:02' AS interval) AS "+01:02"; 147SELECT CAST(interval '02:03' AS time) AS "02:03:00"; 148SELECT time '01:30' + interval '02:01' AS "03:31:00"; 149SELECT time '01:30' - interval '02:01' AS "23:29:00"; 150SELECT time '02:30' + interval '36:01' AS "14:31:00"; 151SELECT time '03:30' + interval '1 month 04:01' AS "07:31:00"; 152SELECT CAST(time with time zone '01:02-08' AS interval) AS "+00:01"; 153SELECT CAST(interval '02:03' AS time with time zone) AS "02:03:00-08"; 154SELECT time with time zone '01:30-08' - interval '02:01' AS "23:29:00-08"; 155SELECT time with time zone '02:30-08' + interval '36:01' AS "14:31:00-08"; 156 157-- These two tests cannot be used because they default to current timezone, 158-- which may be either -08 or -07 depending on the time of year. 159-- SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08"; 160-- SELECT time with time zone '03:30' + interval '1 month 04:01' AS "07:31:00-08"; 161-- Try the following two tests instead, as a poor substitute 162 163SELECT CAST(CAST(date 'today' + time with time zone '05:30' 164 + interval '02:01' AS time with time zone) AS time) AS "07:31:00"; 165 166SELECT CAST(cast(date 'today' + time with time zone '03:30' 167 + interval '1 month 04:01' as timestamp without time zone) AS time) AS "07:31:00"; 168 169SELECT t.d1 AS t, i.f1 AS i, t.d1 + i.f1 AS "add", t.d1 - i.f1 AS "subtract" 170 FROM TIMESTAMP_TBL t, INTERVAL_TBL i 171 WHERE t.d1 BETWEEN '1990-01-01' AND '2001-01-01' 172 AND i.f1 BETWEEN '00:00' AND '23:00' 173 ORDER BY 1,2; 174 175SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract" 176 FROM TIME_TBL t, INTERVAL_TBL i 177 ORDER BY 1,2; 178 179SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract" 180 FROM TIMETZ_TBL t, INTERVAL_TBL i 181 ORDER BY 1,2; 182 183-- SQL9x OVERLAPS operator 184-- test with time zone 185SELECT (timestamp with time zone '2000-11-27', timestamp with time zone '2000-11-28') 186 OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "True"; 187 188SELECT (timestamp with time zone '2000-11-26', timestamp with time zone '2000-11-27') 189 OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "False"; 190 191SELECT (timestamp with time zone '2000-11-27', timestamp with time zone '2000-11-28') 192 OVERLAPS (timestamp with time zone '2000-11-27 12:00', interval '1 day') AS "True"; 193 194SELECT (timestamp with time zone '2000-11-27', interval '12 hours') 195 OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "False"; 196 197SELECT (timestamp with time zone '2000-11-27', interval '12 hours') 198 OVERLAPS (timestamp with time zone '2000-11-27', interval '12 hours') AS "True"; 199 200SELECT (timestamp with time zone '2000-11-27', interval '12 hours') 201 OVERLAPS (timestamp with time zone '2000-11-27 12:00', interval '12 hours') AS "False"; 202 203-- test without time zone 204SELECT (timestamp without time zone '2000-11-27', timestamp without time zone '2000-11-28') 205 OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30') AS "True"; 206 207SELECT (timestamp without time zone '2000-11-26', timestamp without time zone '2000-11-27') 208 OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30') AS "False"; 209 210SELECT (timestamp without time zone '2000-11-27', timestamp without time zone '2000-11-28') 211 OVERLAPS (timestamp without time zone '2000-11-27 12:00', interval '1 day') AS "True"; 212 213SELECT (timestamp without time zone '2000-11-27', interval '12 hours') 214 OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30') AS "False"; 215 216SELECT (timestamp without time zone '2000-11-27', interval '12 hours') 217 OVERLAPS (timestamp without time zone '2000-11-27', interval '12 hours') AS "True"; 218 219SELECT (timestamp without time zone '2000-11-27', interval '12 hours') 220 OVERLAPS (timestamp without time zone '2000-11-27 12:00', interval '12 hours') AS "False"; 221 222-- test time and interval 223SELECT (time '00:00', time '01:00') 224 OVERLAPS (time '00:30', time '01:30') AS "True"; 225 226SELECT (time '00:00', interval '1 hour') 227 OVERLAPS (time '00:30', interval '1 hour') AS "True"; 228 229SELECT (time '00:00', interval '1 hour') 230 OVERLAPS (time '01:30', interval '1 hour') AS "False"; 231 232-- SQL99 seems to want this to be false (and we conform to the spec). 233-- istm that this *should* return true, on the theory that time 234-- intervals can wrap around the day boundary - thomas 2001-09-25 235SELECT (time '00:00', interval '1 hour') 236 OVERLAPS (time '01:30', interval '1 day') AS "False"; 237 238CREATE TABLE TEMP_TIMESTAMP (f1 timestamp with time zone); 239 240-- get some candidate input values 241 242INSERT INTO TEMP_TIMESTAMP (f1) 243 SELECT d1 FROM TIMESTAMP_TBL 244 WHERE d1 BETWEEN '13-jun-1957' AND '1-jan-1997' 245 OR d1 BETWEEN '1-jan-1999' AND '1-jan-2010'; 246 247SELECT '' AS "16", f1 AS "timestamp" 248 FROM TEMP_TIMESTAMP 249 ORDER BY "timestamp"; 250 251SELECT '' AS "160", d.f1 AS "timestamp", t.f1 AS "interval", d.f1 + t.f1 AS plus 252 FROM TEMP_TIMESTAMP d, INTERVAL_TBL t 253 ORDER BY plus, "timestamp", "interval"; 254 255SELECT '' AS "160", d.f1 AS "timestamp", t.f1 AS "interval", d.f1 - t.f1 AS minus 256 FROM TEMP_TIMESTAMP d, INTERVAL_TBL t 257 WHERE isfinite(d.f1) 258 ORDER BY minus, "timestamp", "interval"; 259 260SELECT '' AS "16", d.f1 AS "timestamp", 261 timestamp with time zone '1980-01-06 00:00 GMT' AS gpstime_zero, 262 d.f1 - timestamp with time zone '1980-01-06 00:00 GMT' AS difference 263 FROM TEMP_TIMESTAMP d 264 ORDER BY difference; 265 266SELECT '' AS "226", d1.f1 AS timestamp1, d2.f1 AS timestamp2, d1.f1 - d2.f1 AS difference 267 FROM TEMP_TIMESTAMP d1, TEMP_TIMESTAMP d2 268 ORDER BY timestamp1, timestamp2, difference; 269 270-- 271-- Conversions 272-- 273 274SELECT '' AS "16", f1 AS "timestamp", date(f1) AS date 275 FROM TEMP_TIMESTAMP 276 WHERE f1 <> timestamp 'now' 277 ORDER BY date, "timestamp"; 278 279DROP TABLE TEMP_TIMESTAMP; 280 281-- 282-- Formats 283-- 284 285SET DateStyle TO 'US,Postgres'; 286 287SHOW DateStyle; 288 289SELECT '' AS "64", d1 AS us_postgres FROM TIMESTAMP_TBL; 290 291SET DateStyle TO 'US,ISO'; 292 293SELECT '' AS "64", d1 AS us_iso FROM TIMESTAMP_TBL; 294 295SET DateStyle TO 'US,SQL'; 296 297SHOW DateStyle; 298 299SELECT '' AS "64", d1 AS us_sql FROM TIMESTAMP_TBL; 300 301SET DateStyle TO 'European,Postgres'; 302 303SHOW DateStyle; 304 305INSERT INTO TIMESTAMP_TBL VALUES('13/06/1957'); 306 307SELECT count(*) as one FROM TIMESTAMP_TBL WHERE d1 = 'Jun 13 1957'; 308 309SELECT '' AS "65", d1 AS european_postgres FROM TIMESTAMP_TBL; 310 311SET DateStyle TO 'European,ISO'; 312 313SHOW DateStyle; 314 315SELECT '' AS "65", d1 AS european_iso FROM TIMESTAMP_TBL; 316 317SET DateStyle TO 'European,SQL'; 318 319SHOW DateStyle; 320 321SELECT '' AS "65", d1 AS european_sql FROM TIMESTAMP_TBL; 322 323RESET DateStyle; 324 325-- 326-- to_timestamp() 327-- 328 329SELECT to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS'); 330 331SELECT to_timestamp('97/2/16 8:14:30', 'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS'); 332 333SELECT to_timestamp('2011$03!18 23_38_15', 'YYYY-MM-DD HH24:MI:SS'); 334 335SELECT to_timestamp('1985 January 12', 'YYYY FMMonth DD'); 336 337SELECT to_timestamp('1985 FMMonth 12', 'YYYY "FMMonth" DD'); 338 339SELECT to_timestamp('1985 \ 12', 'YYYY \\ DD'); 340 341SELECT to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16', 342 '"My birthday-> Year:" YYYY, "Month:" FMMonth, "Day:" DD'); 343 344SELECT to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD'); 345 346SELECT to_timestamp('15 "text between quote marks" 98 54 45', 347 E'HH24 "\\"text between quote marks\\"" YY MI SS'); 348 349SELECT to_timestamp('05121445482000', 'MMDDHH24MISSYYYY'); 350 351SELECT to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay'); 352 353SELECT to_timestamp('97/Feb/16', 'YYMonDD'); 354 355SELECT to_timestamp('97/Feb/16', 'YY:Mon:DD'); 356 357SELECT to_timestamp('97/Feb/16', 'FXYY:Mon:DD'); 358 359SELECT to_timestamp('97/Feb/16', 'FXYY/Mon/DD'); 360 361SELECT to_timestamp('19971116', 'YYYYMMDD'); 362 363SELECT to_timestamp('20000-1116', 'YYYY-MMDD'); 364 365SELECT to_timestamp('1997 AD 11 16', 'YYYY BC MM DD'); 366SELECT to_timestamp('1997 BC 11 16', 'YYYY BC MM DD'); 367 368SELECT to_timestamp('9-1116', 'Y-MMDD'); 369 370SELECT to_timestamp('95-1116', 'YY-MMDD'); 371 372SELECT to_timestamp('995-1116', 'YYY-MMDD'); 373 374SELECT to_timestamp('2005426', 'YYYYWWD'); 375 376SELECT to_timestamp('2005300', 'YYYYDDD'); 377 378SELECT to_timestamp('2005527', 'IYYYIWID'); 379 380SELECT to_timestamp('005527', 'IYYIWID'); 381 382SELECT to_timestamp('05527', 'IYIWID'); 383 384SELECT to_timestamp('5527', 'IIWID'); 385 386SELECT to_timestamp('2005364', 'IYYYIDDD'); 387 388SELECT to_timestamp('20050302', 'YYYYMMDD'); 389 390SELECT to_timestamp('2005 03 02', 'YYYYMMDD'); 391 392SELECT to_timestamp(' 2005 03 02', 'YYYYMMDD'); 393 394SELECT to_timestamp(' 20050302', 'YYYYMMDD'); 395 396SELECT to_timestamp('2011-12-18 11:38 AM', 'YYYY-MM-DD HH12:MI PM'); 397SELECT to_timestamp('2011-12-18 11:38 PM', 'YYYY-MM-DD HH12:MI PM'); 398 399SELECT to_timestamp('2011-12-18 11:38 +05', 'YYYY-MM-DD HH12:MI TZH'); 400SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI TZH'); 401SELECT to_timestamp('2011-12-18 11:38 +05:20', 'YYYY-MM-DD HH12:MI TZH:TZM'); 402SELECT to_timestamp('2011-12-18 11:38 -05:20', 'YYYY-MM-DD HH12:MI TZH:TZM'); 403SELECT to_timestamp('2011-12-18 11:38 20', 'YYYY-MM-DD HH12:MI TZM'); 404 405-- 406-- Check handling of multiple spaces in format and/or input 407-- 408 409SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 410SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 411SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 412 413SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 414SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 415SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 416 417SELECT to_timestamp('2000+ JUN', 'YYYY/MON'); 418SELECT to_timestamp(' 2000 +JUN', 'YYYY/MON'); 419SELECT to_timestamp(' 2000 +JUN', 'YYYY//MON'); 420SELECT to_timestamp('2000 +JUN', 'YYYY//MON'); 421SELECT to_timestamp('2000 + JUN', 'YYYY MON'); 422SELECT to_timestamp('2000 ++ JUN', 'YYYY MON'); 423SELECT to_timestamp('2000 + + JUN', 'YYYY MON'); 424SELECT to_timestamp('2000 + + JUN', 'YYYY MON'); 425SELECT to_timestamp('2000 -10', 'YYYY TZH'); 426SELECT to_timestamp('2000 -10', 'YYYY TZH'); 427 428SELECT to_date('2011 12 18', 'YYYY MM DD'); 429SELECT to_date('2011 12 18', 'YYYY MM DD'); 430SELECT to_date('2011 12 18', 'YYYY MM DD'); 431 432SELECT to_date('2011 12 18', 'YYYY MM DD'); 433SELECT to_date('2011 12 18', 'YYYY MM DD'); 434SELECT to_date('2011 12 18', 'YYYY MM DD'); 435 436SELECT to_date('2011 12 18', 'YYYYxMMxDD'); 437SELECT to_date('2011x 12x 18', 'YYYYxMMxDD'); 438SELECT to_date('2011 x12 x18', 'YYYYxMMxDD'); 439 440-- 441-- Check errors for some incorrect usages of to_timestamp() and to_date() 442-- 443 444-- Mixture of date conventions (ISO week and Gregorian): 445SELECT to_timestamp('2005527', 'YYYYIWID'); 446 447-- Insufficient characters in the source string: 448SELECT to_timestamp('19971', 'YYYYMMDD'); 449 450-- Insufficient digit characters for a single node: 451SELECT to_timestamp('19971)24', 'YYYYMMDD'); 452 453-- Value clobbering: 454SELECT to_timestamp('1997-11-Jan-16', 'YYYY-MM-Mon-DD'); 455 456-- Non-numeric input: 457SELECT to_timestamp('199711xy', 'YYYYMMDD'); 458 459-- Input that doesn't fit in an int: 460SELECT to_timestamp('10000000000', 'FMYYYY'); 461 462-- Out-of-range and not-quite-out-of-range fields: 463SELECT to_timestamp('2016-06-13 25:00:00', 'YYYY-MM-DD HH24:MI:SS'); 464SELECT to_timestamp('2016-06-13 15:60:00', 'YYYY-MM-DD HH24:MI:SS'); 465SELECT to_timestamp('2016-06-13 15:50:60', 'YYYY-MM-DD HH24:MI:SS'); 466SELECT to_timestamp('2016-06-13 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); -- ok 467SELECT to_timestamp('2016-06-13 15:50:55', 'YYYY-MM-DD HH:MI:SS'); 468SELECT to_timestamp('2016-13-01 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); 469SELECT to_timestamp('2016-02-30 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); 470SELECT to_timestamp('2016-02-29 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); -- ok 471SELECT to_timestamp('2015-02-29 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); 472SELECT to_timestamp('2015-02-11 86000', 'YYYY-MM-DD SSSS'); -- ok 473SELECT to_timestamp('2015-02-11 86400', 'YYYY-MM-DD SSSS'); 474SELECT to_date('2016-13-10', 'YYYY-MM-DD'); 475SELECT to_date('2016-02-30', 'YYYY-MM-DD'); 476SELECT to_date('2016-02-29', 'YYYY-MM-DD'); -- ok 477SELECT to_date('2015-02-29', 'YYYY-MM-DD'); 478SELECT to_date('2015 365', 'YYYY DDD'); -- ok 479SELECT to_date('2015 366', 'YYYY DDD'); 480SELECT to_date('2016 365', 'YYYY DDD'); -- ok 481SELECT to_date('2016 366', 'YYYY DDD'); -- ok 482SELECT to_date('2016 367', 'YYYY DDD'); 483 484-- 485-- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572) 486-- 487 488SET TIME ZONE 'America/New_York'; 489SET TIME ZONE '-1.5'; 490 491SHOW TIME ZONE; 492 493SELECT '2012-12-12 12:00'::timestamptz; 494SELECT '2012-12-12 12:00 America/New_York'::timestamptz; 495 496SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ'); 497 498RESET TIME ZONE; 499