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 125-- (just for fun, let's use an intentionally nonstandard POSIX zone spec) 126SET TIME ZONE 'CST7CDT,M4.1.0,M10.5.0'; 127SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' as "Apr 3, 12:00"; 128SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '24 hours' as "Apr 3, 13:00"; 129SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '1 day' as "Apr 2, 12:00"; 130SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '24 hours' as "Apr 2, 11:00"; 131RESET TIME ZONE; 132 133 134SELECT timestamptz(date '1994-01-01', time '11:00') AS "Jan_01_1994_10am"; 135SELECT timestamptz(date '1994-01-01', time '10:00') AS "Jan_01_1994_9am"; 136SELECT timestamptz(date '1994-01-01', time with time zone '11:00-8') AS "Jan_01_1994_11am"; 137SELECT timestamptz(date '1994-01-01', time with time zone '10:00-8') AS "Jan_01_1994_10am"; 138SELECT timestamptz(date '1994-01-01', time with time zone '11:00-5') AS "Jan_01_1994_8am"; 139 140SELECT '' AS "64", d1 + interval '1 year' AS one_year FROM TIMESTAMPTZ_TBL; 141SELECT '' AS "64", d1 - interval '1 year' AS one_year FROM TIMESTAMPTZ_TBL; 142 143-- 144-- time, interval arithmetic 145-- 146 147SELECT CAST(time '01:02' AS interval) AS "+01:02"; 148SELECT CAST(interval '02:03' AS time) AS "02:03:00"; 149SELECT time '01:30' + interval '02:01' AS "03:31:00"; 150SELECT time '01:30' - interval '02:01' AS "23:29:00"; 151SELECT time '02:30' + interval '36:01' AS "14:31:00"; 152SELECT time '03:30' + interval '1 month 04:01' AS "07:31:00"; 153SELECT CAST(time with time zone '01:02-08' AS interval) AS "+00:01"; 154SELECT CAST(interval '02:03' AS time with time zone) AS "02:03:00-08"; 155SELECT time with time zone '01:30-08' - interval '02:01' AS "23:29:00-08"; 156SELECT time with time zone '02:30-08' + interval '36:01' AS "14:31:00-08"; 157 158-- These two tests cannot be used because they default to current timezone, 159-- which may be either -08 or -07 depending on the time of year. 160-- SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08"; 161-- SELECT time with time zone '03:30' + interval '1 month 04:01' AS "07:31:00-08"; 162-- Try the following two tests instead, as a poor substitute 163 164SELECT CAST(CAST(date 'today' + time with time zone '05:30' 165 + interval '02:01' AS time with time zone) AS time) AS "07:31:00"; 166 167SELECT CAST(cast(date 'today' + time with time zone '03:30' 168 + interval '1 month 04:01' as timestamp without time zone) AS time) AS "07:31:00"; 169 170SELECT t.d1 AS t, i.f1 AS i, t.d1 + i.f1 AS "add", t.d1 - i.f1 AS "subtract" 171 FROM TIMESTAMP_TBL t, INTERVAL_TBL i 172 WHERE t.d1 BETWEEN '1990-01-01' AND '2001-01-01' 173 AND i.f1 BETWEEN '00:00' AND '23:00' 174 ORDER BY 1,2; 175 176SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract" 177 FROM TIME_TBL t, INTERVAL_TBL i 178 ORDER BY 1,2; 179 180SELECT t.f1 AS t, i.f1 AS i, t.f1 + i.f1 AS "add", t.f1 - i.f1 AS "subtract" 181 FROM TIMETZ_TBL t, INTERVAL_TBL i 182 ORDER BY 1,2; 183 184-- SQL9x OVERLAPS operator 185-- test with time zone 186SELECT (timestamp with time zone '2000-11-27', timestamp with time zone '2000-11-28') 187 OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "True"; 188 189SELECT (timestamp with time zone '2000-11-26', timestamp with time zone '2000-11-27') 190 OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "False"; 191 192SELECT (timestamp with time zone '2000-11-27', timestamp with time zone '2000-11-28') 193 OVERLAPS (timestamp with time zone '2000-11-27 12:00', interval '1 day') AS "True"; 194 195SELECT (timestamp with time zone '2000-11-27', interval '12 hours') 196 OVERLAPS (timestamp with time zone '2000-11-27 12:00', timestamp with time zone '2000-11-30') AS "False"; 197 198SELECT (timestamp with time zone '2000-11-27', interval '12 hours') 199 OVERLAPS (timestamp with time zone '2000-11-27', interval '12 hours') AS "True"; 200 201SELECT (timestamp with time zone '2000-11-27', interval '12 hours') 202 OVERLAPS (timestamp with time zone '2000-11-27 12:00', interval '12 hours') AS "False"; 203 204-- test without time zone 205SELECT (timestamp without time zone '2000-11-27', timestamp without time zone '2000-11-28') 206 OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30') AS "True"; 207 208SELECT (timestamp without time zone '2000-11-26', timestamp without time zone '2000-11-27') 209 OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30') AS "False"; 210 211SELECT (timestamp without time zone '2000-11-27', timestamp without time zone '2000-11-28') 212 OVERLAPS (timestamp without time zone '2000-11-27 12:00', interval '1 day') AS "True"; 213 214SELECT (timestamp without time zone '2000-11-27', interval '12 hours') 215 OVERLAPS (timestamp without time zone '2000-11-27 12:00', timestamp without time zone '2000-11-30') AS "False"; 216 217SELECT (timestamp without time zone '2000-11-27', interval '12 hours') 218 OVERLAPS (timestamp without time zone '2000-11-27', interval '12 hours') AS "True"; 219 220SELECT (timestamp without time zone '2000-11-27', interval '12 hours') 221 OVERLAPS (timestamp without time zone '2000-11-27 12:00', interval '12 hours') AS "False"; 222 223-- test time and interval 224SELECT (time '00:00', time '01:00') 225 OVERLAPS (time '00:30', time '01:30') AS "True"; 226 227SELECT (time '00:00', interval '1 hour') 228 OVERLAPS (time '00:30', interval '1 hour') AS "True"; 229 230SELECT (time '00:00', interval '1 hour') 231 OVERLAPS (time '01:30', interval '1 hour') AS "False"; 232 233-- SQL99 seems to want this to be false (and we conform to the spec). 234-- istm that this *should* return true, on the theory that time 235-- intervals can wrap around the day boundary - thomas 2001-09-25 236SELECT (time '00:00', interval '1 hour') 237 OVERLAPS (time '01:30', interval '1 day') AS "False"; 238 239CREATE TABLE TEMP_TIMESTAMP (f1 timestamp with time zone); 240 241-- get some candidate input values 242 243INSERT INTO TEMP_TIMESTAMP (f1) 244 SELECT d1 FROM TIMESTAMP_TBL 245 WHERE d1 BETWEEN '13-jun-1957' AND '1-jan-1997' 246 OR d1 BETWEEN '1-jan-1999' AND '1-jan-2010'; 247 248SELECT '' AS "16", f1 AS "timestamp" 249 FROM TEMP_TIMESTAMP 250 ORDER BY "timestamp"; 251 252SELECT '' AS "160", d.f1 AS "timestamp", t.f1 AS "interval", d.f1 + t.f1 AS plus 253 FROM TEMP_TIMESTAMP d, INTERVAL_TBL t 254 ORDER BY plus, "timestamp", "interval"; 255 256SELECT '' AS "160", d.f1 AS "timestamp", t.f1 AS "interval", d.f1 - t.f1 AS minus 257 FROM TEMP_TIMESTAMP d, INTERVAL_TBL t 258 WHERE isfinite(d.f1) 259 ORDER BY minus, "timestamp", "interval"; 260 261SELECT '' AS "16", d.f1 AS "timestamp", 262 timestamp with time zone '1980-01-06 00:00 GMT' AS gpstime_zero, 263 d.f1 - timestamp with time zone '1980-01-06 00:00 GMT' AS difference 264 FROM TEMP_TIMESTAMP d 265 ORDER BY difference; 266 267SELECT '' AS "226", d1.f1 AS timestamp1, d2.f1 AS timestamp2, d1.f1 - d2.f1 AS difference 268 FROM TEMP_TIMESTAMP d1, TEMP_TIMESTAMP d2 269 ORDER BY timestamp1, timestamp2, difference; 270 271-- 272-- abstime, reltime arithmetic 273-- 274 275SELECT '' AS ten, ABSTIME_TBL.f1 AS abstime, RELTIME_TBL.f1 AS reltime 276 FROM ABSTIME_TBL, RELTIME_TBL 277 WHERE (ABSTIME_TBL.f1 + RELTIME_TBL.f1) < abstime 'Jan 14 14:00:00 1971' 278 ORDER BY abstime, reltime; 279 280-- these four queries should return the same answer 281-- the "infinity" and "-infinity" tuples in ABSTIME_TBL cannot be added and 282-- therefore, should not show up in the results. 283 284SELECT '' AS three, * FROM ABSTIME_TBL 285 WHERE (ABSTIME_TBL.f1 + reltime '@ 3 year') -- +3 years 286 < abstime 'Jan 14 14:00:00 1977'; 287 288SELECT '' AS three, * FROM ABSTIME_TBL 289 WHERE (ABSTIME_TBL.f1 + reltime '@ 3 year ago') -- -3 years 290 < abstime 'Jan 14 14:00:00 1971'; 291 292SELECT '' AS three, * FROM ABSTIME_TBL 293 WHERE (ABSTIME_TBL.f1 - reltime '@ 3 year') -- -(+3) years 294 < abstime 'Jan 14 14:00:00 1971'; 295 296SELECT '' AS three, * FROM ABSTIME_TBL 297 WHERE (ABSTIME_TBL.f1 - reltime '@ 3 year ago') -- -(-3) years 298 < abstime 'Jan 14 14:00:00 1977'; 299 300-- 301-- Conversions 302-- 303 304SELECT '' AS "16", f1 AS "timestamp", date(f1) AS date 305 FROM TEMP_TIMESTAMP 306 WHERE f1 <> timestamp 'now' 307 ORDER BY date, "timestamp"; 308 309SELECT '' AS "16", f1 AS "timestamp", abstime(f1) AS abstime 310 FROM TEMP_TIMESTAMP 311 ORDER BY abstime; 312 313SELECT '' AS four, f1 AS abstime, date(f1) AS date 314 FROM ABSTIME_TBL 315 WHERE isfinite(f1) AND f1 <> abstime 'now' 316 ORDER BY date, abstime; 317 318SELECT '' AS two, d1 AS "timestamp", abstime(d1) AS abstime 319 FROM TIMESTAMP_TBL WHERE NOT isfinite(d1); 320 321SELECT '' AS three, f1 as abstime, cast(f1 as timestamp) AS "timestamp" 322 FROM ABSTIME_TBL WHERE NOT isfinite(f1); 323 324SELECT '' AS ten, f1 AS interval, reltime(f1) AS reltime 325 FROM INTERVAL_TBL; 326 327SELECT '' AS six, f1 as reltime, CAST(f1 AS interval) AS interval 328 FROM RELTIME_TBL; 329 330DROP TABLE TEMP_TIMESTAMP; 331 332-- 333-- Formats 334-- 335 336SET DateStyle TO 'US,Postgres'; 337 338SHOW DateStyle; 339 340SELECT '' AS "64", d1 AS us_postgres FROM TIMESTAMP_TBL; 341 342SELECT '' AS seven, f1 AS us_postgres FROM ABSTIME_TBL; 343 344SET DateStyle TO 'US,ISO'; 345 346SELECT '' AS "64", d1 AS us_iso FROM TIMESTAMP_TBL; 347 348SELECT '' AS seven, f1 AS us_iso FROM ABSTIME_TBL; 349 350SET DateStyle TO 'US,SQL'; 351 352SHOW DateStyle; 353 354SELECT '' AS "64", d1 AS us_sql FROM TIMESTAMP_TBL; 355 356SELECT '' AS seven, f1 AS us_sql FROM ABSTIME_TBL; 357 358SET DateStyle TO 'European,Postgres'; 359 360SHOW DateStyle; 361 362INSERT INTO TIMESTAMP_TBL VALUES('13/06/1957'); 363 364SELECT count(*) as one FROM TIMESTAMP_TBL WHERE d1 = 'Jun 13 1957'; 365 366SELECT '' AS "65", d1 AS european_postgres FROM TIMESTAMP_TBL; 367 368SELECT '' AS seven, f1 AS european_postgres FROM ABSTIME_TBL; 369 370SET DateStyle TO 'European,ISO'; 371 372SHOW DateStyle; 373 374SELECT '' AS "65", d1 AS european_iso FROM TIMESTAMP_TBL; 375 376SELECT '' AS seven, f1 AS european_iso FROM ABSTIME_TBL; 377 378SET DateStyle TO 'European,SQL'; 379 380SHOW DateStyle; 381 382SELECT '' AS "65", d1 AS european_sql FROM TIMESTAMP_TBL; 383 384SELECT '' AS seven, f1 AS european_sql FROM ABSTIME_TBL; 385 386RESET DateStyle; 387 388-- 389-- to_timestamp() 390-- 391 392SELECT to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS'); 393 394SELECT to_timestamp('97/2/16 8:14:30', 'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS'); 395 396SELECT to_timestamp('1985 January 12', 'YYYY FMMonth DD'); 397 398SELECT to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16', 399 '"My birthday-> Year" YYYY, "Month:" FMMonth, "Day:" DD'); 400 401SELECT to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD'); 402 403SELECT to_timestamp('15 "text between quote marks" 98 54 45', 404 E'HH24 "\\text between quote marks\\"" YY MI SS'); 405 406SELECT to_timestamp('05121445482000', 'MMDDHH24MISSYYYY'); 407 408SELECT to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay'); 409 410SELECT to_timestamp('97/Feb/16', 'YYMonDD'); 411 412SELECT to_timestamp('19971116', 'YYYYMMDD'); 413 414SELECT to_timestamp('20000-1116', 'YYYY-MMDD'); 415 416SELECT to_timestamp('1997 A.D. 11 16', 'YYYY B.C. MM DD'); 417SELECT to_timestamp('1997 B.C. 11 16', 'YYYY B.C. MM DD'); 418 419SELECT to_timestamp('9-1116', 'Y-MMDD'); 420 421SELECT to_timestamp('95-1116', 'YY-MMDD'); 422 423SELECT to_timestamp('995-1116', 'YYY-MMDD'); 424 425SELECT to_timestamp('2005426', 'YYYYWWD'); 426 427SELECT to_timestamp('2005300', 'YYYYDDD'); 428 429SELECT to_timestamp('2005527', 'IYYYIWID'); 430 431SELECT to_timestamp('005527', 'IYYIWID'); 432 433SELECT to_timestamp('05527', 'IYIWID'); 434 435SELECT to_timestamp('5527', 'IIWID'); 436 437SELECT to_timestamp('2005364', 'IYYYIDDD'); 438 439SELECT to_timestamp('20050302', 'YYYYMMDD'); 440 441SELECT to_timestamp('2005 03 02', 'YYYYMMDD'); 442 443SELECT to_timestamp(' 2005 03 02', 'YYYYMMDD'); 444 445SELECT to_timestamp(' 20050302', 'YYYYMMDD'); 446 447SELECT to_timestamp('2011-12-18 11:38 A.M.', 'YYYY-MM-DD HH12:MI P.M.'); 448SELECT to_timestamp('2011-12-18 11:38 P.M.', 'YYYY-MM-DD HH12:MI P.M.'); 449 450SELECT to_timestamp('2011-12-18 11:38 PST', 'YYYY-MM-DD HH12:MI TZ'); -- NYI 451 452SELECT to_timestamp('2018-11-02 12:34:56.025', 'YYYY-MM-DD HH24:MI:SS.MS'); 453 454SELECT to_date('1 4 1902', 'Q MM YYYY'); -- Q is ignored 455SELECT to_date('3 4 21 01', 'W MM CC YY'); 456SELECT to_date('2458872', 'J'); 457 458-- 459-- Check handling of BC dates 460-- 461 462SELECT to_date('44-02-01 BC','YYYY-MM-DD BC'); 463SELECT to_date('-44-02-01','YYYY-MM-DD'); 464SELECT to_date('-44-02-01 BC','YYYY-MM-DD BC'); 465SELECT to_timestamp('44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC'); 466SELECT to_timestamp('-44-02-01 11:12:13','YYYY-MM-DD HH24:MI:SS'); 467SELECT to_timestamp('-44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC'); 468 469-- 470-- Check handling of multiple spaces in format and/or input 471-- 472 473SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 474SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 475SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 476 477SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 478SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 479SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 480 481SELECT to_date('2011 12 18', 'YYYY MM DD'); 482SELECT to_date('2011 12 18', 'YYYY MM DD'); 483SELECT to_date('2011 12 18', 'YYYY MM DD'); 484 485SELECT to_date('2011 12 18', 'YYYY MM DD'); 486SELECT to_date('2011 12 18', 'YYYY MM DD'); 487SELECT to_date('2011 12 18', 'YYYY MM DD'); 488 489-- 490-- Check errors for some incorrect usages of to_timestamp() 491-- 492 493-- Mixture of date conventions (ISO week and Gregorian): 494SELECT to_timestamp('2005527', 'YYYYIWID'); 495 496-- Insufficient characters in the source string: 497SELECT to_timestamp('19971', 'YYYYMMDD'); 498 499-- Insufficient digit characters for a single node: 500SELECT to_timestamp('19971)24', 'YYYYMMDD'); 501 502-- We don't accept full-length day or month names if short form is specified: 503SELECT to_timestamp('Friday 1-January-1999', 'DY DD MON YYYY'); 504SELECT to_timestamp('Fri 1-January-1999', 'DY DD MON YYYY'); 505SELECT to_timestamp('Fri 1-Jan-1999', 'DY DD MON YYYY'); -- ok 506 507-- Value clobbering: 508SELECT to_timestamp('1997-11-Jan-16', 'YYYY-MM-Mon-DD'); 509 510-- Non-numeric input: 511SELECT to_timestamp('199711xy', 'YYYYMMDD'); 512 513-- Input that doesn't fit in an int: 514SELECT to_timestamp('10000000000', 'FMYYYY'); 515 516-- 517-- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572) 518-- 519 520SET TIME ZONE 'America/New_York'; 521SET TIME ZONE '-1.5'; 522 523SHOW TIME ZONE; 524 525SELECT '2012-12-12 12:00'::timestamptz; 526SELECT '2012-12-12 12:00 America/New_York'::timestamptz; 527 528SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ'); 529 530RESET TIME ZONE; 531 532-- 533-- Drop tables that we don't want to keep because they interfere with 534-- testing pg_upgrade to v12 and up 535-- 536 537DROP TABLE abstime_tbl; 538DROP TABLE reltime_tbl; 539DROP TABLE tinterval_tbl; 540