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