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-- Conversions 273-- 274 275SELECT '' AS "16", f1 AS "timestamp", date(f1) AS date 276 FROM TEMP_TIMESTAMP 277 WHERE f1 <> timestamp 'now' 278 ORDER BY date, "timestamp"; 279 280DROP TABLE TEMP_TIMESTAMP; 281 282-- 283-- Formats 284-- 285 286SET DateStyle TO 'US,Postgres'; 287 288SHOW DateStyle; 289 290SELECT '' AS "64", d1 AS us_postgres FROM TIMESTAMP_TBL; 291 292SET DateStyle TO 'US,ISO'; 293 294SELECT '' AS "64", d1 AS us_iso FROM TIMESTAMP_TBL; 295 296SET DateStyle TO 'US,SQL'; 297 298SHOW DateStyle; 299 300SELECT '' AS "64", d1 AS us_sql FROM TIMESTAMP_TBL; 301 302SET DateStyle TO 'European,Postgres'; 303 304SHOW DateStyle; 305 306INSERT INTO TIMESTAMP_TBL VALUES('13/06/1957'); 307 308SELECT count(*) as one FROM TIMESTAMP_TBL WHERE d1 = 'Jun 13 1957'; 309 310SELECT '' AS "65", d1 AS european_postgres FROM TIMESTAMP_TBL; 311 312SET DateStyle TO 'European,ISO'; 313 314SHOW DateStyle; 315 316SELECT '' AS "65", d1 AS european_iso FROM TIMESTAMP_TBL; 317 318SET DateStyle TO 'European,SQL'; 319 320SHOW DateStyle; 321 322SELECT '' AS "65", d1 AS european_sql FROM TIMESTAMP_TBL; 323 324RESET DateStyle; 325 326-- 327-- to_timestamp() 328-- 329 330SELECT to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS'); 331 332SELECT to_timestamp('97/2/16 8:14:30', 'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS'); 333 334SELECT to_timestamp('2011$03!18 23_38_15', 'YYYY-MM-DD HH24:MI:SS'); 335 336SELECT to_timestamp('1985 January 12', 'YYYY FMMonth DD'); 337 338SELECT to_timestamp('1985 FMMonth 12', 'YYYY "FMMonth" DD'); 339 340SELECT to_timestamp('1985 \ 12', 'YYYY \\ DD'); 341 342SELECT to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16', 343 '"My birthday-> Year:" YYYY, "Month:" FMMonth, "Day:" DD'); 344 345SELECT to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD'); 346 347SELECT to_timestamp('15 "text between quote marks" 98 54 45', 348 E'HH24 "\\"text between quote marks\\"" YY MI SS'); 349 350SELECT to_timestamp('05121445482000', 'MMDDHH24MISSYYYY'); 351 352SELECT to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay'); 353 354SELECT to_timestamp('97/Feb/16', 'YYMonDD'); 355 356SELECT to_timestamp('97/Feb/16', 'YY:Mon:DD'); 357 358SELECT to_timestamp('97/Feb/16', 'FXYY:Mon:DD'); 359 360SELECT to_timestamp('97/Feb/16', 'FXYY/Mon/DD'); 361 362SELECT to_timestamp('19971116', 'YYYYMMDD'); 363 364SELECT to_timestamp('20000-1116', 'YYYY-MMDD'); 365 366SELECT to_timestamp('1997 AD 11 16', 'YYYY BC MM DD'); 367SELECT to_timestamp('1997 BC 11 16', 'YYYY BC MM DD'); 368 369SELECT to_timestamp('1997 A.D. 11 16', 'YYYY B.C. MM DD'); 370SELECT to_timestamp('1997 B.C. 11 16', 'YYYY B.C. MM DD'); 371 372SELECT to_timestamp('9-1116', 'Y-MMDD'); 373 374SELECT to_timestamp('95-1116', 'YY-MMDD'); 375 376SELECT to_timestamp('995-1116', 'YYY-MMDD'); 377 378SELECT to_timestamp('2005426', 'YYYYWWD'); 379 380SELECT to_timestamp('2005300', 'YYYYDDD'); 381 382SELECT to_timestamp('2005527', 'IYYYIWID'); 383 384SELECT to_timestamp('005527', 'IYYIWID'); 385 386SELECT to_timestamp('05527', 'IYIWID'); 387 388SELECT to_timestamp('5527', 'IIWID'); 389 390SELECT to_timestamp('2005364', 'IYYYIDDD'); 391 392SELECT to_timestamp('20050302', 'YYYYMMDD'); 393 394SELECT to_timestamp('2005 03 02', 'YYYYMMDD'); 395 396SELECT to_timestamp(' 2005 03 02', 'YYYYMMDD'); 397 398SELECT to_timestamp(' 20050302', 'YYYYMMDD'); 399 400SELECT to_timestamp('2011-12-18 11:38 AM', 'YYYY-MM-DD HH12:MI PM'); 401SELECT to_timestamp('2011-12-18 11:38 PM', 'YYYY-MM-DD HH12:MI PM'); 402 403SELECT to_timestamp('2011-12-18 11:38 A.M.', 'YYYY-MM-DD HH12:MI P.M.'); 404SELECT to_timestamp('2011-12-18 11:38 P.M.', 'YYYY-MM-DD HH12:MI P.M.'); 405 406SELECT to_timestamp('2011-12-18 11:38 +05', 'YYYY-MM-DD HH12:MI TZH'); 407SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI TZH'); 408SELECT to_timestamp('2011-12-18 11:38 +05:20', 'YYYY-MM-DD HH12:MI TZH:TZM'); 409SELECT to_timestamp('2011-12-18 11:38 -05:20', 'YYYY-MM-DD HH12:MI TZH:TZM'); 410SELECT to_timestamp('2011-12-18 11:38 20', 'YYYY-MM-DD HH12:MI TZM'); 411 412SELECT to_timestamp('2011-12-18 11:38 PST', 'YYYY-MM-DD HH12:MI TZ'); -- NYI 413 414SELECT to_timestamp('2018-11-02 12:34:56.025', 'YYYY-MM-DD HH24:MI:SS.MS'); 415 416SELECT to_date('1 4 1902', 'Q MM YYYY'); -- Q is ignored 417SELECT to_date('3 4 21 01', 'W MM CC YY'); 418SELECT to_date('2458872', 'J'); 419 420-- 421-- Check handling of BC dates 422-- 423 424SELECT to_date('44-02-01 BC','YYYY-MM-DD BC'); 425SELECT to_date('-44-02-01','YYYY-MM-DD'); 426SELECT to_date('-44-02-01 BC','YYYY-MM-DD BC'); 427SELECT to_timestamp('44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC'); 428SELECT to_timestamp('-44-02-01 11:12:13','YYYY-MM-DD HH24:MI:SS'); 429SELECT to_timestamp('-44-02-01 11:12:13 BC','YYYY-MM-DD HH24:MI:SS BC'); 430 431-- 432-- Check handling of multiple spaces in format and/or input 433-- 434 435SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 436SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 437SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 438 439SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 440SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 441SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS'); 442 443SELECT to_timestamp('2000+ JUN', 'YYYY/MON'); 444SELECT to_timestamp(' 2000 +JUN', 'YYYY/MON'); 445SELECT to_timestamp(' 2000 +JUN', 'YYYY//MON'); 446SELECT to_timestamp('2000 +JUN', 'YYYY//MON'); 447SELECT to_timestamp('2000 + JUN', 'YYYY MON'); 448SELECT to_timestamp('2000 ++ JUN', 'YYYY MON'); 449SELECT to_timestamp('2000 + + JUN', 'YYYY MON'); 450SELECT to_timestamp('2000 + + JUN', 'YYYY MON'); 451SELECT to_timestamp('2000 -10', 'YYYY TZH'); 452SELECT to_timestamp('2000 -10', 'YYYY TZH'); 453 454SELECT to_date('2011 12 18', 'YYYY MM DD'); 455SELECT to_date('2011 12 18', 'YYYY MM DD'); 456SELECT to_date('2011 12 18', 'YYYY MM DD'); 457 458SELECT to_date('2011 12 18', 'YYYY MM DD'); 459SELECT to_date('2011 12 18', 'YYYY MM DD'); 460SELECT to_date('2011 12 18', 'YYYY MM DD'); 461 462SELECT to_date('2011 12 18', 'YYYYxMMxDD'); 463SELECT to_date('2011x 12x 18', 'YYYYxMMxDD'); 464SELECT to_date('2011 x12 x18', 'YYYYxMMxDD'); 465 466-- 467-- Check errors for some incorrect usages of to_timestamp() and to_date() 468-- 469 470-- Mixture of date conventions (ISO week and Gregorian): 471SELECT to_timestamp('2005527', 'YYYYIWID'); 472 473-- Insufficient characters in the source string: 474SELECT to_timestamp('19971', 'YYYYMMDD'); 475 476-- Insufficient digit characters for a single node: 477SELECT to_timestamp('19971)24', 'YYYYMMDD'); 478 479-- We don't accept full-length day or month names if short form is specified: 480SELECT to_timestamp('Friday 1-January-1999', 'DY DD MON YYYY'); 481SELECT to_timestamp('Fri 1-January-1999', 'DY DD MON YYYY'); 482SELECT to_timestamp('Fri 1-Jan-1999', 'DY DD MON YYYY'); -- ok 483 484-- Value clobbering: 485SELECT to_timestamp('1997-11-Jan-16', 'YYYY-MM-Mon-DD'); 486 487-- Non-numeric input: 488SELECT to_timestamp('199711xy', 'YYYYMMDD'); 489 490-- Input that doesn't fit in an int: 491SELECT to_timestamp('10000000000', 'FMYYYY'); 492 493-- Out-of-range and not-quite-out-of-range fields: 494SELECT to_timestamp('2016-06-13 25:00:00', 'YYYY-MM-DD HH24:MI:SS'); 495SELECT to_timestamp('2016-06-13 15:60:00', 'YYYY-MM-DD HH24:MI:SS'); 496SELECT to_timestamp('2016-06-13 15:50:60', 'YYYY-MM-DD HH24:MI:SS'); 497SELECT to_timestamp('2016-06-13 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); -- ok 498SELECT to_timestamp('2016-06-13 15:50:55', 'YYYY-MM-DD HH:MI:SS'); 499SELECT to_timestamp('2016-13-01 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); 500SELECT to_timestamp('2016-02-30 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); 501SELECT to_timestamp('2016-02-29 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); -- ok 502SELECT to_timestamp('2015-02-29 15:50:55', 'YYYY-MM-DD HH24:MI:SS'); 503SELECT to_timestamp('2015-02-11 86000', 'YYYY-MM-DD SSSS'); -- ok 504SELECT to_timestamp('2015-02-11 86400', 'YYYY-MM-DD SSSS'); 505SELECT to_date('2016-13-10', 'YYYY-MM-DD'); 506SELECT to_date('2016-02-30', 'YYYY-MM-DD'); 507SELECT to_date('2016-02-29', 'YYYY-MM-DD'); -- ok 508SELECT to_date('2015-02-29', 'YYYY-MM-DD'); 509SELECT to_date('2015 365', 'YYYY DDD'); -- ok 510SELECT to_date('2015 366', 'YYYY DDD'); 511SELECT to_date('2016 365', 'YYYY DDD'); -- ok 512SELECT to_date('2016 366', 'YYYY DDD'); -- ok 513SELECT to_date('2016 367', 'YYYY DDD'); 514SELECT to_date('0000-02-01','YYYY-MM-DD'); -- allowed, though it shouldn't be 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