aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/test/regress/expected/horology.out1400
-rw-r--r--src/test/regress/expected/interval.out (renamed from src/test/regress/expected/timespan.out)64
-rw-r--r--src/test/regress/expected/timestamp.out (renamed from src/test/regress/expected/datetime.out)255
-rw-r--r--src/test/regress/output/misc.source4
-rw-r--r--src/test/regress/sql/datetime.sql165
-rw-r--r--src/test/regress/sql/horology.sql156
-rw-r--r--src/test/regress/sql/interval.sql48
-rw-r--r--src/test/regress/sql/run_check.tests6
-rw-r--r--src/test/regress/sql/tests4
-rw-r--r--src/test/regress/sql/timespan.sql48
-rw-r--r--src/test/regress/sql/timestamp.sql169
11 files changed, 909 insertions, 1410 deletions
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 81587675ad1..4403e3ff033 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -2,403 +2,446 @@
-- HOROLOGY
--
--
--- datetime, timespan arithmetic
+-- timestamp, interval arithmetic
--
-SELECT datetime '1996-03-01' - timespan '1 second' AS "Feb 29";
+SELECT timestamp '1996-03-01' - interval '1 second' AS "Feb 29";
Feb 29
------------------------------
Thu Feb 29 23:59:59 1996 PST
(1 row)
-SELECT datetime '1999-03-01' - timespan '1 second' AS "Feb 28";
+SELECT timestamp '1999-03-01' - interval '1 second' AS "Feb 28";
Feb 28
------------------------------
Sun Feb 28 23:59:59 1999 PST
(1 row)
-SELECT datetime '2000-03-01' - timespan '1 second' AS "Feb 29";
+SELECT timestamp '2000-03-01' - interval '1 second' AS "Feb 29";
Feb 29
------------------------------
Tue Feb 29 23:59:59 2000 PST
(1 row)
-SELECT datetime '1999-12-01' + timespan '1 month - 1 second' AS "Dec 31";
+SELECT timestamp '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
Dec 31
------------------------------
Fri Dec 31 23:59:59 1999 PST
(1 row)
-CREATE TABLE TEMP_DATETIME (f1 datetime);
+CREATE TABLE TEMP_TIMESTAMP (f1 timestamp);
-- get some candidate input values
-INSERT INTO TEMP_DATETIME (f1)
- SELECT d1 FROM DATETIME_TBL
+INSERT INTO TEMP_TIMESTAMP (f1)
+ SELECT d1 FROM TIMESTAMP_TBL
WHERE d1 BETWEEN '13-jun-1957' AND '1-jan-1997'
OR d1 BETWEEN '1-jan-1999' AND '1-jan-2010';
-SELECT '' AS ten, f1 AS datetime
- FROM TEMP_DATETIME
- ORDER BY datetime;
- ten | datetime
------+------------------------------
- | epoch
- | Wed Feb 28 17:32:01 1996 PST
- | Thu Feb 29 17:32:01 1996 PST
- | Fri Mar 01 17:32:01 1996 PST
- | Mon Dec 30 17:32:01 1996 PST
- | Tue Dec 31 17:32:01 1996 PST
- | Fri Dec 31 17:32:01 1999 PST
- | Sat Jan 01 17:32:01 2000 PST
- | Sun Dec 31 17:32:01 2000 PST
- | Mon Jan 01 17:32:01 2001 PST
-(10 rows)
+SELECT '' AS "11", f1 AS timestamp
+ FROM TEMP_TIMESTAMP
+ ORDER BY timestamp;
+ 11 | timestamp
+----+------------------------------
+ | epoch
+ | Wed Feb 28 17:32:01 1996 PST
+ | Thu Feb 29 17:32:01 1996 PST
+ | Fri Mar 01 17:32:01 1996 PST
+ | Mon Dec 30 17:32:01 1996 PST
+ | Tue Dec 31 17:32:01 1996 PST
+ | Fri Dec 31 17:32:01 1999 PST
+ | Sat Jan 01 17:32:01 2000 PST
+ | Tue Feb 15 23:14:30 2000 PST
+ | Sun Dec 31 17:32:01 2000 PST
+ | Mon Jan 01 17:32:01 2001 PST
+(11 rows)
-SELECT '' AS hundred, d.f1 AS datetime, t.f1 AS timespan, d.f1 + t.f1 AS plus
- FROM TEMP_DATETIME d, TIMESPAN_TBL t
- ORDER BY plus, datetime, timespan;
- hundred | datetime | timespan | plus
----------+------------------------------+-------------------------------+------------------------------
- | epoch | @ 14 secs ago | Wed Dec 31 15:59:46 1969 PST
- | epoch | @ 1 min | Wed Dec 31 16:01:00 1969 PST
- | epoch | @ 5 hours | Wed Dec 31 21:00:00 1969 PST
- | epoch | @ 1 day 2 hours 3 mins 4 secs | Thu Jan 01 18:03:04 1970 PST
- | epoch | @ 10 days | Sat Jan 10 16:00:00 1970 PST
- | epoch | @ 3 mons | Tue Mar 31 16:00:00 1970 PST
- | epoch | @ 5 mons | Sun May 31 17:00:00 1970 PDT
- | epoch | @ 5 mons 12 hours | Mon Jun 01 05:00:00 1970 PDT
- | epoch | @ 6 years | Wed Dec 31 16:00:00 1975 PST
- | Wed Feb 28 17:32:01 1996 PST | @ 14 secs ago | Wed Feb 28 17:31:47 1996 PST
- | Wed Feb 28 17:32:01 1996 PST | @ 1 min | Wed Feb 28 17:33:01 1996 PST
- | Wed Feb 28 17:32:01 1996 PST | @ 5 hours | Wed Feb 28 22:32:01 1996 PST
- | Thu Feb 29 17:32:01 1996 PST | @ 14 secs ago | Thu Feb 29 17:31:47 1996 PST
- | Thu Feb 29 17:32:01 1996 PST | @ 1 min | Thu Feb 29 17:33:01 1996 PST
- | Wed Feb 28 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Feb 29 19:35:05 1996 PST
- | Thu Feb 29 17:32:01 1996 PST | @ 5 hours | Thu Feb 29 22:32:01 1996 PST
- | Fri Mar 01 17:32:01 1996 PST | @ 14 secs ago | Fri Mar 01 17:31:47 1996 PST
- | Fri Mar 01 17:32:01 1996 PST | @ 1 min | Fri Mar 01 17:33:01 1996 PST
- | Thu Feb 29 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Fri Mar 01 19:35:05 1996 PST
- | Fri Mar 01 17:32:01 1996 PST | @ 5 hours | Fri Mar 01 22:32:01 1996 PST
- | Fri Mar 01 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Mar 02 19:35:05 1996 PST
- | Wed Feb 28 17:32:01 1996 PST | @ 10 days | Sat Mar 09 17:32:01 1996 PST
- | Thu Feb 29 17:32:01 1996 PST | @ 10 days | Sun Mar 10 17:32:01 1996 PST
- | Fri Mar 01 17:32:01 1996 PST | @ 10 days | Mon Mar 11 17:32:01 1996 PST
- | Wed Feb 28 17:32:01 1996 PST | @ 3 mons | Tue May 28 18:32:01 1996 PDT
- | Thu Feb 29 17:32:01 1996 PST | @ 3 mons | Wed May 29 18:32:01 1996 PDT
- | Fri Mar 01 17:32:01 1996 PST | @ 3 mons | Sat Jun 01 18:32:01 1996 PDT
- | Wed Feb 28 17:32:01 1996 PST | @ 5 mons | Sun Jul 28 18:32:01 1996 PDT
- | Wed Feb 28 17:32:01 1996 PST | @ 5 mons 12 hours | Mon Jul 29 06:32:01 1996 PDT
- | Thu Feb 29 17:32:01 1996 PST | @ 5 mons | Mon Jul 29 18:32:01 1996 PDT
- | Thu Feb 29 17:32:01 1996 PST | @ 5 mons 12 hours | Tue Jul 30 06:32:01 1996 PDT
- | Fri Mar 01 17:32:01 1996 PST | @ 5 mons | Thu Aug 01 18:32:01 1996 PDT
- | Fri Mar 01 17:32:01 1996 PST | @ 5 mons 12 hours | Fri Aug 02 06:32:01 1996 PDT
- | Mon Dec 30 17:32:01 1996 PST | @ 14 secs ago | Mon Dec 30 17:31:47 1996 PST
- | Mon Dec 30 17:32:01 1996 PST | @ 1 min | Mon Dec 30 17:33:01 1996 PST
- | Mon Dec 30 17:32:01 1996 PST | @ 5 hours | Mon Dec 30 22:32:01 1996 PST
- | Tue Dec 31 17:32:01 1996 PST | @ 14 secs ago | Tue Dec 31 17:31:47 1996 PST
- | Tue Dec 31 17:32:01 1996 PST | @ 1 min | Tue Dec 31 17:33:01 1996 PST
- | Mon Dec 30 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Dec 31 19:35:05 1996 PST
- | Tue Dec 31 17:32:01 1996 PST | @ 5 hours | Tue Dec 31 22:32:01 1996 PST
- | Tue Dec 31 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Wed Jan 01 19:35:05 1997 PST
- | Mon Dec 30 17:32:01 1996 PST | @ 10 days | Thu Jan 09 17:32:01 1997 PST
- | Tue Dec 31 17:32:01 1996 PST | @ 10 days | Fri Jan 10 17:32:01 1997 PST
- | Mon Dec 30 17:32:01 1996 PST | @ 3 mons | Sun Mar 30 17:32:01 1997 PST
- | Tue Dec 31 17:32:01 1996 PST | @ 3 mons | Mon Mar 31 17:32:01 1997 PST
- | Mon Dec 30 17:32:01 1996 PST | @ 5 mons | Fri May 30 18:32:01 1997 PDT
- | Mon Dec 30 17:32:01 1996 PST | @ 5 mons 12 hours | Sat May 31 06:32:01 1997 PDT
- | Tue Dec 31 17:32:01 1996 PST | @ 5 mons | Sat May 31 18:32:01 1997 PDT
- | Tue Dec 31 17:32:01 1996 PST | @ 5 mons 12 hours | Sun Jun 01 06:32:01 1997 PDT
- | Fri Dec 31 17:32:01 1999 PST | @ 14 secs ago | Fri Dec 31 17:31:47 1999 PST
- | Fri Dec 31 17:32:01 1999 PST | @ 1 min | Fri Dec 31 17:33:01 1999 PST
- | Fri Dec 31 17:32:01 1999 PST | @ 5 hours | Fri Dec 31 22:32:01 1999 PST
- | Sat Jan 01 17:32:01 2000 PST | @ 14 secs ago | Sat Jan 01 17:31:47 2000 PST
- | Sat Jan 01 17:32:01 2000 PST | @ 1 min | Sat Jan 01 17:33:01 2000 PST
- | Fri Dec 31 17:32:01 1999 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Jan 01 19:35:05 2000 PST
- | Sat Jan 01 17:32:01 2000 PST | @ 5 hours | Sat Jan 01 22:32:01 2000 PST
- | Sat Jan 01 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Jan 02 19:35:05 2000 PST
- | Fri Dec 31 17:32:01 1999 PST | @ 10 days | Mon Jan 10 17:32:01 2000 PST
- | Sat Jan 01 17:32:01 2000 PST | @ 10 days | Tue Jan 11 17:32:01 2000 PST
- | Fri Dec 31 17:32:01 1999 PST | @ 3 mons | Fri Mar 31 17:32:01 2000 PST
- | Sat Jan 01 17:32:01 2000 PST | @ 3 mons | Sat Apr 01 17:32:01 2000 PST
- | Fri Dec 31 17:32:01 1999 PST | @ 5 mons | Wed May 31 18:32:01 2000 PDT
- | Fri Dec 31 17:32:01 1999 PST | @ 5 mons 12 hours | Thu Jun 01 06:32:01 2000 PDT
- | Sat Jan 01 17:32:01 2000 PST | @ 5 mons | Thu Jun 01 18:32:01 2000 PDT
- | Sat Jan 01 17:32:01 2000 PST | @ 5 mons 12 hours | Fri Jun 02 06:32:01 2000 PDT
- | Sun Dec 31 17:32:01 2000 PST | @ 14 secs ago | Sun Dec 31 17:31:47 2000 PST
- | Sun Dec 31 17:32:01 2000 PST | @ 1 min | Sun Dec 31 17:33:01 2000 PST
- | Sun Dec 31 17:32:01 2000 PST | @ 5 hours | Sun Dec 31 22:32:01 2000 PST
- | Mon Jan 01 17:32:01 2001 PST | @ 14 secs ago | Mon Jan 01 17:31:47 2001 PST
- | Mon Jan 01 17:32:01 2001 PST | @ 1 min | Mon Jan 01 17:33:01 2001 PST
- | Sun Dec 31 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Mon Jan 01 19:35:05 2001 PST
- | Mon Jan 01 17:32:01 2001 PST | @ 5 hours | Mon Jan 01 22:32:01 2001 PST
- | Mon Jan 01 17:32:01 2001 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Jan 02 19:35:05 2001 PST
- | Sun Dec 31 17:32:01 2000 PST | @ 10 days | Wed Jan 10 17:32:01 2001 PST
- | Mon Jan 01 17:32:01 2001 PST | @ 10 days | Thu Jan 11 17:32:01 2001 PST
- | Sun Dec 31 17:32:01 2000 PST | @ 3 mons | Sat Mar 31 17:32:01 2001 PST
- | Mon Jan 01 17:32:01 2001 PST | @ 3 mons | Sun Apr 01 18:32:01 2001 PDT
- | Sun Dec 31 17:32:01 2000 PST | @ 5 mons | Thu May 31 18:32:01 2001 PDT
- | Sun Dec 31 17:32:01 2000 PST | @ 5 mons 12 hours | Fri Jun 01 06:32:01 2001 PDT
- | Mon Jan 01 17:32:01 2001 PST | @ 5 mons | Fri Jun 01 18:32:01 2001 PDT
- | Mon Jan 01 17:32:01 2001 PST | @ 5 mons 12 hours | Sat Jun 02 06:32:01 2001 PDT
- | Wed Feb 28 17:32:01 1996 PST | @ 6 years | Thu Feb 28 17:32:01 2002 PST
- | Thu Feb 29 17:32:01 1996 PST | @ 6 years | Thu Feb 28 17:32:01 2002 PST
- | Fri Mar 01 17:32:01 1996 PST | @ 6 years | Fri Mar 01 17:32:01 2002 PST
- | Mon Dec 30 17:32:01 1996 PST | @ 6 years | Mon Dec 30 17:32:01 2002 PST
- | Tue Dec 31 17:32:01 1996 PST | @ 6 years | Tue Dec 31 17:32:01 2002 PST
- | epoch | @ 34 years | Wed Dec 31 16:00:00 2003 PST
- | Fri Dec 31 17:32:01 1999 PST | @ 6 years | Sat Dec 31 17:32:01 2005 PST
- | Sat Jan 01 17:32:01 2000 PST | @ 6 years | Sun Jan 01 17:32:01 2006 PST
- | Sun Dec 31 17:32:01 2000 PST | @ 6 years | Sun Dec 31 17:32:01 2006 PST
- | Mon Jan 01 17:32:01 2001 PST | @ 6 years | Mon Jan 01 17:32:01 2007 PST
- | Wed Feb 28 17:32:01 1996 PST | @ 34 years | Thu Feb 28 17:32:01 2030 PST
- | Thu Feb 29 17:32:01 1996 PST | @ 34 years | Thu Feb 28 17:32:01 2030 PST
- | Fri Mar 01 17:32:01 1996 PST | @ 34 years | Fri Mar 01 17:32:01 2030 PST
- | Mon Dec 30 17:32:01 1996 PST | @ 34 years | Mon Dec 30 17:32:01 2030 PST
- | Tue Dec 31 17:32:01 1996 PST | @ 34 years | Tue Dec 31 17:32:01 2030 PST
- | Fri Dec 31 17:32:01 1999 PST | @ 34 years | Sat Dec 31 17:32:01 2033 PST
- | Sat Jan 01 17:32:01 2000 PST | @ 34 years | Sun Jan 01 17:32:01 2034 PST
- | Sun Dec 31 17:32:01 2000 PST | @ 34 years | Sun Dec 31 17:32:01 2034 PST
- | Mon Jan 01 17:32:01 2001 PST | @ 34 years | Mon Jan 01 17:32:01 2035 PST
-(100 rows)
+SELECT '' AS "110", d.f1 AS timestamp, t.f1 AS interval, d.f1 + t.f1 AS plus
+ FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
+ ORDER BY plus, timestamp, interval;
+ 110 | timestamp | interval | plus
+-----+------------------------------+-------------------------------+------------------------------
+ | epoch | @ 14 secs ago | Wed Dec 31 15:59:46 1969 PST
+ | epoch | @ 1 min | Wed Dec 31 16:01:00 1969 PST
+ | epoch | @ 5 hours | Wed Dec 31 21:00:00 1969 PST
+ | epoch | @ 1 day 2 hours 3 mins 4 secs | Thu Jan 01 18:03:04 1970 PST
+ | epoch | @ 10 days | Sat Jan 10 16:00:00 1970 PST
+ | epoch | @ 3 mons | Tue Mar 31 16:00:00 1970 PST
+ | epoch | @ 5 mons | Sun May 31 17:00:00 1970 PDT
+ | epoch | @ 5 mons 12 hours | Mon Jun 01 05:00:00 1970 PDT
+ | epoch | @ 6 years | Wed Dec 31 16:00:00 1975 PST
+ | Wed Feb 28 17:32:01 1996 PST | @ 14 secs ago | Wed Feb 28 17:31:47 1996 PST
+ | Wed Feb 28 17:32:01 1996 PST | @ 1 min | Wed Feb 28 17:33:01 1996 PST
+ | Wed Feb 28 17:32:01 1996 PST | @ 5 hours | Wed Feb 28 22:32:01 1996 PST
+ | Thu Feb 29 17:32:01 1996 PST | @ 14 secs ago | Thu Feb 29 17:31:47 1996 PST
+ | Thu Feb 29 17:32:01 1996 PST | @ 1 min | Thu Feb 29 17:33:01 1996 PST
+ | Wed Feb 28 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Feb 29 19:35:05 1996 PST
+ | Thu Feb 29 17:32:01 1996 PST | @ 5 hours | Thu Feb 29 22:32:01 1996 PST
+ | Fri Mar 01 17:32:01 1996 PST | @ 14 secs ago | Fri Mar 01 17:31:47 1996 PST
+ | Fri Mar 01 17:32:01 1996 PST | @ 1 min | Fri Mar 01 17:33:01 1996 PST
+ | Thu Feb 29 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Fri Mar 01 19:35:05 1996 PST
+ | Fri Mar 01 17:32:01 1996 PST | @ 5 hours | Fri Mar 01 22:32:01 1996 PST
+ | Fri Mar 01 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Mar 02 19:35:05 1996 PST
+ | Wed Feb 28 17:32:01 1996 PST | @ 10 days | Sat Mar 09 17:32:01 1996 PST
+ | Thu Feb 29 17:32:01 1996 PST | @ 10 days | Sun Mar 10 17:32:01 1996 PST
+ | Fri Mar 01 17:32:01 1996 PST | @ 10 days | Mon Mar 11 17:32:01 1996 PST
+ | Wed Feb 28 17:32:01 1996 PST | @ 3 mons | Tue May 28 18:32:01 1996 PDT
+ | Thu Feb 29 17:32:01 1996 PST | @ 3 mons | Wed May 29 18:32:01 1996 PDT
+ | Fri Mar 01 17:32:01 1996 PST | @ 3 mons | Sat Jun 01 18:32:01 1996 PDT
+ | Wed Feb 28 17:32:01 1996 PST | @ 5 mons | Sun Jul 28 18:32:01 1996 PDT
+ | Wed Feb 28 17:32:01 1996 PST | @ 5 mons 12 hours | Mon Jul 29 06:32:01 1996 PDT
+ | Thu Feb 29 17:32:01 1996 PST | @ 5 mons | Mon Jul 29 18:32:01 1996 PDT
+ | Thu Feb 29 17:32:01 1996 PST | @ 5 mons 12 hours | Tue Jul 30 06:32:01 1996 PDT
+ | Fri Mar 01 17:32:01 1996 PST | @ 5 mons | Thu Aug 01 18:32:01 1996 PDT
+ | Fri Mar 01 17:32:01 1996 PST | @ 5 mons 12 hours | Fri Aug 02 06:32:01 1996 PDT
+ | Mon Dec 30 17:32:01 1996 PST | @ 14 secs ago | Mon Dec 30 17:31:47 1996 PST
+ | Mon Dec 30 17:32:01 1996 PST | @ 1 min | Mon Dec 30 17:33:01 1996 PST
+ | Mon Dec 30 17:32:01 1996 PST | @ 5 hours | Mon Dec 30 22:32:01 1996 PST
+ | Tue Dec 31 17:32:01 1996 PST | @ 14 secs ago | Tue Dec 31 17:31:47 1996 PST
+ | Tue Dec 31 17:32:01 1996 PST | @ 1 min | Tue Dec 31 17:33:01 1996 PST
+ | Mon Dec 30 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Dec 31 19:35:05 1996 PST
+ | Tue Dec 31 17:32:01 1996 PST | @ 5 hours | Tue Dec 31 22:32:01 1996 PST
+ | Tue Dec 31 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Wed Jan 01 19:35:05 1997 PST
+ | Mon Dec 30 17:32:01 1996 PST | @ 10 days | Thu Jan 09 17:32:01 1997 PST
+ | Tue Dec 31 17:32:01 1996 PST | @ 10 days | Fri Jan 10 17:32:01 1997 PST
+ | Mon Dec 30 17:32:01 1996 PST | @ 3 mons | Sun Mar 30 17:32:01 1997 PST
+ | Tue Dec 31 17:32:01 1996 PST | @ 3 mons | Mon Mar 31 17:32:01 1997 PST
+ | Mon Dec 30 17:32:01 1996 PST | @ 5 mons | Fri May 30 18:32:01 1997 PDT
+ | Mon Dec 30 17:32:01 1996 PST | @ 5 mons 12 hours | Sat May 31 06:32:01 1997 PDT
+ | Tue Dec 31 17:32:01 1996 PST | @ 5 mons | Sat May 31 18:32:01 1997 PDT
+ | Tue Dec 31 17:32:01 1996 PST | @ 5 mons 12 hours | Sun Jun 01 06:32:01 1997 PDT
+ | Fri Dec 31 17:32:01 1999 PST | @ 14 secs ago | Fri Dec 31 17:31:47 1999 PST
+ | Fri Dec 31 17:32:01 1999 PST | @ 1 min | Fri Dec 31 17:33:01 1999 PST
+ | Fri Dec 31 17:32:01 1999 PST | @ 5 hours | Fri Dec 31 22:32:01 1999 PST
+ | Sat Jan 01 17:32:01 2000 PST | @ 14 secs ago | Sat Jan 01 17:31:47 2000 PST
+ | Sat Jan 01 17:32:01 2000 PST | @ 1 min | Sat Jan 01 17:33:01 2000 PST
+ | Fri Dec 31 17:32:01 1999 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Jan 01 19:35:05 2000 PST
+ | Sat Jan 01 17:32:01 2000 PST | @ 5 hours | Sat Jan 01 22:32:01 2000 PST
+ | Sat Jan 01 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Jan 02 19:35:05 2000 PST
+ | Fri Dec 31 17:32:01 1999 PST | @ 10 days | Mon Jan 10 17:32:01 2000 PST
+ | Sat Jan 01 17:32:01 2000 PST | @ 10 days | Tue Jan 11 17:32:01 2000 PST
+ | Tue Feb 15 23:14:30 2000 PST | @ 14 secs ago | Tue Feb 15 23:14:16 2000 PST
+ | Tue Feb 15 23:14:30 2000 PST | @ 1 min | Tue Feb 15 23:15:30 2000 PST
+ | Tue Feb 15 23:14:30 2000 PST | @ 5 hours | Wed Feb 16 04:14:30 2000 PST
+ | Tue Feb 15 23:14:30 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Feb 17 01:17:34 2000 PST
+ | Tue Feb 15 23:14:30 2000 PST | @ 10 days | Fri Feb 25 23:14:30 2000 PST
+ | Fri Dec 31 17:32:01 1999 PST | @ 3 mons | Fri Mar 31 17:32:01 2000 PST
+ | Sat Jan 01 17:32:01 2000 PST | @ 3 mons | Sat Apr 01 17:32:01 2000 PST
+ | Tue Feb 15 23:14:30 2000 PST | @ 3 mons | Tue May 16 00:14:30 2000 PDT
+ | Fri Dec 31 17:32:01 1999 PST | @ 5 mons | Wed May 31 18:32:01 2000 PDT
+ | Fri Dec 31 17:32:01 1999 PST | @ 5 mons 12 hours | Thu Jun 01 06:32:01 2000 PDT
+ | Sat Jan 01 17:32:01 2000 PST | @ 5 mons | Thu Jun 01 18:32:01 2000 PDT
+ | Sat Jan 01 17:32:01 2000 PST | @ 5 mons 12 hours | Fri Jun 02 06:32:01 2000 PDT
+ | Tue Feb 15 23:14:30 2000 PST | @ 5 mons | Sun Jul 16 00:14:30 2000 PDT
+ | Tue Feb 15 23:14:30 2000 PST | @ 5 mons 12 hours | Sun Jul 16 12:14:30 2000 PDT
+ | Sun Dec 31 17:32:01 2000 PST | @ 14 secs ago | Sun Dec 31 17:31:47 2000 PST
+ | Sun Dec 31 17:32:01 2000 PST | @ 1 min | Sun Dec 31 17:33:01 2000 PST
+ | Sun Dec 31 17:32:01 2000 PST | @ 5 hours | Sun Dec 31 22:32:01 2000 PST
+ | Mon Jan 01 17:32:01 2001 PST | @ 14 secs ago | Mon Jan 01 17:31:47 2001 PST
+ | Mon Jan 01 17:32:01 2001 PST | @ 1 min | Mon Jan 01 17:33:01 2001 PST
+ | Sun Dec 31 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Mon Jan 01 19:35:05 2001 PST
+ | Mon Jan 01 17:32:01 2001 PST | @ 5 hours | Mon Jan 01 22:32:01 2001 PST
+ | Mon Jan 01 17:32:01 2001 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Jan 02 19:35:05 2001 PST
+ | Sun Dec 31 17:32:01 2000 PST | @ 10 days | Wed Jan 10 17:32:01 2001 PST
+ | Mon Jan 01 17:32:01 2001 PST | @ 10 days | Thu Jan 11 17:32:01 2001 PST
+ | Sun Dec 31 17:32:01 2000 PST | @ 3 mons | Sat Mar 31 17:32:01 2001 PST
+ | Mon Jan 01 17:32:01 2001 PST | @ 3 mons | Sun Apr 01 18:32:01 2001 PDT
+ | Sun Dec 31 17:32:01 2000 PST | @ 5 mons | Thu May 31 18:32:01 2001 PDT
+ | Sun Dec 31 17:32:01 2000 PST | @ 5 mons 12 hours | Fri Jun 01 06:32:01 2001 PDT
+ | Mon Jan 01 17:32:01 2001 PST | @ 5 mons | Fri Jun 01 18:32:01 2001 PDT
+ | Mon Jan 01 17:32:01 2001 PST | @ 5 mons 12 hours | Sat Jun 02 06:32:01 2001 PDT
+ | Wed Feb 28 17:32:01 1996 PST | @ 6 years | Thu Feb 28 17:32:01 2002 PST
+ | Thu Feb 29 17:32:01 1996 PST | @ 6 years | Thu Feb 28 17:32:01 2002 PST
+ | Fri Mar 01 17:32:01 1996 PST | @ 6 years | Fri Mar 01 17:32:01 2002 PST
+ | Mon Dec 30 17:32:01 1996 PST | @ 6 years | Mon Dec 30 17:32:01 2002 PST
+ | Tue Dec 31 17:32:01 1996 PST | @ 6 years | Tue Dec 31 17:32:01 2002 PST
+ | epoch | @ 34 years | Wed Dec 31 16:00:00 2003 PST
+ | Fri Dec 31 17:32:01 1999 PST | @ 6 years | Sat Dec 31 17:32:01 2005 PST
+ | Sat Jan 01 17:32:01 2000 PST | @ 6 years | Sun Jan 01 17:32:01 2006 PST
+ | Tue Feb 15 23:14:30 2000 PST | @ 6 years | Wed Feb 15 23:14:30 2006 PST
+ | Sun Dec 31 17:32:01 2000 PST | @ 6 years | Sun Dec 31 17:32:01 2006 PST
+ | Mon Jan 01 17:32:01 2001 PST | @ 6 years | Mon Jan 01 17:32:01 2007 PST
+ | Wed Feb 28 17:32:01 1996 PST | @ 34 years | Thu Feb 28 17:32:01 2030 PST
+ | Thu Feb 29 17:32:01 1996 PST | @ 34 years | Thu Feb 28 17:32:01 2030 PST
+ | Fri Mar 01 17:32:01 1996 PST | @ 34 years | Fri Mar 01 17:32:01 2030 PST
+ | Mon Dec 30 17:32:01 1996 PST | @ 34 years | Mon Dec 30 17:32:01 2030 PST
+ | Tue Dec 31 17:32:01 1996 PST | @ 34 years | Tue Dec 31 17:32:01 2030 PST
+ | Fri Dec 31 17:32:01 1999 PST | @ 34 years | Sat Dec 31 17:32:01 2033 PST
+ | Sat Jan 01 17:32:01 2000 PST | @ 34 years | Sun Jan 01 17:32:01 2034 PST
+ | Tue Feb 15 23:14:30 2000 PST | @ 34 years | Wed Feb 15 23:14:30 2034 PST
+ | Sun Dec 31 17:32:01 2000 PST | @ 34 years | Sun Dec 31 17:32:01 2034 PST
+ | Mon Jan 01 17:32:01 2001 PST | @ 34 years | Mon Jan 01 17:32:01 2035 PST
+(110 rows)
-SELECT '' AS hundred, d.f1 AS datetime, t.f1 AS timespan, d.f1 - t.f1 AS minus
- FROM TEMP_DATETIME d, TIMESPAN_TBL t
+SELECT '' AS "110", d.f1 AS timestamp, t.f1 AS interval, d.f1 - t.f1 AS minus
+ FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
WHERE isfinite(d.f1)
- ORDER BY minus, datetime, timespan;
- hundred | datetime | timespan | minus
----------+------------------------------+-------------------------------+------------------------------
- | epoch | @ 34 years | Tue Dec 31 16:00:00 1935 PST
- | Wed Feb 28 17:32:01 1996 PST | @ 34 years | Wed Feb 28 17:32:01 1962 PST
- | Thu Feb 29 17:32:01 1996 PST | @ 34 years | Wed Feb 28 17:32:01 1962 PST
- | Fri Mar 01 17:32:01 1996 PST | @ 34 years | Thu Mar 01 17:32:01 1962 PST
- | Mon Dec 30 17:32:01 1996 PST | @ 34 years | Sun Dec 30 17:32:01 1962 PST
- | Tue Dec 31 17:32:01 1996 PST | @ 34 years | Mon Dec 31 17:32:01 1962 PST
- | epoch | @ 6 years | Tue Dec 31 16:00:00 1963 PST
- | Fri Dec 31 17:32:01 1999 PST | @ 34 years | Fri Dec 31 17:32:01 1965 PST
- | Sat Jan 01 17:32:01 2000 PST | @ 34 years | Sat Jan 01 17:32:01 1966 PST
- | Sun Dec 31 17:32:01 2000 PST | @ 34 years | Sat Dec 31 17:32:01 1966 PST
- | Mon Jan 01 17:32:01 2001 PST | @ 34 years | Sun Jan 01 17:32:01 1967 PST
- | epoch | @ 5 mons 12 hours | Thu Jul 31 05:00:00 1969 PDT
- | epoch | @ 5 mons | Thu Jul 31 17:00:00 1969 PDT
- | epoch | @ 3 mons | Tue Sep 30 17:00:00 1969 PDT
- | epoch | @ 10 days | Sun Dec 21 16:00:00 1969 PST
- | epoch | @ 1 day 2 hours 3 mins 4 secs | Tue Dec 30 13:56:56 1969 PST
- | epoch | @ 5 hours | Wed Dec 31 11:00:00 1969 PST
- | epoch | @ 1 min | Wed Dec 31 15:59:00 1969 PST
- | epoch | @ 14 secs ago | Wed Dec 31 16:00:14 1969 PST
- | Wed Feb 28 17:32:01 1996 PST | @ 6 years | Wed Feb 28 17:32:01 1990 PST
- | Thu Feb 29 17:32:01 1996 PST | @ 6 years | Wed Feb 28 17:32:01 1990 PST
- | Fri Mar 01 17:32:01 1996 PST | @ 6 years | Thu Mar 01 17:32:01 1990 PST
- | Mon Dec 30 17:32:01 1996 PST | @ 6 years | Sun Dec 30 17:32:01 1990 PST
- | Tue Dec 31 17:32:01 1996 PST | @ 6 years | Mon Dec 31 17:32:01 1990 PST
- | Fri Dec 31 17:32:01 1999 PST | @ 6 years | Fri Dec 31 17:32:01 1993 PST
- | Sat Jan 01 17:32:01 2000 PST | @ 6 years | Sat Jan 01 17:32:01 1994 PST
- | Sun Dec 31 17:32:01 2000 PST | @ 6 years | Sat Dec 31 17:32:01 1994 PST
- | Mon Jan 01 17:32:01 2001 PST | @ 6 years | Sun Jan 01 17:32:01 1995 PST
- | Wed Feb 28 17:32:01 1996 PST | @ 5 mons 12 hours | Thu Sep 28 06:32:01 1995 PDT
- | Wed Feb 28 17:32:01 1996 PST | @ 5 mons | Thu Sep 28 18:32:01 1995 PDT
- | Thu Feb 29 17:32:01 1996 PST | @ 5 mons 12 hours | Fri Sep 29 06:32:01 1995 PDT
- | Thu Feb 29 17:32:01 1996 PST | @ 5 mons | Fri Sep 29 18:32:01 1995 PDT
- | Fri Mar 01 17:32:01 1996 PST | @ 5 mons 12 hours | Sun Oct 01 06:32:01 1995 PDT
- | Fri Mar 01 17:32:01 1996 PST | @ 5 mons | Sun Oct 01 18:32:01 1995 PDT
- | Wed Feb 28 17:32:01 1996 PST | @ 3 mons | Tue Nov 28 17:32:01 1995 PST
- | Thu Feb 29 17:32:01 1996 PST | @ 3 mons | Wed Nov 29 17:32:01 1995 PST
- | Fri Mar 01 17:32:01 1996 PST | @ 3 mons | Fri Dec 01 17:32:01 1995 PST
- | Wed Feb 28 17:32:01 1996 PST | @ 10 days | Sun Feb 18 17:32:01 1996 PST
- | Thu Feb 29 17:32:01 1996 PST | @ 10 days | Mon Feb 19 17:32:01 1996 PST
- | Fri Mar 01 17:32:01 1996 PST | @ 10 days | Tue Feb 20 17:32:01 1996 PST
- | Wed Feb 28 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Feb 27 15:28:57 1996 PST
- | Wed Feb 28 17:32:01 1996 PST | @ 5 hours | Wed Feb 28 12:32:01 1996 PST
- | Thu Feb 29 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Wed Feb 28 15:28:57 1996 PST
- | Wed Feb 28 17:32:01 1996 PST | @ 1 min | Wed Feb 28 17:31:01 1996 PST
- | Wed Feb 28 17:32:01 1996 PST | @ 14 secs ago | Wed Feb 28 17:32:15 1996 PST
- | Thu Feb 29 17:32:01 1996 PST | @ 5 hours | Thu Feb 29 12:32:01 1996 PST
- | Fri Mar 01 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Feb 29 15:28:57 1996 PST
- | Thu Feb 29 17:32:01 1996 PST | @ 1 min | Thu Feb 29 17:31:01 1996 PST
- | Thu Feb 29 17:32:01 1996 PST | @ 14 secs ago | Thu Feb 29 17:32:15 1996 PST
- | Fri Mar 01 17:32:01 1996 PST | @ 5 hours | Fri Mar 01 12:32:01 1996 PST
- | Fri Mar 01 17:32:01 1996 PST | @ 1 min | Fri Mar 01 17:31:01 1996 PST
- | Fri Mar 01 17:32:01 1996 PST | @ 14 secs ago | Fri Mar 01 17:32:15 1996 PST
- | Mon Dec 30 17:32:01 1996 PST | @ 5 mons 12 hours | Tue Jul 30 06:32:01 1996 PDT
- | Mon Dec 30 17:32:01 1996 PST | @ 5 mons | Tue Jul 30 18:32:01 1996 PDT
- | Tue Dec 31 17:32:01 1996 PST | @ 5 mons 12 hours | Wed Jul 31 06:32:01 1996 PDT
- | Tue Dec 31 17:32:01 1996 PST | @ 5 mons | Wed Jul 31 18:32:01 1996 PDT
- | Mon Dec 30 17:32:01 1996 PST | @ 3 mons | Mon Sep 30 18:32:01 1996 PDT
- | Tue Dec 31 17:32:01 1996 PST | @ 3 mons | Mon Sep 30 18:32:01 1996 PDT
- | Mon Dec 30 17:32:01 1996 PST | @ 10 days | Fri Dec 20 17:32:01 1996 PST
- | Tue Dec 31 17:32:01 1996 PST | @ 10 days | Sat Dec 21 17:32:01 1996 PST
- | Mon Dec 30 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Dec 29 15:28:57 1996 PST
- | Mon Dec 30 17:32:01 1996 PST | @ 5 hours | Mon Dec 30 12:32:01 1996 PST
- | Tue Dec 31 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Mon Dec 30 15:28:57 1996 PST
- | Mon Dec 30 17:32:01 1996 PST | @ 1 min | Mon Dec 30 17:31:01 1996 PST
- | Mon Dec 30 17:32:01 1996 PST | @ 14 secs ago | Mon Dec 30 17:32:15 1996 PST
- | Tue Dec 31 17:32:01 1996 PST | @ 5 hours | Tue Dec 31 12:32:01 1996 PST
- | Tue Dec 31 17:32:01 1996 PST | @ 1 min | Tue Dec 31 17:31:01 1996 PST
- | Tue Dec 31 17:32:01 1996 PST | @ 14 secs ago | Tue Dec 31 17:32:15 1996 PST
- | Fri Dec 31 17:32:01 1999 PST | @ 5 mons 12 hours | Sat Jul 31 06:32:01 1999 PDT
- | Fri Dec 31 17:32:01 1999 PST | @ 5 mons | Sat Jul 31 18:32:01 1999 PDT
- | Sat Jan 01 17:32:01 2000 PST | @ 5 mons 12 hours | Sun Aug 01 06:32:01 1999 PDT
- | Sat Jan 01 17:32:01 2000 PST | @ 5 mons | Sun Aug 01 18:32:01 1999 PDT
- | Fri Dec 31 17:32:01 1999 PST | @ 3 mons | Thu Sep 30 18:32:01 1999 PDT
- | Sat Jan 01 17:32:01 2000 PST | @ 3 mons | Fri Oct 01 18:32:01 1999 PDT
- | Fri Dec 31 17:32:01 1999 PST | @ 10 days | Tue Dec 21 17:32:01 1999 PST
- | Sat Jan 01 17:32:01 2000 PST | @ 10 days | Wed Dec 22 17:32:01 1999 PST
- | Fri Dec 31 17:32:01 1999 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Dec 30 15:28:57 1999 PST
- | Fri Dec 31 17:32:01 1999 PST | @ 5 hours | Fri Dec 31 12:32:01 1999 PST
- | Sat Jan 01 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Fri Dec 31 15:28:57 1999 PST
- | Fri Dec 31 17:32:01 1999 PST | @ 1 min | Fri Dec 31 17:31:01 1999 PST
- | Fri Dec 31 17:32:01 1999 PST | @ 14 secs ago | Fri Dec 31 17:32:15 1999 PST
- | Sat Jan 01 17:32:01 2000 PST | @ 5 hours | Sat Jan 01 12:32:01 2000 PST
- | Sat Jan 01 17:32:01 2000 PST | @ 1 min | Sat Jan 01 17:31:01 2000 PST
- | Sat Jan 01 17:32:01 2000 PST | @ 14 secs ago | Sat Jan 01 17:32:15 2000 PST
- | Sun Dec 31 17:32:01 2000 PST | @ 5 mons 12 hours | Mon Jul 31 06:32:01 2000 PDT
- | Sun Dec 31 17:32:01 2000 PST | @ 5 mons | Mon Jul 31 18:32:01 2000 PDT
- | Mon Jan 01 17:32:01 2001 PST | @ 5 mons 12 hours | Tue Aug 01 06:32:01 2000 PDT
- | Mon Jan 01 17:32:01 2001 PST | @ 5 mons | Tue Aug 01 18:32:01 2000 PDT
- | Sun Dec 31 17:32:01 2000 PST | @ 3 mons | Sat Sep 30 18:32:01 2000 PDT
- | Mon Jan 01 17:32:01 2001 PST | @ 3 mons | Sun Oct 01 18:32:01 2000 PDT
- | Sun Dec 31 17:32:01 2000 PST | @ 10 days | Thu Dec 21 17:32:01 2000 PST
- | Mon Jan 01 17:32:01 2001 PST | @ 10 days | Fri Dec 22 17:32:01 2000 PST
- | Sun Dec 31 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Dec 30 15:28:57 2000 PST
- | Sun Dec 31 17:32:01 2000 PST | @ 5 hours | Sun Dec 31 12:32:01 2000 PST
- | Mon Jan 01 17:32:01 2001 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Dec 31 15:28:57 2000 PST
- | Sun Dec 31 17:32:01 2000 PST | @ 1 min | Sun Dec 31 17:31:01 2000 PST
- | Sun Dec 31 17:32:01 2000 PST | @ 14 secs ago | Sun Dec 31 17:32:15 2000 PST
- | Mon Jan 01 17:32:01 2001 PST | @ 5 hours | Mon Jan 01 12:32:01 2001 PST
- | Mon Jan 01 17:32:01 2001 PST | @ 1 min | Mon Jan 01 17:31:01 2001 PST
- | Mon Jan 01 17:32:01 2001 PST | @ 14 secs ago | Mon Jan 01 17:32:15 2001 PST
-(100 rows)
+ ORDER BY minus, timestamp, interval;
+ 110 | timestamp | interval | minus
+-----+------------------------------+-------------------------------+------------------------------
+ | epoch | @ 34 years | Tue Dec 31 16:00:00 1935 PST
+ | Wed Feb 28 17:32:01 1996 PST | @ 34 years | Wed Feb 28 17:32:01 1962 PST
+ | Thu Feb 29 17:32:01 1996 PST | @ 34 years | Wed Feb 28 17:32:01 1962 PST
+ | Fri Mar 01 17:32:01 1996 PST | @ 34 years | Thu Mar 01 17:32:01 1962 PST
+ | Mon Dec 30 17:32:01 1996 PST | @ 34 years | Sun Dec 30 17:32:01 1962 PST
+ | Tue Dec 31 17:32:01 1996 PST | @ 34 years | Mon Dec 31 17:32:01 1962 PST
+ | epoch | @ 6 years | Tue Dec 31 16:00:00 1963 PST
+ | Fri Dec 31 17:32:01 1999 PST | @ 34 years | Fri Dec 31 17:32:01 1965 PST
+ | Sat Jan 01 17:32:01 2000 PST | @ 34 years | Sat Jan 01 17:32:01 1966 PST
+ | Tue Feb 15 23:14:30 2000 PST | @ 34 years | Tue Feb 15 23:14:30 1966 PST
+ | Sun Dec 31 17:32:01 2000 PST | @ 34 years | Sat Dec 31 17:32:01 1966 PST
+ | Mon Jan 01 17:32:01 2001 PST | @ 34 years | Sun Jan 01 17:32:01 1967 PST
+ | epoch | @ 5 mons 12 hours | Thu Jul 31 05:00:00 1969 PDT
+ | epoch | @ 5 mons | Thu Jul 31 17:00:00 1969 PDT
+ | epoch | @ 3 mons | Tue Sep 30 17:00:00 1969 PDT
+ | epoch | @ 10 days | Sun Dec 21 16:00:00 1969 PST
+ | epoch | @ 1 day 2 hours 3 mins 4 secs | Tue Dec 30 13:56:56 1969 PST
+ | epoch | @ 5 hours | Wed Dec 31 11:00:00 1969 PST
+ | epoch | @ 1 min | Wed Dec 31 15:59:00 1969 PST
+ | epoch | @ 14 secs ago | Wed Dec 31 16:00:14 1969 PST
+ | Wed Feb 28 17:32:01 1996 PST | @ 6 years | Wed Feb 28 17:32:01 1990 PST
+ | Thu Feb 29 17:32:01 1996 PST | @ 6 years | Wed Feb 28 17:32:01 1990 PST
+ | Fri Mar 01 17:32:01 1996 PST | @ 6 years | Thu Mar 01 17:32:01 1990 PST
+ | Mon Dec 30 17:32:01 1996 PST | @ 6 years | Sun Dec 30 17:32:01 1990 PST
+ | Tue Dec 31 17:32:01 1996 PST | @ 6 years | Mon Dec 31 17:32:01 1990 PST
+ | Fri Dec 31 17:32:01 1999 PST | @ 6 years | Fri Dec 31 17:32:01 1993 PST
+ | Sat Jan 01 17:32:01 2000 PST | @ 6 years | Sat Jan 01 17:32:01 1994 PST
+ | Tue Feb 15 23:14:30 2000 PST | @ 6 years | Tue Feb 15 23:14:30 1994 PST
+ | Sun Dec 31 17:32:01 2000 PST | @ 6 years | Sat Dec 31 17:32:01 1994 PST
+ | Mon Jan 01 17:32:01 2001 PST | @ 6 years | Sun Jan 01 17:32:01 1995 PST
+ | Wed Feb 28 17:32:01 1996 PST | @ 5 mons 12 hours | Thu Sep 28 06:32:01 1995 PDT
+ | Wed Feb 28 17:32:01 1996 PST | @ 5 mons | Thu Sep 28 18:32:01 1995 PDT
+ | Thu Feb 29 17:32:01 1996 PST | @ 5 mons 12 hours | Fri Sep 29 06:32:01 1995 PDT
+ | Thu Feb 29 17:32:01 1996 PST | @ 5 mons | Fri Sep 29 18:32:01 1995 PDT
+ | Fri Mar 01 17:32:01 1996 PST | @ 5 mons 12 hours | Sun Oct 01 06:32:01 1995 PDT
+ | Fri Mar 01 17:32:01 1996 PST | @ 5 mons | Sun Oct 01 18:32:01 1995 PDT
+ | Wed Feb 28 17:32:01 1996 PST | @ 3 mons | Tue Nov 28 17:32:01 1995 PST
+ | Thu Feb 29 17:32:01 1996 PST | @ 3 mons | Wed Nov 29 17:32:01 1995 PST
+ | Fri Mar 01 17:32:01 1996 PST | @ 3 mons | Fri Dec 01 17:32:01 1995 PST
+ | Wed Feb 28 17:32:01 1996 PST | @ 10 days | Sun Feb 18 17:32:01 1996 PST
+ | Thu Feb 29 17:32:01 1996 PST | @ 10 days | Mon Feb 19 17:32:01 1996 PST
+ | Fri Mar 01 17:32:01 1996 PST | @ 10 days | Tue Feb 20 17:32:01 1996 PST
+ | Wed Feb 28 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Feb 27 15:28:57 1996 PST
+ | Wed Feb 28 17:32:01 1996 PST | @ 5 hours | Wed Feb 28 12:32:01 1996 PST
+ | Thu Feb 29 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Wed Feb 28 15:28:57 1996 PST
+ | Wed Feb 28 17:32:01 1996 PST | @ 1 min | Wed Feb 28 17:31:01 1996 PST
+ | Wed Feb 28 17:32:01 1996 PST | @ 14 secs ago | Wed Feb 28 17:32:15 1996 PST
+ | Thu Feb 29 17:32:01 1996 PST | @ 5 hours | Thu Feb 29 12:32:01 1996 PST
+ | Fri Mar 01 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Feb 29 15:28:57 1996 PST
+ | Thu Feb 29 17:32:01 1996 PST | @ 1 min | Thu Feb 29 17:31:01 1996 PST
+ | Thu Feb 29 17:32:01 1996 PST | @ 14 secs ago | Thu Feb 29 17:32:15 1996 PST
+ | Fri Mar 01 17:32:01 1996 PST | @ 5 hours | Fri Mar 01 12:32:01 1996 PST
+ | Fri Mar 01 17:32:01 1996 PST | @ 1 min | Fri Mar 01 17:31:01 1996 PST
+ | Fri Mar 01 17:32:01 1996 PST | @ 14 secs ago | Fri Mar 01 17:32:15 1996 PST
+ | Mon Dec 30 17:32:01 1996 PST | @ 5 mons 12 hours | Tue Jul 30 06:32:01 1996 PDT
+ | Mon Dec 30 17:32:01 1996 PST | @ 5 mons | Tue Jul 30 18:32:01 1996 PDT
+ | Tue Dec 31 17:32:01 1996 PST | @ 5 mons 12 hours | Wed Jul 31 06:32:01 1996 PDT
+ | Tue Dec 31 17:32:01 1996 PST | @ 5 mons | Wed Jul 31 18:32:01 1996 PDT
+ | Mon Dec 30 17:32:01 1996 PST | @ 3 mons | Mon Sep 30 18:32:01 1996 PDT
+ | Tue Dec 31 17:32:01 1996 PST | @ 3 mons | Mon Sep 30 18:32:01 1996 PDT
+ | Mon Dec 30 17:32:01 1996 PST | @ 10 days | Fri Dec 20 17:32:01 1996 PST
+ | Tue Dec 31 17:32:01 1996 PST | @ 10 days | Sat Dec 21 17:32:01 1996 PST
+ | Mon Dec 30 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Dec 29 15:28:57 1996 PST
+ | Mon Dec 30 17:32:01 1996 PST | @ 5 hours | Mon Dec 30 12:32:01 1996 PST
+ | Tue Dec 31 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Mon Dec 30 15:28:57 1996 PST
+ | Mon Dec 30 17:32:01 1996 PST | @ 1 min | Mon Dec 30 17:31:01 1996 PST
+ | Mon Dec 30 17:32:01 1996 PST | @ 14 secs ago | Mon Dec 30 17:32:15 1996 PST
+ | Tue Dec 31 17:32:01 1996 PST | @ 5 hours | Tue Dec 31 12:32:01 1996 PST
+ | Tue Dec 31 17:32:01 1996 PST | @ 1 min | Tue Dec 31 17:31:01 1996 PST
+ | Tue Dec 31 17:32:01 1996 PST | @ 14 secs ago | Tue Dec 31 17:32:15 1996 PST
+ | Fri Dec 31 17:32:01 1999 PST | @ 5 mons 12 hours | Sat Jul 31 06:32:01 1999 PDT
+ | Fri Dec 31 17:32:01 1999 PST | @ 5 mons | Sat Jul 31 18:32:01 1999 PDT
+ | Sat Jan 01 17:32:01 2000 PST | @ 5 mons 12 hours | Sun Aug 01 06:32:01 1999 PDT
+ | Sat Jan 01 17:32:01 2000 PST | @ 5 mons | Sun Aug 01 18:32:01 1999 PDT
+ | Tue Feb 15 23:14:30 2000 PST | @ 5 mons 12 hours | Wed Sep 15 12:14:30 1999 PDT
+ | Tue Feb 15 23:14:30 2000 PST | @ 5 mons | Thu Sep 16 00:14:30 1999 PDT
+ | Fri Dec 31 17:32:01 1999 PST | @ 3 mons | Thu Sep 30 18:32:01 1999 PDT
+ | Sat Jan 01 17:32:01 2000 PST | @ 3 mons | Fri Oct 01 18:32:01 1999 PDT
+ | Tue Feb 15 23:14:30 2000 PST | @ 3 mons | Mon Nov 15 23:14:30 1999 PST
+ | Fri Dec 31 17:32:01 1999 PST | @ 10 days | Tue Dec 21 17:32:01 1999 PST
+ | Sat Jan 01 17:32:01 2000 PST | @ 10 days | Wed Dec 22 17:32:01 1999 PST
+ | Fri Dec 31 17:32:01 1999 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Dec 30 15:28:57 1999 PST
+ | Fri Dec 31 17:32:01 1999 PST | @ 5 hours | Fri Dec 31 12:32:01 1999 PST
+ | Sat Jan 01 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Fri Dec 31 15:28:57 1999 PST
+ | Fri Dec 31 17:32:01 1999 PST | @ 1 min | Fri Dec 31 17:31:01 1999 PST
+ | Fri Dec 31 17:32:01 1999 PST | @ 14 secs ago | Fri Dec 31 17:32:15 1999 PST
+ | Sat Jan 01 17:32:01 2000 PST | @ 5 hours | Sat Jan 01 12:32:01 2000 PST
+ | Sat Jan 01 17:32:01 2000 PST | @ 1 min | Sat Jan 01 17:31:01 2000 PST
+ | Sat Jan 01 17:32:01 2000 PST | @ 14 secs ago | Sat Jan 01 17:32:15 2000 PST
+ | Tue Feb 15 23:14:30 2000 PST | @ 10 days | Sat Feb 05 23:14:30 2000 PST
+ | Tue Feb 15 23:14:30 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Mon Feb 14 21:11:26 2000 PST
+ | Tue Feb 15 23:14:30 2000 PST | @ 5 hours | Tue Feb 15 18:14:30 2000 PST
+ | Tue Feb 15 23:14:30 2000 PST | @ 1 min | Tue Feb 15 23:13:30 2000 PST
+ | Tue Feb 15 23:14:30 2000 PST | @ 14 secs ago | Tue Feb 15 23:14:44 2000 PST
+ | Sun Dec 31 17:32:01 2000 PST | @ 5 mons 12 hours | Mon Jul 31 06:32:01 2000 PDT
+ | Sun Dec 31 17:32:01 2000 PST | @ 5 mons | Mon Jul 31 18:32:01 2000 PDT
+ | Mon Jan 01 17:32:01 2001 PST | @ 5 mons 12 hours | Tue Aug 01 06:32:01 2000 PDT
+ | Mon Jan 01 17:32:01 2001 PST | @ 5 mons | Tue Aug 01 18:32:01 2000 PDT
+ | Sun Dec 31 17:32:01 2000 PST | @ 3 mons | Sat Sep 30 18:32:01 2000 PDT
+ | Mon Jan 01 17:32:01 2001 PST | @ 3 mons | Sun Oct 01 18:32:01 2000 PDT
+ | Sun Dec 31 17:32:01 2000 PST | @ 10 days | Thu Dec 21 17:32:01 2000 PST
+ | Mon Jan 01 17:32:01 2001 PST | @ 10 days | Fri Dec 22 17:32:01 2000 PST
+ | Sun Dec 31 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Dec 30 15:28:57 2000 PST
+ | Sun Dec 31 17:32:01 2000 PST | @ 5 hours | Sun Dec 31 12:32:01 2000 PST
+ | Mon Jan 01 17:32:01 2001 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Dec 31 15:28:57 2000 PST
+ | Sun Dec 31 17:32:01 2000 PST | @ 1 min | Sun Dec 31 17:31:01 2000 PST
+ | Sun Dec 31 17:32:01 2000 PST | @ 14 secs ago | Sun Dec 31 17:32:15 2000 PST
+ | Mon Jan 01 17:32:01 2001 PST | @ 5 hours | Mon Jan 01 12:32:01 2001 PST
+ | Mon Jan 01 17:32:01 2001 PST | @ 1 min | Mon Jan 01 17:31:01 2001 PST
+ | Mon Jan 01 17:32:01 2001 PST | @ 14 secs ago | Mon Jan 01 17:32:15 2001 PST
+(110 rows)
-SELECT '' AS ten, d.f1 AS datetime, datetime '1980-01-06 00:00 GMT' AS gpstime_zero,
- d.f1 - datetime '1980-01-06 00:00 GMT' AS difference
- FROM TEMP_DATETIME d
+SELECT '' AS "11", d.f1 AS timestamp, timestamp '1980-01-06 00:00 GMT' AS gpstime_zero,
+ d.f1 - timestamp '1980-01-06 00:00 GMT' AS difference
+ FROM TEMP_TIMESTAMP d
ORDER BY difference;
- ten | datetime | gpstime_zero | difference
------+------------------------------+------------------------------+----------------------------------
- | epoch | Sat Jan 05 16:00:00 1980 PST | @ 3657 days ago
- | Wed Feb 28 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5898 days 1 hour 32 mins 1 sec
- | Thu Feb 29 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5899 days 1 hour 32 mins 1 sec
- | Fri Mar 01 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5900 days 1 hour 32 mins 1 sec
- | Mon Dec 30 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 6204 days 1 hour 32 mins 1 sec
- | Tue Dec 31 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 6205 days 1 hour 32 mins 1 sec
- | Fri Dec 31 17:32:01 1999 PST | Sat Jan 05 16:00:00 1980 PST | @ 7300 days 1 hour 32 mins 1 sec
- | Sat Jan 01 17:32:01 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7301 days 1 hour 32 mins 1 sec
- | Sun Dec 31 17:32:01 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7666 days 1 hour 32 mins 1 sec
- | Mon Jan 01 17:32:01 2001 PST | Sat Jan 05 16:00:00 1980 PST | @ 7667 days 1 hour 32 mins 1 sec
-(10 rows)
+ 11 | timestamp | gpstime_zero | difference
+----+------------------------------+------------------------------+-------------------------------------
+ | epoch | Sat Jan 05 16:00:00 1980 PST | @ 3657 days ago
+ | Wed Feb 28 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5898 days 1 hour 32 mins 1 sec
+ | Thu Feb 29 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5899 days 1 hour 32 mins 1 sec
+ | Fri Mar 01 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5900 days 1 hour 32 mins 1 sec
+ | Mon Dec 30 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 6204 days 1 hour 32 mins 1 sec
+ | Tue Dec 31 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 6205 days 1 hour 32 mins 1 sec
+ | Fri Dec 31 17:32:01 1999 PST | Sat Jan 05 16:00:00 1980 PST | @ 7300 days 1 hour 32 mins 1 sec
+ | Sat Jan 01 17:32:01 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7301 days 1 hour 32 mins 1 sec
+ | Tue Feb 15 23:14:30 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7346 days 7 hours 14 mins 30 secs
+ | Sun Dec 31 17:32:01 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7666 days 1 hour 32 mins 1 sec
+ | Mon Jan 01 17:32:01 2001 PST | Sat Jan 05 16:00:00 1980 PST | @ 7667 days 1 hour 32 mins 1 sec
+(11 rows)
-SELECT '' AS hundred, d1.f1 AS datetime1, d2.f1 AS datetime2, d1.f1 - d2.f1 AS difference
- FROM TEMP_DATETIME d1, TEMP_DATETIME d2
- ORDER BY datetime1, datetime2, difference;
- hundred | datetime1 | datetime2 | difference
----------+------------------------------+------------------------------+---------------------------------------
- | epoch | epoch | @ 0
- | epoch | Wed Feb 28 17:32:01 1996 PST | @ 9555 days 1 hour 32 mins 1 sec ago
- | epoch | Thu Feb 29 17:32:01 1996 PST | @ 9556 days 1 hour 32 mins 1 sec ago
- | epoch | Fri Mar 01 17:32:01 1996 PST | @ 9557 days 1 hour 32 mins 1 sec ago
- | epoch | Mon Dec 30 17:32:01 1996 PST | @ 9861 days 1 hour 32 mins 1 sec ago
- | epoch | Tue Dec 31 17:32:01 1996 PST | @ 9862 days 1 hour 32 mins 1 sec ago
- | epoch | Fri Dec 31 17:32:01 1999 PST | @ 10957 days 1 hour 32 mins 1 sec ago
- | epoch | Sat Jan 01 17:32:01 2000 PST | @ 10958 days 1 hour 32 mins 1 sec ago
- | epoch | Sun Dec 31 17:32:01 2000 PST | @ 11323 days 1 hour 32 mins 1 sec ago
- | epoch | Mon Jan 01 17:32:01 2001 PST | @ 11324 days 1 hour 32 mins 1 sec ago
- | Wed Feb 28 17:32:01 1996 PST | epoch | @ 9555 days 1 hour 32 mins 1 sec
- | Wed Feb 28 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 0
- | Wed Feb 28 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day ago
- | Wed Feb 28 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 2 days ago
- | Wed Feb 28 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 306 days ago
- | Wed Feb 28 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 307 days ago
- | Wed Feb 28 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1402 days ago
- | Wed Feb 28 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1403 days ago
- | Wed Feb 28 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1768 days ago
- | Wed Feb 28 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1769 days ago
- | Thu Feb 29 17:32:01 1996 PST | epoch | @ 9556 days 1 hour 32 mins 1 sec
- | Thu Feb 29 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 1 day
- | Thu Feb 29 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 0
- | Thu Feb 29 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 1 day ago
- | Thu Feb 29 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 305 days ago
- | Thu Feb 29 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 306 days ago
- | Thu Feb 29 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1401 days ago
- | Thu Feb 29 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1402 days ago
- | Thu Feb 29 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1767 days ago
- | Thu Feb 29 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1768 days ago
- | Fri Mar 01 17:32:01 1996 PST | epoch | @ 9557 days 1 hour 32 mins 1 sec
- | Fri Mar 01 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 2 days
- | Fri Mar 01 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day
- | Fri Mar 01 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 0
- | Fri Mar 01 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 304 days ago
- | Fri Mar 01 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 305 days ago
- | Fri Mar 01 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1400 days ago
- | Fri Mar 01 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1401 days ago
- | Fri Mar 01 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1766 days ago
- | Fri Mar 01 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1767 days ago
- | Mon Dec 30 17:32:01 1996 PST | epoch | @ 9861 days 1 hour 32 mins 1 sec
- | Mon Dec 30 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 306 days
- | Mon Dec 30 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 305 days
- | Mon Dec 30 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 304 days
- | Mon Dec 30 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 0
- | Mon Dec 30 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 1 day ago
- | Mon Dec 30 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1096 days ago
- | Mon Dec 30 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1097 days ago
- | Mon Dec 30 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1462 days ago
- | Mon Dec 30 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1463 days ago
- | Tue Dec 31 17:32:01 1996 PST | epoch | @ 9862 days 1 hour 32 mins 1 sec
- | Tue Dec 31 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 307 days
- | Tue Dec 31 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 306 days
- | Tue Dec 31 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 305 days
- | Tue Dec 31 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 1 day
- | Tue Dec 31 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 0
- | Tue Dec 31 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1095 days ago
- | Tue Dec 31 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1096 days ago
- | Tue Dec 31 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1461 days ago
- | Tue Dec 31 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1462 days ago
- | Fri Dec 31 17:32:01 1999 PST | epoch | @ 10957 days 1 hour 32 mins 1 sec
- | Fri Dec 31 17:32:01 1999 PST | Wed Feb 28 17:32:01 1996 PST | @ 1402 days
- | Fri Dec 31 17:32:01 1999 PST | Thu Feb 29 17:32:01 1996 PST | @ 1401 days
- | Fri Dec 31 17:32:01 1999 PST | Fri Mar 01 17:32:01 1996 PST | @ 1400 days
- | Fri Dec 31 17:32:01 1999 PST | Mon Dec 30 17:32:01 1996 PST | @ 1096 days
- | Fri Dec 31 17:32:01 1999 PST | Tue Dec 31 17:32:01 1996 PST | @ 1095 days
- | Fri Dec 31 17:32:01 1999 PST | Fri Dec 31 17:32:01 1999 PST | @ 0
- | Fri Dec 31 17:32:01 1999 PST | Sat Jan 01 17:32:01 2000 PST | @ 1 day ago
- | Fri Dec 31 17:32:01 1999 PST | Sun Dec 31 17:32:01 2000 PST | @ 366 days ago
- | Fri Dec 31 17:32:01 1999 PST | Mon Jan 01 17:32:01 2001 PST | @ 367 days ago
- | Sat Jan 01 17:32:01 2000 PST | epoch | @ 10958 days 1 hour 32 mins 1 sec
- | Sat Jan 01 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1403 days
- | Sat Jan 01 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1402 days
- | Sat Jan 01 17:32:01 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1401 days
- | Sat Jan 01 17:32:01 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1097 days
- | Sat Jan 01 17:32:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1096 days
- | Sat Jan 01 17:32:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 1 day
- | Sat Jan 01 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 0
- | Sat Jan 01 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 365 days ago
- | Sat Jan 01 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 366 days ago
- | Sun Dec 31 17:32:01 2000 PST | epoch | @ 11323 days 1 hour 32 mins 1 sec
- | Sun Dec 31 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1768 days
- | Sun Dec 31 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1767 days
- | Sun Dec 31 17:32:01 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1766 days
- | Sun Dec 31 17:32:01 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1462 days
- | Sun Dec 31 17:32:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1461 days
- | Sun Dec 31 17:32:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 366 days
- | Sun Dec 31 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 365 days
- | Sun Dec 31 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 0
- | Sun Dec 31 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 1 day ago
- | Mon Jan 01 17:32:01 2001 PST | epoch | @ 11324 days 1 hour 32 mins 1 sec
- | Mon Jan 01 17:32:01 2001 PST | Wed Feb 28 17:32:01 1996 PST | @ 1769 days
- | Mon Jan 01 17:32:01 2001 PST | Thu Feb 29 17:32:01 1996 PST | @ 1768 days
- | Mon Jan 01 17:32:01 2001 PST | Fri Mar 01 17:32:01 1996 PST | @ 1767 days
- | Mon Jan 01 17:32:01 2001 PST | Mon Dec 30 17:32:01 1996 PST | @ 1463 days
- | Mon Jan 01 17:32:01 2001 PST | Tue Dec 31 17:32:01 1996 PST | @ 1462 days
- | Mon Jan 01 17:32:01 2001 PST | Fri Dec 31 17:32:01 1999 PST | @ 367 days
- | Mon Jan 01 17:32:01 2001 PST | Sat Jan 01 17:32:01 2000 PST | @ 366 days
- | Mon Jan 01 17:32:01 2001 PST | Sun Dec 31 17:32:01 2000 PST | @ 1 day
- | Mon Jan 01 17:32:01 2001 PST | Mon Jan 01 17:32:01 2001 PST | @ 0
-(100 rows)
+SELECT '' AS "121", d1.f1 AS timestamp1, d2.f1 AS timestamp2, d1.f1 - d2.f1 AS difference
+ FROM TEMP_TIMESTAMP d1, TEMP_TIMESTAMP d2
+ ORDER BY timestamp1, timestamp2, difference;
+ 121 | timestamp1 | timestamp2 | difference
+-----+------------------------------+------------------------------+------------------------------------------
+ | epoch | epoch | @ 0
+ | epoch | Wed Feb 28 17:32:01 1996 PST | @ 9555 days 1 hour 32 mins 1 sec ago
+ | epoch | Thu Feb 29 17:32:01 1996 PST | @ 9556 days 1 hour 32 mins 1 sec ago
+ | epoch | Fri Mar 01 17:32:01 1996 PST | @ 9557 days 1 hour 32 mins 1 sec ago
+ | epoch | Mon Dec 30 17:32:01 1996 PST | @ 9861 days 1 hour 32 mins 1 sec ago
+ | epoch | Tue Dec 31 17:32:01 1996 PST | @ 9862 days 1 hour 32 mins 1 sec ago
+ | epoch | Fri Dec 31 17:32:01 1999 PST | @ 10957 days 1 hour 32 mins 1 sec ago
+ | epoch | Sat Jan 01 17:32:01 2000 PST | @ 10958 days 1 hour 32 mins 1 sec ago
+ | epoch | Tue Feb 15 23:14:30 2000 PST | @ 11003 days 7 hours 14 mins 30 secs ago
+ | epoch | Sun Dec 31 17:32:01 2000 PST | @ 11323 days 1 hour 32 mins 1 sec ago
+ | epoch | Mon Jan 01 17:32:01 2001 PST | @ 11324 days 1 hour 32 mins 1 sec ago
+ | Wed Feb 28 17:32:01 1996 PST | epoch | @ 9555 days 1 hour 32 mins 1 sec
+ | Wed Feb 28 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 0
+ | Wed Feb 28 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day ago
+ | Wed Feb 28 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 2 days ago
+ | Wed Feb 28 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 306 days ago
+ | Wed Feb 28 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 307 days ago
+ | Wed Feb 28 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1402 days ago
+ | Wed Feb 28 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1403 days ago
+ | Wed Feb 28 17:32:01 1996 PST | Tue Feb 15 23:14:30 2000 PST | @ 1448 days 5 hours 42 mins 29 secs ago
+ | Wed Feb 28 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1768 days ago
+ | Wed Feb 28 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1769 days ago
+ | Thu Feb 29 17:32:01 1996 PST | epoch | @ 9556 days 1 hour 32 mins 1 sec
+ | Thu Feb 29 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 1 day
+ | Thu Feb 29 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 0
+ | Thu Feb 29 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 1 day ago
+ | Thu Feb 29 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 305 days ago
+ | Thu Feb 29 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 306 days ago
+ | Thu Feb 29 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1401 days ago
+ | Thu Feb 29 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1402 days ago
+ | Thu Feb 29 17:32:01 1996 PST | Tue Feb 15 23:14:30 2000 PST | @ 1447 days 5 hours 42 mins 29 secs ago
+ | Thu Feb 29 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1767 days ago
+ | Thu Feb 29 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1768 days ago
+ | Fri Mar 01 17:32:01 1996 PST | epoch | @ 9557 days 1 hour 32 mins 1 sec
+ | Fri Mar 01 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 2 days
+ | Fri Mar 01 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day
+ | Fri Mar 01 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 0
+ | Fri Mar 01 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 304 days ago
+ | Fri Mar 01 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 305 days ago
+ | Fri Mar 01 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1400 days ago
+ | Fri Mar 01 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1401 days ago
+ | Fri Mar 01 17:32:01 1996 PST | Tue Feb 15 23:14:30 2000 PST | @ 1446 days 5 hours 42 mins 29 secs ago
+ | Fri Mar 01 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1766 days ago
+ | Fri Mar 01 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1767 days ago
+ | Mon Dec 30 17:32:01 1996 PST | epoch | @ 9861 days 1 hour 32 mins 1 sec
+ | Mon Dec 30 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 306 days
+ | Mon Dec 30 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 305 days
+ | Mon Dec 30 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 304 days
+ | Mon Dec 30 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 0
+ | Mon Dec 30 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 1 day ago
+ | Mon Dec 30 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1096 days ago
+ | Mon Dec 30 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1097 days ago
+ | Mon Dec 30 17:32:01 1996 PST | Tue Feb 15 23:14:30 2000 PST | @ 1142 days 5 hours 42 mins 29 secs ago
+ | Mon Dec 30 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1462 days ago
+ | Mon Dec 30 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1463 days ago
+ | Tue Dec 31 17:32:01 1996 PST | epoch | @ 9862 days 1 hour 32 mins 1 sec
+ | Tue Dec 31 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 307 days
+ | Tue Dec 31 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 306 days
+ | Tue Dec 31 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 305 days
+ | Tue Dec 31 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 1 day
+ | Tue Dec 31 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 0
+ | Tue Dec 31 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1095 days ago
+ | Tue Dec 31 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1096 days ago
+ | Tue Dec 31 17:32:01 1996 PST | Tue Feb 15 23:14:30 2000 PST | @ 1141 days 5 hours 42 mins 29 secs ago
+ | Tue Dec 31 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1461 days ago
+ | Tue Dec 31 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1462 days ago
+ | Fri Dec 31 17:32:01 1999 PST | epoch | @ 10957 days 1 hour 32 mins 1 sec
+ | Fri Dec 31 17:32:01 1999 PST | Wed Feb 28 17:32:01 1996 PST | @ 1402 days
+ | Fri Dec 31 17:32:01 1999 PST | Thu Feb 29 17:32:01 1996 PST | @ 1401 days
+ | Fri Dec 31 17:32:01 1999 PST | Fri Mar 01 17:32:01 1996 PST | @ 1400 days
+ | Fri Dec 31 17:32:01 1999 PST | Mon Dec 30 17:32:01 1996 PST | @ 1096 days
+ | Fri Dec 31 17:32:01 1999 PST | Tue Dec 31 17:32:01 1996 PST | @ 1095 days
+ | Fri Dec 31 17:32:01 1999 PST | Fri Dec 31 17:32:01 1999 PST | @ 0
+ | Fri Dec 31 17:32:01 1999 PST | Sat Jan 01 17:32:01 2000 PST | @ 1 day ago
+ | Fri Dec 31 17:32:01 1999 PST | Tue Feb 15 23:14:30 2000 PST | @ 46 days 5 hours 42 mins 29 secs ago
+ | Fri Dec 31 17:32:01 1999 PST | Sun Dec 31 17:32:01 2000 PST | @ 366 days ago
+ | Fri Dec 31 17:32:01 1999 PST | Mon Jan 01 17:32:01 2001 PST | @ 367 days ago
+ | Sat Jan 01 17:32:01 2000 PST | epoch | @ 10958 days 1 hour 32 mins 1 sec
+ | Sat Jan 01 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1403 days
+ | Sat Jan 01 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1402 days
+ | Sat Jan 01 17:32:01 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1401 days
+ | Sat Jan 01 17:32:01 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1097 days
+ | Sat Jan 01 17:32:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1096 days
+ | Sat Jan 01 17:32:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 1 day
+ | Sat Jan 01 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 0
+ | Sat Jan 01 17:32:01 2000 PST | Tue Feb 15 23:14:30 2000 PST | @ 45 days 5 hours 42 mins 29 secs ago
+ | Sat Jan 01 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 365 days ago
+ | Sat Jan 01 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 366 days ago
+ | Tue Feb 15 23:14:30 2000 PST | epoch | @ 11003 days 7 hours 14 mins 30 secs
+ | Tue Feb 15 23:14:30 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1448 days 5 hours 42 mins 29 secs
+ | Tue Feb 15 23:14:30 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1447 days 5 hours 42 mins 29 secs
+ | Tue Feb 15 23:14:30 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1446 days 5 hours 42 mins 29 secs
+ | Tue Feb 15 23:14:30 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1142 days 5 hours 42 mins 29 secs
+ | Tue Feb 15 23:14:30 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1141 days 5 hours 42 mins 29 secs
+ | Tue Feb 15 23:14:30 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 46 days 5 hours 42 mins 29 secs
+ | Tue Feb 15 23:14:30 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 45 days 5 hours 42 mins 29 secs
+ | Tue Feb 15 23:14:30 2000 PST | Tue Feb 15 23:14:30 2000 PST | @ 0
+ | Tue Feb 15 23:14:30 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 319 days 18 hours 17 mins 31 secs ago
+ | Tue Feb 15 23:14:30 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 320 days 18 hours 17 mins 31 secs ago
+ | Sun Dec 31 17:32:01 2000 PST | epoch | @ 11323 days 1 hour 32 mins 1 sec
+ | Sun Dec 31 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1768 days
+ | Sun Dec 31 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1767 days
+ | Sun Dec 31 17:32:01 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1766 days
+ | Sun Dec 31 17:32:01 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1462 days
+ | Sun Dec 31 17:32:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1461 days
+ | Sun Dec 31 17:32:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 366 days
+ | Sun Dec 31 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 365 days
+ | Sun Dec 31 17:32:01 2000 PST | Tue Feb 15 23:14:30 2000 PST | @ 319 days 18 hours 17 mins 31 secs
+ | Sun Dec 31 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 0
+ | Sun Dec 31 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 1 day ago
+ | Mon Jan 01 17:32:01 2001 PST | epoch | @ 11324 days 1 hour 32 mins 1 sec
+ | Mon Jan 01 17:32:01 2001 PST | Wed Feb 28 17:32:01 1996 PST | @ 1769 days
+ | Mon Jan 01 17:32:01 2001 PST | Thu Feb 29 17:32:01 1996 PST | @ 1768 days
+ | Mon Jan 01 17:32:01 2001 PST | Fri Mar 01 17:32:01 1996 PST | @ 1767 days
+ | Mon Jan 01 17:32:01 2001 PST | Mon Dec 30 17:32:01 1996 PST | @ 1463 days
+ | Mon Jan 01 17:32:01 2001 PST | Tue Dec 31 17:32:01 1996 PST | @ 1462 days
+ | Mon Jan 01 17:32:01 2001 PST | Fri Dec 31 17:32:01 1999 PST | @ 367 days
+ | Mon Jan 01 17:32:01 2001 PST | Sat Jan 01 17:32:01 2000 PST | @ 366 days
+ | Mon Jan 01 17:32:01 2001 PST | Tue Feb 15 23:14:30 2000 PST | @ 320 days 18 hours 17 mins 31 secs
+ | Mon Jan 01 17:32:01 2001 PST | Sun Dec 31 17:32:01 2000 PST | @ 1 day
+ | Mon Jan 01 17:32:01 2001 PST | Mon Jan 01 17:32:01 2001 PST | @ 0
+(121 rows)
-SELECT '' as fifty, d1 as datetime,
+SELECT '' as fifty, d1 as timestamp,
date_part('year', d1) AS year, date_part('month', d1) AS month,
date_part('day',d1) AS day, date_part('hour', d1) AS hour,
date_part('minute', d1) AS minute, date_part('second', d1) AS second
- FROM DATETIME_TBL
+ FROM TIMESTAMP_TBL
WHERE isfinite(d1) and d1 >= '1-jan-1900 GMT'
- ORDER BY datetime;
- fifty | datetime | year | month | day | hour | minute | second
+ ORDER BY timestamp;
+ fifty | timestamp | year | month | day | hour | minute | second
-------+---------------------------------+------+-------+-----+------+--------+----------
| epoch | 1969 | 12 | 31 | 16 | 0 | 0
| Wed Feb 28 17:32:01 1996 PST | 1996 | 2 | 28 | 17 | 32 | 1
@@ -446,10 +489,11 @@ SELECT '' as fifty, d1 as datetime,
| Wed Dec 31 17:32:01 1997 PST | 1997 | 12 | 31 | 17 | 32 | 1
| Fri Dec 31 17:32:01 1999 PST | 1999 | 12 | 31 | 17 | 32 | 1
| Sat Jan 01 17:32:01 2000 PST | 2000 | 1 | 1 | 17 | 32 | 1
+ | Tue Feb 15 23:14:30 2000 PST | 2000 | 2 | 15 | 23 | 14 | 30
| Sun Dec 31 17:32:01 2000 PST | 2000 | 12 | 31 | 17 | 32 | 1
| Mon Jan 01 17:32:01 2001 PST | 2001 | 1 | 1 | 17 | 32 | 1
| Sat Feb 16 17:32:01 2097 | 2097 | 2 | 16 | 17 | 32 | 1
-(49 rows)
+(50 rows)
--
-- abstime, reltime arithmetic
@@ -470,48 +514,50 @@ SELECT '' AS four, f1 AS abstime,
(4 rows)
--
--- conversions
+-- Conversions
--
-SELECT '' AS ten, f1 AS datetime, date( f1) AS date
- FROM TEMP_DATETIME
- WHERE f1 <> datetime 'current'
+SELECT '' AS "11", f1 AS timestamp, date( f1) AS date
+ FROM TEMP_TIMESTAMP
+ WHERE f1 <> timestamp 'current'
ORDER BY date;
- ten | datetime | date
------+------------------------------+------------
- | epoch | 01-01-1970
- | Wed Feb 28 17:32:01 1996 PST | 02-28-1996
- | Thu Feb 29 17:32:01 1996 PST | 02-29-1996
- | Fri Mar 01 17:32:01 1996 PST | 03-01-1996
- | Mon Dec 30 17:32:01 1996 PST | 12-30-1996
- | Tue Dec 31 17:32:01 1996 PST | 12-31-1996
- | Fri Dec 31 17:32:01 1999 PST | 12-31-1999
- | Sat Jan 01 17:32:01 2000 PST | 01-01-2000
- | Sun Dec 31 17:32:01 2000 PST | 12-31-2000
- | Mon Jan 01 17:32:01 2001 PST | 01-01-2001
-(10 rows)
+ 11 | timestamp | date
+----+------------------------------+------------
+ | epoch | 01-01-1970
+ | Wed Feb 28 17:32:01 1996 PST | 02-28-1996
+ | Thu Feb 29 17:32:01 1996 PST | 02-29-1996
+ | Fri Mar 01 17:32:01 1996 PST | 03-01-1996
+ | Mon Dec 30 17:32:01 1996 PST | 12-30-1996
+ | Tue Dec 31 17:32:01 1996 PST | 12-31-1996
+ | Fri Dec 31 17:32:01 1999 PST | 12-31-1999
+ | Sat Jan 01 17:32:01 2000 PST | 01-01-2000
+ | Tue Feb 15 23:14:30 2000 PST | 02-15-2000
+ | Sun Dec 31 17:32:01 2000 PST | 12-31-2000
+ | Mon Jan 01 17:32:01 2001 PST | 01-01-2001
+(11 rows)
-SELECT '' AS ten, f1 AS datetime, abstime( f1) AS abstime
- FROM TEMP_DATETIME
+SELECT '' AS "11", f1 AS timestamp, abstime( f1) AS abstime
+ FROM TEMP_TIMESTAMP
ORDER BY abstime;
- ten | datetime | abstime
------+------------------------------+------------------------------
- | epoch | epoch
- | Wed Feb 28 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST
- | Thu Feb 29 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST
- | Fri Mar 01 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST
- | Mon Dec 30 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST
- | Tue Dec 31 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST
- | Fri Dec 31 17:32:01 1999 PST | Fri Dec 31 17:32:01 1999 PST
- | Sat Jan 01 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST
- | Sun Dec 31 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST
- | Mon Jan 01 17:32:01 2001 PST | Mon Jan 01 17:32:01 2001 PST
-(10 rows)
+ 11 | timestamp | abstime
+----+------------------------------+------------------------------
+ | epoch | epoch
+ | Wed Feb 28 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST
+ | Thu Feb 29 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST
+ | Fri Mar 01 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST
+ | Mon Dec 30 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST
+ | Tue Dec 31 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST
+ | Fri Dec 31 17:32:01 1999 PST | Fri Dec 31 17:32:01 1999 PST
+ | Sat Jan 01 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST
+ | Tue Feb 15 23:14:30 2000 PST | Tue Feb 15 23:14:30 2000 PST
+ | Sun Dec 31 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST
+ | Mon Jan 01 17:32:01 2001 PST | Mon Jan 01 17:32:01 2001 PST
+(11 rows)
-SELECT '' AS five, f1 AS abstime, date( f1) AS date
+SELECT '' AS four, f1 AS abstime, date( f1) AS date
FROM ABSTIME_TBL
WHERE isfinite(f1) AND f1 <> abstime 'current'
ORDER BY date;
- five | abstime | date
+ four | abstime | date
------+------------------------------+------------
| Sat May 10 23:59:12 1947 PST | 05-10-1947
| epoch | 01-01-1970
@@ -519,9 +565,9 @@ SELECT '' AS five, f1 AS abstime, date( f1) AS date
| Mon May 01 00:30:30 1995 PDT | 05-01-1995
(4 rows)
-SELECT '' AS five, d1 AS datetime, abstime(d1) AS abstime
- FROM DATETIME_TBL WHERE NOT isfinite(d1);
- five | datetime | abstime
+SELECT '' AS five, d1 AS timestamp, abstime(d1) AS abstime
+ FROM TIMESTAMP_TBL WHERE NOT isfinite(d1);
+ five | timestamp | abstime
------+-----------+-----------
| invalid | invalid
| -infinity | -infinity
@@ -530,18 +576,18 @@ SELECT '' AS five, d1 AS datetime, abstime(d1) AS abstime
| invalid | invalid
(5 rows)
-SELECT '' AS three, f1 as abstime, datetime(f1) AS datetime
+SELECT '' AS three, f1 as abstime, timestamp(f1) AS timestamp
FROM ABSTIME_TBL WHERE NOT isfinite(f1);
- three | abstime | datetime
+ three | abstime | timestamp
-------+-----------+-----------
| infinity | infinity
| -infinity | -infinity
| invalid | invalid
(3 rows)
-SELECT '' AS ten, f1 AS timespan, reltime( f1) AS reltime
- FROM TIMESPAN_TBL;
- ten | timespan | reltime
+SELECT '' AS ten, f1 AS interval, reltime( f1) AS reltime
+ FROM INTERVAL_TBL;
+ ten | interval | reltime
-----+-------------------------------+-------------------------------
| @ 1 min | @ 1 min
| @ 5 hours | @ 5 hours
@@ -555,9 +601,9 @@ SELECT '' AS ten, f1 AS timespan, reltime( f1) AS reltime
| @ 5 mons 12 hours | @ 5 mons 12 hours
(10 rows)
-SELECT '' AS six, f1 as reltime, timespan( f1) AS timespan
+SELECT '' AS six, f1 as reltime, interval( f1) AS interval
FROM RELTIME_TBL;
- six | reltime | timespan
+ six | reltime | interval
-----+---------------+---------------
| @ 1 min | @ 1 min
| @ 5 hours | @ 5 hours
@@ -567,520 +613,14 @@ SELECT '' AS six, f1 as reltime, timespan( f1) AS timespan
| @ 14 secs ago | @ 14 secs ago
(6 rows)
-DROP TABLE TEMP_DATETIME;
---
--- formats
---
-SET DateStyle TO 'US,Postgres';
-SHOW DateStyle;
-NOTICE: DateStyle is Postgres with US (NonEuropean) conventions
-SELECT '' AS sixty_two, d1 AS us_postgres FROM DATETIME_TBL;
- sixty_two | us_postgres
------------+---------------------------------
- | invalid
- | -infinity
- | infinity
- | epoch
- | Mon Feb 10 17:32:01 1997 PST
- | invalid
- | invalid
- | Mon Feb 10 17:32:01.00 1997 PST
- | Mon Feb 10 17:32:02.00 1997 PST
- | Mon Feb 10 17:32:01.40 1997 PST
- | Mon Feb 10 17:32:01.50 1997 PST
- | Mon Feb 10 17:32:01.60 1997 PST
- | Thu Jan 02 00:00:00 1997 PST
- | Thu Jan 02 03:04:05 1997 PST
- | Mon Feb 10 17:32:01 1997 PST
- | Mon Feb 10 17:32:01 1997 PST
- | Mon Feb 10 17:32:01 1997 PST
- | Mon Feb 10 17:32:01 1997 PST
- | Tue Jun 10 17:32:01 1997 PDT
- | Mon Feb 10 17:32:01 1997 PST
- | Mon Feb 10 17:32:01 1997 PST
- | Mon Feb 10 17:32:00 1997 PST
- | Mon Feb 10 17:32:01 1997 PST
- | Mon Feb 10 17:32:01 1997 PST
- | Mon Feb 10 17:32:01 1997 PST
- | Mon Feb 10 17:32:01 1997 PST
- | Mon Feb 10 17:32:01 1997 PST
- | Mon Feb 10 09:32:01 1997 PST
- | Mon Feb 10 09:32:01 1997 PST
- | Mon Feb 10 09:32:01 1997 PST
- | Tue Jun 10 18:32:01 1997 PDT
- | Mon Feb 10 17:32:01 1997 PST
- | Tue Feb 11 17:32:01 1997 PST
- | Wed Feb 12 17:32:01 1997 PST
- | Thu Feb 13 17:32:01 1997 PST
- | Fri Feb 14 17:32:01 1997 PST
- | Sat Feb 15 17:32:01 1997 PST
- | Sun Feb 16 17:32:01 1997 PST
- | Tue Feb 16 17:32 0097 BC
- | Sat Feb 16 17:32:01 0097
- | Thu Feb 16 17:32:01 0597
- | Tue Feb 16 17:32:01 1097
- | Sat Feb 16 17:32:01 1697
- | Thu Feb 16 17:32:01 1797
- | Tue Feb 16 17:32:01 1897
- | Sun Feb 16 17:32:01 1997 PST
- | Sat Feb 16 17:32:01 2097
- | Wed Feb 28 17:32:01 1996 PST
- | Thu Feb 29 17:32:01 1996 PST
- | Fri Mar 01 17:32:01 1996 PST
- | Mon Dec 30 17:32:01 1996 PST
- | Tue Dec 31 17:32:01 1996 PST
- | Wed Jan 01 17:32:01 1997 PST
- | Fri Feb 28 17:32:01 1997 PST
- | Sat Mar 01 17:32:01 1997 PST
- | Tue Dec 30 17:32:01 1997 PST
- | Wed Dec 31 17:32:01 1997 PST
- | Fri Dec 31 17:32:01 1999 PST
- | Sat Jan 01 17:32:01 2000 PST
- | Sun Dec 31 17:32:01 2000 PST
- | Mon Jan 01 17:32:01 2001 PST
-(61 rows)
-
-SELECT '' AS eight, f1 AS us_postgres FROM ABSTIME_TBL;
- eight | us_postgres
--------+------------------------------
- | Sun Jan 14 03:14:21 1973 PST
- | Mon May 01 00:30:30 1995 PDT
- | epoch
- | current
- | infinity
- | -infinity
- | Sat May 10 23:59:12 1947 PST
- | invalid
-(8 rows)
-
-SET DateStyle TO 'US,ISO';
-SELECT '' AS sixty_two, d1 AS us_iso FROM DATETIME_TBL;
- sixty_two | us_iso
------------+---------------------------
- | invalid
- | -infinity
- | infinity
- | epoch
- | 1997-02-10 17:32:01-08
- | invalid
- | invalid
- | 1997-02-10 17:32:01.00-08
- | 1997-02-10 17:32:02.00-08
- | 1997-02-10 17:32:01.40-08
- | 1997-02-10 17:32:01.50-08
- | 1997-02-10 17:32:01.60-08
- | 1997-01-02 00:00:00-08
- | 1997-01-02 03:04:05-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-06-10 17:32:01-07
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:00-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 09:32:01-08
- | 1997-02-10 09:32:01-08
- | 1997-02-10 09:32:01-08
- | 1997-06-10 18:32:01-07
- | 1997-02-10 17:32:01-08
- | 1997-02-11 17:32:01-08
- | 1997-02-12 17:32:01-08
- | 1997-02-13 17:32:01-08
- | 1997-02-14 17:32:01-08
- | 1997-02-15 17:32:01-08
- | 1997-02-16 17:32:01-08
- | 0097-02-16 17:32 BC
- | 0097-02-16 17:32:01
- | 0597-02-16 17:32:01
- | 1097-02-16 17:32:01
- | 1697-02-16 17:32:01
- | 1797-02-16 17:32:01
- | 1897-02-16 17:32:01
- | 1997-02-16 17:32:01-08
- | 2097-02-16 17:32:01
- | 1996-02-28 17:32:01-08
- | 1996-02-29 17:32:01-08
- | 1996-03-01 17:32:01-08
- | 1996-12-30 17:32:01-08
- | 1996-12-31 17:32:01-08
- | 1997-01-01 17:32:01-08
- | 1997-02-28 17:32:01-08
- | 1997-03-01 17:32:01-08
- | 1997-12-30 17:32:01-08
- | 1997-12-31 17:32:01-08
- | 1999-12-31 17:32:01-08
- | 2000-01-01 17:32:01-08
- | 2000-12-31 17:32:01-08
- | 2001-01-01 17:32:01-08
-(61 rows)
-
-SELECT '' AS eight, f1 AS us_iso FROM ABSTIME_TBL;
- eight | us_iso
--------+------------------------
- | 1973-01-14 03:14:21-08
- | 1995-05-01 00:30:30-07
- | epoch
- | current
- | infinity
- | -infinity
- | 1947-05-10 23:59:12-08
- | invalid
-(8 rows)
-
-SET DateStyle TO 'US,SQL';
-SHOW DateStyle;
-NOTICE: DateStyle is SQL with US (NonEuropean) conventions
-SELECT '' AS sixty_two, d1 AS us_sql FROM DATETIME_TBL;
- sixty_two | us_sql
------------+----------------------------
- | invalid
- | -infinity
- | infinity
- | epoch
- | 02/10/1997 17:32:01.00 PST
- | invalid
- | invalid
- | 02/10/1997 17:32:01.00 PST
- | 02/10/1997 17:32:02.00 PST
- | 02/10/1997 17:32:01.40 PST
- | 02/10/1997 17:32:01.50 PST
- | 02/10/1997 17:32:01.60 PST
- | 01/02/1997 00:00:00.00 PST
- | 01/02/1997 03:04:05.00 PST
- | 02/10/1997 17:32:01.00 PST
- | 02/10/1997 17:32:01.00 PST
- | 02/10/1997 17:32:01.00 PST
- | 02/10/1997 17:32:01.00 PST
- | 06/10/1997 17:32:01.00 PDT
- | 02/10/1997 17:32:01.00 PST
- | 02/10/1997 17:32:01.00 PST
- | 02/10/1997 17:32:00.00 PST
- | 02/10/1997 17:32:01.00 PST
- | 02/10/1997 17:32:01.00 PST
- | 02/10/1997 17:32:01.00 PST
- | 02/10/1997 17:32:01.00 PST
- | 02/10/1997 17:32:01.00 PST
- | 02/10/1997 09:32:01.00 PST
- | 02/10/1997 09:32:01.00 PST
- | 02/10/1997 09:32:01.00 PST
- | 06/10/1997 18:32:01.00 PDT
- | 02/10/1997 17:32:01.00 PST
- | 02/11/1997 17:32:01.00 PST
- | 02/12/1997 17:32:01.00 PST
- | 02/13/1997 17:32:01.00 PST
- | 02/14/1997 17:32:01.00 PST
- | 02/15/1997 17:32:01.00 PST
- | 02/16/1997 17:32:01.00 PST
- | 02/16/0097 17:32 BC
- | 02/16/0097 17:32:01.00
- | 02/16/0597 17:32:01.00
- | 02/16/1097 17:32:01.00
- | 02/16/1697 17:32:01.00
- | 02/16/1797 17:32:01.00
- | 02/16/1897 17:32:01.00
- | 02/16/1997 17:32:01.00 PST
- | 02/16/2097 17:32:01.00
- | 02/28/1996 17:32:01.00 PST
- | 02/29/1996 17:32:01.00 PST
- | 03/01/1996 17:32:01.00 PST
- | 12/30/1996 17:32:01.00 PST
- | 12/31/1996 17:32:01.00 PST
- | 01/01/1997 17:32:01.00 PST
- | 02/28/1997 17:32:01.00 PST
- | 03/01/1997 17:32:01.00 PST
- | 12/30/1997 17:32:01.00 PST
- | 12/31/1997 17:32:01.00 PST
- | 12/31/1999 17:32:01.00 PST
- | 01/01/2000 17:32:01.00 PST
- | 12/31/2000 17:32:01.00 PST
- | 01/01/2001 17:32:01.00 PST
-(61 rows)
-
-SELECT '' AS eight, f1 AS us_sql FROM ABSTIME_TBL;
- eight | us_sql
--------+----------------------------
- | 01/14/1973 03:14:21.00 PST
- | 05/01/1995 00:30:30.00 PDT
- | epoch
- | current
- | infinity
- | -infinity
- | 05/10/1947 23:59:12.00 PST
- | invalid
-(8 rows)
-
-SET DateStyle TO 'European,Postgres';
-SHOW DateStyle;
-NOTICE: DateStyle is Postgres with European conventions
-INSERT INTO DATETIME_TBL VALUES('13/06/1957');
-SELECT count(*) as one FROM DATETIME_TBL WHERE d1 = 'Jun 13 1957';
- one
------
- 1
-(1 row)
-
-SELECT '' AS sixty_three, d1 AS european_postgres FROM DATETIME_TBL;
- sixty_three | european_postgres
--------------+---------------------------------
- | invalid
- | -infinity
- | infinity
- | epoch
- | Mon 10 Feb 17:32:01 1997 PST
- | invalid
- | invalid
- | Mon 10 Feb 17:32:01.00 1997 PST
- | Mon 10 Feb 17:32:02.00 1997 PST
- | Mon 10 Feb 17:32:01.40 1997 PST
- | Mon 10 Feb 17:32:01.50 1997 PST
- | Mon 10 Feb 17:32:01.60 1997 PST
- | Thu 02 Jan 00:00:00 1997 PST
- | Thu 02 Jan 03:04:05 1997 PST
- | Mon 10 Feb 17:32:01 1997 PST
- | Mon 10 Feb 17:32:01 1997 PST
- | Mon 10 Feb 17:32:01 1997 PST
- | Mon 10 Feb 17:32:01 1997 PST
- | Tue 10 Jun 17:32:01 1997 PDT
- | Mon 10 Feb 17:32:01 1997 PST
- | Mon 10 Feb 17:32:01 1997 PST
- | Mon 10 Feb 17:32:00 1997 PST
- | Mon 10 Feb 17:32:01 1997 PST
- | Mon 10 Feb 17:32:01 1997 PST
- | Mon 10 Feb 17:32:01 1997 PST
- | Mon 10 Feb 17:32:01 1997 PST
- | Mon 10 Feb 17:32:01 1997 PST
- | Mon 10 Feb 09:32:01 1997 PST
- | Mon 10 Feb 09:32:01 1997 PST
- | Mon 10 Feb 09:32:01 1997 PST
- | Tue 10 Jun 18:32:01 1997 PDT
- | Mon 10 Feb 17:32:01 1997 PST
- | Tue 11 Feb 17:32:01 1997 PST
- | Wed 12 Feb 17:32:01 1997 PST
- | Thu 13 Feb 17:32:01 1997 PST
- | Fri 14 Feb 17:32:01 1997 PST
- | Sat 15 Feb 17:32:01 1997 PST
- | Sun 16 Feb 17:32:01 1997 PST
- | Tue 16 Feb 17:32 0097 BC
- | Sat 16 Feb 17:32:01 0097
- | Thu 16 Feb 17:32:01 0597
- | Tue 16 Feb 17:32:01 1097
- | Sat 16 Feb 17:32:01 1697
- | Thu 16 Feb 17:32:01 1797
- | Tue 16 Feb 17:32:01 1897
- | Sun 16 Feb 17:32:01 1997 PST
- | Sat 16 Feb 17:32:01 2097
- | Wed 28 Feb 17:32:01 1996 PST
- | Thu 29 Feb 17:32:01 1996 PST
- | Fri 01 Mar 17:32:01 1996 PST
- | Mon 30 Dec 17:32:01 1996 PST
- | Tue 31 Dec 17:32:01 1996 PST
- | Wed 01 Jan 17:32:01 1997 PST
- | Fri 28 Feb 17:32:01 1997 PST
- | Sat 01 Mar 17:32:01 1997 PST
- | Tue 30 Dec 17:32:01 1997 PST
- | Wed 31 Dec 17:32:01 1997 PST
- | Fri 31 Dec 17:32:01 1999 PST
- | Sat 01 Jan 17:32:01 2000 PST
- | Sun 31 Dec 17:32:01 2000 PST
- | Mon 01 Jan 17:32:01 2001 PST
- | Thu 13 Jun 00:00:00 1957 PDT
-(62 rows)
-
-SELECT '' AS eight, f1 AS european_postgres FROM ABSTIME_TBL;
- eight | european_postgres
--------+------------------------------
- | Sun 14 Jan 03:14:21 1973 PST
- | Mon 01 May 00:30:30 1995 PDT
- | epoch
- | current
- | infinity
- | -infinity
- | Sat 10 May 23:59:12 1947 PST
- | invalid
-(8 rows)
-
-SET DateStyle TO 'European,ISO';
-SHOW DateStyle;
-NOTICE: DateStyle is ISO with European conventions
-SELECT '' AS sixty_three, d1 AS european_iso FROM DATETIME_TBL;
- sixty_three | european_iso
--------------+---------------------------
- | invalid
- | -infinity
- | infinity
- | epoch
- | 1997-02-10 17:32:01-08
- | invalid
- | invalid
- | 1997-02-10 17:32:01.00-08
- | 1997-02-10 17:32:02.00-08
- | 1997-02-10 17:32:01.40-08
- | 1997-02-10 17:32:01.50-08
- | 1997-02-10 17:32:01.60-08
- | 1997-01-02 00:00:00-08
- | 1997-01-02 03:04:05-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-06-10 17:32:01-07
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:00-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 17:32:01-08
- | 1997-02-10 09:32:01-08
- | 1997-02-10 09:32:01-08
- | 1997-02-10 09:32:01-08
- | 1997-06-10 18:32:01-07
- | 1997-02-10 17:32:01-08
- | 1997-02-11 17:32:01-08
- | 1997-02-12 17:32:01-08
- | 1997-02-13 17:32:01-08
- | 1997-02-14 17:32:01-08
- | 1997-02-15 17:32:01-08
- | 1997-02-16 17:32:01-08
- | 0097-02-16 17:32 BC
- | 0097-02-16 17:32:01
- | 0597-02-16 17:32:01
- | 1097-02-16 17:32:01
- | 1697-02-16 17:32:01
- | 1797-02-16 17:32:01
- | 1897-02-16 17:32:01
- | 1997-02-16 17:32:01-08
- | 2097-02-16 17:32:01
- | 1996-02-28 17:32:01-08
- | 1996-02-29 17:32:01-08
- | 1996-03-01 17:32:01-08
- | 1996-12-30 17:32:01-08
- | 1996-12-31 17:32:01-08
- | 1997-01-01 17:32:01-08
- | 1997-02-28 17:32:01-08
- | 1997-03-01 17:32:01-08
- | 1997-12-30 17:32:01-08
- | 1997-12-31 17:32:01-08
- | 1999-12-31 17:32:01-08
- | 2000-01-01 17:32:01-08
- | 2000-12-31 17:32:01-08
- | 2001-01-01 17:32:01-08
- | 1957-06-13 00:00:00-07
-(62 rows)
-
-SELECT '' AS eight, f1 AS european_iso FROM ABSTIME_TBL;
- eight | european_iso
--------+------------------------
- | 1973-01-14 03:14:21-08
- | 1995-05-01 00:30:30-07
- | epoch
- | current
- | infinity
- | -infinity
- | 1947-05-10 23:59:12-08
- | invalid
-(8 rows)
-
-SET DateStyle TO 'European,SQL';
-SHOW DateStyle;
-NOTICE: DateStyle is SQL with European conventions
-SELECT '' AS sixty_three, d1 AS european_sql FROM DATETIME_TBL;
- sixty_three | european_sql
--------------+----------------------------
- | invalid
- | -infinity
- | infinity
- | epoch
- | 10/02/1997 17:32:01.00 PST
- | invalid
- | invalid
- | 10/02/1997 17:32:01.00 PST
- | 10/02/1997 17:32:02.00 PST
- | 10/02/1997 17:32:01.40 PST
- | 10/02/1997 17:32:01.50 PST
- | 10/02/1997 17:32:01.60 PST
- | 02/01/1997 00:00:00.00 PST
- | 02/01/1997 03:04:05.00 PST
- | 10/02/1997 17:32:01.00 PST
- | 10/02/1997 17:32:01.00 PST
- | 10/02/1997 17:32:01.00 PST
- | 10/02/1997 17:32:01.00 PST
- | 10/06/1997 17:32:01.00 PDT
- | 10/02/1997 17:32:01.00 PST
- | 10/02/1997 17:32:01.00 PST
- | 10/02/1997 17:32:00.00 PST
- | 10/02/1997 17:32:01.00 PST
- | 10/02/1997 17:32:01.00 PST
- | 10/02/1997 17:32:01.00 PST
- | 10/02/1997 17:32:01.00 PST
- | 10/02/1997 17:32:01.00 PST
- | 10/02/1997 09:32:01.00 PST
- | 10/02/1997 09:32:01.00 PST
- | 10/02/1997 09:32:01.00 PST
- | 10/06/1997 18:32:01.00 PDT
- | 10/02/1997 17:32:01.00 PST
- | 11/02/1997 17:32:01.00 PST
- | 12/02/1997 17:32:01.00 PST
- | 13/02/1997 17:32:01.00 PST
- | 14/02/1997 17:32:01.00 PST
- | 15/02/1997 17:32:01.00 PST
- | 16/02/1997 17:32:01.00 PST
- | 16/02/0097 17:32 BC
- | 16/02/0097 17:32:01.00
- | 16/02/0597 17:32:01.00
- | 16/02/1097 17:32:01.00
- | 16/02/1697 17:32:01.00
- | 16/02/1797 17:32:01.00
- | 16/02/1897 17:32:01.00
- | 16/02/1997 17:32:01.00 PST
- | 16/02/2097 17:32:01.00
- | 28/02/1996 17:32:01.00 PST
- | 29/02/1996 17:32:01.00 PST
- | 01/03/1996 17:32:01.00 PST
- | 30/12/1996 17:32:01.00 PST
- | 31/12/1996 17:32:01.00 PST
- | 01/01/1997 17:32:01.00 PST
- | 28/02/1997 17:32:01.00 PST
- | 01/03/1997 17:32:01.00 PST
- | 30/12/1997 17:32:01.00 PST
- | 31/12/1997 17:32:01.00 PST
- | 31/12/1999 17:32:01.00 PST
- | 01/01/2000 17:32:01.00 PST
- | 31/12/2000 17:32:01.00 PST
- | 01/01/2001 17:32:01.00 PST
- | 13/06/1957 00:00:00.00 PDT
-(62 rows)
-
-SELECT '' AS eight, f1 AS european_sql FROM ABSTIME_TBL;
- eight | european_sql
--------+----------------------------
- | 14/01/1973 03:14:21.00 PST
- | 01/05/1995 00:30:30.00 PDT
- | epoch
- | current
- | infinity
- | -infinity
- | 10/05/1947 23:59:12.00 PST
- | invalid
-(8 rows)
-
-RESET DateStyle;
-SHOW DateStyle;
-NOTICE: DateStyle is Postgres with US (NonEuropean) conventions
+DROP TABLE TEMP_TIMESTAMP;
--
--- formats
+-- Formats
--
SET DateStyle TO 'US,Postgres';
SHOW DateStyle;
NOTICE: DateStyle is Postgres with US (NonEuropean) conventions
-SELECT '' AS sixty_two, d1 AS us_postgres FROM DATETIME_TBL;
+SELECT '' AS sixty_two, d1 AS us_postgres FROM TIMESTAMP_TBL;
sixty_two | us_postgres
-----------+---------------------------------
| invalid
@@ -1102,6 +642,7 @@ SELECT '' AS sixty_two, d1 AS us_postgres FROM DATETIME_TBL;
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Tue Jun 10 17:32:01 1997 PDT
+ | Tue Feb 15 23:14:30 2000 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:00 1997 PST
@@ -1144,7 +685,6 @@ SELECT '' AS sixty_two, d1 AS us_postgres FROM DATETIME_TBL;
| Sat Jan 01 17:32:01 2000 PST
| Sun Dec 31 17:32:01 2000 PST
| Mon Jan 01 17:32:01 2001 PST
- | Thu Jun 13 00:00:00 1957 PDT
(62 rows)
SELECT '' AS eight, f1 AS us_postgres FROM ABSTIME_TBL;
@@ -1161,7 +701,7 @@ SELECT '' AS eight, f1 AS us_postgres FROM ABSTIME_TBL;
(8 rows)
SET DateStyle TO 'US,ISO';
-SELECT '' AS sixty_two, d1 AS us_iso FROM DATETIME_TBL;
+SELECT '' AS sixty_two, d1 AS us_iso FROM TIMESTAMP_TBL;
sixty_two | us_iso
-----------+---------------------------
| invalid
@@ -1183,6 +723,7 @@ SELECT '' AS sixty_two, d1 AS us_iso FROM DATETIME_TBL;
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-06-10 17:32:01-07
+ | 2000-02-15 23:14:30-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:00-08
@@ -1225,7 +766,6 @@ SELECT '' AS sixty_two, d1 AS us_iso FROM DATETIME_TBL;
| 2000-01-01 17:32:01-08
| 2000-12-31 17:32:01-08
| 2001-01-01 17:32:01-08
- | 1957-06-13 00:00:00-07
(62 rows)
SELECT '' AS eight, f1 AS us_iso FROM ABSTIME_TBL;
@@ -1244,7 +784,7 @@ SELECT '' AS eight, f1 AS us_iso FROM ABSTIME_TBL;
SET DateStyle TO 'US,SQL';
SHOW DateStyle;
NOTICE: DateStyle is SQL with US (NonEuropean) conventions
-SELECT '' AS sixty_two, d1 AS us_sql FROM DATETIME_TBL;
+SELECT '' AS sixty_two, d1 AS us_sql FROM TIMESTAMP_TBL;
sixty_two | us_sql
-----------+----------------------------
| invalid
@@ -1266,6 +806,7 @@ SELECT '' AS sixty_two, d1 AS us_sql FROM DATETIME_TBL;
| 02/10/1997 17:32:01.00 PST
| 02/10/1997 17:32:01.00 PST
| 06/10/1997 17:32:01.00 PDT
+ | 02/15/2000 23:14:30.00 PST
| 02/10/1997 17:32:01.00 PST
| 02/10/1997 17:32:01.00 PST
| 02/10/1997 17:32:00.00 PST
@@ -1308,7 +849,6 @@ SELECT '' AS sixty_two, d1 AS us_sql FROM DATETIME_TBL;
| 01/01/2000 17:32:01.00 PST
| 12/31/2000 17:32:01.00 PST
| 01/01/2001 17:32:01.00 PST
- | 06/13/1957 00:00:00.00 PDT
(62 rows)
SELECT '' AS eight, f1 AS us_sql FROM ABSTIME_TBL;
@@ -1327,14 +867,14 @@ SELECT '' AS eight, f1 AS us_sql FROM ABSTIME_TBL;
SET DateStyle TO 'European,Postgres';
SHOW DateStyle;
NOTICE: DateStyle is Postgres with European conventions
-INSERT INTO DATETIME_TBL VALUES('13/06/1957');
-SELECT count(*) as one FROM DATETIME_TBL WHERE d1 = 'Jun 13 1957';
+INSERT INTO TIMESTAMP_TBL VALUES('13/06/1957');
+SELECT count(*) as one FROM TIMESTAMP_TBL WHERE d1 = 'Jun 13 1957';
one
-----
- 2
+ 1
(1 row)
-SELECT '' AS sixty_three, d1 AS european_postgres FROM DATETIME_TBL;
+SELECT '' AS sixty_three, d1 AS european_postgres FROM TIMESTAMP_TBL;
sixty_three | european_postgres
-------------+---------------------------------
| invalid
@@ -1356,6 +896,7 @@ SELECT '' AS sixty_three, d1 AS european_postgres FROM DATETIME_TBL;
| Mon 10 Feb 17:32:01 1997 PST
| Mon 10 Feb 17:32:01 1997 PST
| Tue 10 Jun 17:32:01 1997 PDT
+ | Tue 15 Feb 23:14:30 2000 PST
| Mon 10 Feb 17:32:01 1997 PST
| Mon 10 Feb 17:32:01 1997 PST
| Mon 10 Feb 17:32:00 1997 PST
@@ -1399,7 +940,6 @@ SELECT '' AS sixty_three, d1 AS european_postgres FROM DATETIME_TBL;
| Sun 31 Dec 17:32:01 2000 PST
| Mon 01 Jan 17:32:01 2001 PST
| Thu 13 Jun 00:00:00 1957 PDT
- | Thu 13 Jun 00:00:00 1957 PDT
(63 rows)
SELECT '' AS eight, f1 AS european_postgres FROM ABSTIME_TBL;
@@ -1418,7 +958,7 @@ SELECT '' AS eight, f1 AS european_postgres FROM ABSTIME_TBL;
SET DateStyle TO 'European,ISO';
SHOW DateStyle;
NOTICE: DateStyle is ISO with European conventions
-SELECT '' AS sixty_three, d1 AS european_iso FROM DATETIME_TBL;
+SELECT '' AS sixty_three, d1 AS european_iso FROM TIMESTAMP_TBL;
sixty_three | european_iso
-------------+---------------------------
| invalid
@@ -1440,6 +980,7 @@ SELECT '' AS sixty_three, d1 AS european_iso FROM DATETIME_TBL;
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-06-10 17:32:01-07
+ | 2000-02-15 23:14:30-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:01-08
| 1997-02-10 17:32:00-08
@@ -1483,7 +1024,6 @@ SELECT '' AS sixty_three, d1 AS european_iso FROM DATETIME_TBL;
| 2000-12-31 17:32:01-08
| 2001-01-01 17:32:01-08
| 1957-06-13 00:00:00-07
- | 1957-06-13 00:00:00-07
(63 rows)
SELECT '' AS eight, f1 AS european_iso FROM ABSTIME_TBL;
@@ -1502,7 +1042,7 @@ SELECT '' AS eight, f1 AS european_iso FROM ABSTIME_TBL;
SET DateStyle TO 'European,SQL';
SHOW DateStyle;
NOTICE: DateStyle is SQL with European conventions
-SELECT '' AS sixty_three, d1 AS european_sql FROM DATETIME_TBL;
+SELECT '' AS sixty_three, d1 AS european_sql FROM TIMESTAMP_TBL;
sixty_three | european_sql
-------------+----------------------------
| invalid
@@ -1524,6 +1064,7 @@ SELECT '' AS sixty_three, d1 AS european_sql FROM DATETIME_TBL;
| 10/02/1997 17:32:01.00 PST
| 10/02/1997 17:32:01.00 PST
| 10/06/1997 17:32:01.00 PDT
+ | 15/02/2000 23:14:30.00 PST
| 10/02/1997 17:32:01.00 PST
| 10/02/1997 17:32:01.00 PST
| 10/02/1997 17:32:00.00 PST
@@ -1567,7 +1108,6 @@ SELECT '' AS sixty_three, d1 AS european_sql FROM DATETIME_TBL;
| 31/12/2000 17:32:01.00 PST
| 01/01/2001 17:32:01.00 PST
| 13/06/1957 00:00:00.00 PDT
- | 13/06/1957 00:00:00.00 PDT
(63 rows)
SELECT '' AS eight, f1 AS european_sql FROM ABSTIME_TBL;
diff --git a/src/test/regress/expected/timespan.out b/src/test/regress/expected/interval.out
index f6bc67be2ff..3a5b24e41f7 100644
--- a/src/test/regress/expected/timespan.out
+++ b/src/test/regress/expected/interval.out
@@ -1,24 +1,24 @@
--
--- TIMESPAN
+-- INTERVAL
--
-CREATE TABLE TIMESPAN_TBL (f1 timespan);
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('@ 1 minute');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('@ 5 hour');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('@ 10 day');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('@ 34 year');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('@ 3 months');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('@ 14 seconds ago');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('6 years');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('5 months');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('5 months 12 hours');
--- badly formatted timespan
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('badly formatted timespan');
-ERROR: Bad timespan external representation 'badly formatted timespan'
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('@ 30 eons ago');
-ERROR: Bad timespan external representation '@ 30 eons ago'
--- test timespan operators
-SELECT '' AS ten, TIMESPAN_TBL.*;
+CREATE TABLE INTERVAL_TBL (f1 interval);
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 1 minute');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 5 hour');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 10 day');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 34 year');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 3 months');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 14 seconds ago');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('6 years');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours');
+-- badly formatted interval
+INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval');
+ERROR: Bad interval external representation 'badly formatted interval'
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
+ERROR: Bad interval external representation '@ 30 eons ago'
+-- test interval operators
+SELECT '' AS ten, INTERVAL_TBL.*;
ten | f1
-----+-------------------------------
| @ 1 min
@@ -33,8 +33,8 @@ SELECT '' AS ten, TIMESPAN_TBL.*;
| @ 5 mons 12 hours
(10 rows)
-SELECT '' AS nine, TIMESPAN_TBL.*
- WHERE TIMESPAN_TBL.f1 <> timespan '@ 10 days';
+SELECT '' AS nine, INTERVAL_TBL.*
+ WHERE INTERVAL_TBL.f1 <> interval '@ 10 days';
nine | f1
------+-------------------------------
| @ 1 min
@@ -48,8 +48,8 @@ SELECT '' AS nine, TIMESPAN_TBL.*
| @ 5 mons 12 hours
(9 rows)
-SELECT '' AS three, TIMESPAN_TBL.*
- WHERE TIMESPAN_TBL.f1 <= timespan '@ 5 hours';
+SELECT '' AS three, INTERVAL_TBL.*
+ WHERE INTERVAL_TBL.f1 <= interval '@ 5 hours';
three | f1
-------+---------------
| @ 1 min
@@ -57,8 +57,8 @@ SELECT '' AS three, TIMESPAN_TBL.*
| @ 14 secs ago
(3 rows)
-SELECT '' AS three, TIMESPAN_TBL.*
- WHERE TIMESPAN_TBL.f1 < timespan '@ 1 day';
+SELECT '' AS three, INTERVAL_TBL.*
+ WHERE INTERVAL_TBL.f1 < interval '@ 1 day';
three | f1
-------+---------------
| @ 1 min
@@ -66,15 +66,15 @@ SELECT '' AS three, TIMESPAN_TBL.*
| @ 14 secs ago
(3 rows)
-SELECT '' AS one, TIMESPAN_TBL.*
- WHERE TIMESPAN_TBL.f1 = timespan '@ 34 years';
+SELECT '' AS one, INTERVAL_TBL.*
+ WHERE INTERVAL_TBL.f1 = interval '@ 34 years';
one | f1
-----+------------
| @ 34 years
(1 row)
-SELECT '' AS five, TIMESPAN_TBL.*
- WHERE TIMESPAN_TBL.f1 >= timespan '@ 1 month';
+SELECT '' AS five, INTERVAL_TBL.*
+ WHERE INTERVAL_TBL.f1 >= interval '@ 1 month';
five | f1
------+-------------------
| @ 34 years
@@ -84,8 +84,8 @@ SELECT '' AS five, TIMESPAN_TBL.*
| @ 5 mons 12 hours
(5 rows)
-SELECT '' AS nine, TIMESPAN_TBL.*
- WHERE TIMESPAN_TBL.f1 > timespan '@ 3 seconds ago';
+SELECT '' AS nine, INTERVAL_TBL.*
+ WHERE INTERVAL_TBL.f1 > interval '@ 3 seconds ago';
nine | f1
------+-------------------------------
| @ 1 min
@@ -100,7 +100,7 @@ SELECT '' AS nine, TIMESPAN_TBL.*
(9 rows)
SELECT '' AS fortyfive, r1.*, r2.*
- FROM TIMESPAN_TBL r1, TIMESPAN_TBL r2
+ FROM INTERVAL_TBL r1, INTERVAL_TBL r2
WHERE r1.f1 > r2.f1
ORDER BY r1.f1, r2.f1;
fortyfive | f1 | f1
diff --git a/src/test/regress/expected/datetime.out b/src/test/regress/expected/timestamp.out
index 3cd8f44b507..37705b615c8 100644
--- a/src/test/regress/expected/datetime.out
+++ b/src/test/regress/expected/timestamp.out
@@ -4,174 +4,178 @@
-- Shorthand values
-- Not directly usable for regression testing since these are not constants.
-- So, just try to test parser and hope for the best - tgl 97/04/26
-SELECT (datetime 'today' = (datetime 'yesterday' + timespan '1 day')) as "True";
+SELECT (timestamp 'today' = (timestamp 'yesterday' + interval '1 day')) as "True";
True
------
t
(1 row)
-SELECT (datetime 'today' = (datetime 'tomorrow' - timespan '1 day')) as "True";
+SELECT (timestamp 'today' = (timestamp 'tomorrow' - interval '1 day')) as "True";
True
------
t
(1 row)
-SELECT (datetime 'tomorrow' = (datetime 'yesterday' + timespan '2 days')) as "True";
+SELECT (timestamp 'tomorrow' = (timestamp 'yesterday' + interval '2 days')) as "True";
True
------
t
(1 row)
-SELECT (datetime 'current' = 'now') as "True";
+SELECT (timestamp 'current' = 'now') as "True";
True
------
t
(1 row)
-SELECT (datetime 'now' - 'current') AS "ZeroSecs";
+SELECT (timestamp 'now' - 'current') AS "ZeroSecs";
ZeroSecs
----------
@ 0
(1 row)
SET DateStyle = 'Postgres,noneuropean';
-SELECT datetime('1994-01-01', '11:00') AS "Jan_01_1994_11am";
+SELECT timestamp('1994-01-01', '11:00') AS "Jan_01_1994_11am";
Jan_01_1994_11am
------------------------------
Sat Jan 01 11:00:00 1994 PST
(1 row)
-CREATE TABLE DATETIME_TBL( d1 datetime);
-INSERT INTO DATETIME_TBL VALUES ('current');
-INSERT INTO DATETIME_TBL VALUES ('today');
-INSERT INTO DATETIME_TBL VALUES ('yesterday');
-INSERT INTO DATETIME_TBL VALUES ('tomorrow');
-INSERT INTO DATETIME_TBL VALUES ('tomorrow EST');
-INSERT INTO DATETIME_TBL VALUES ('tomorrow zulu');
-SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = datetime 'today';
+CREATE TABLE TIMESTAMP_TBL ( d1 timestamp);
+INSERT INTO TIMESTAMP_TBL VALUES ('current');
+INSERT INTO TIMESTAMP_TBL VALUES ('today');
+INSERT INTO TIMESTAMP_TBL VALUES ('yesterday');
+INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow');
+INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow EST');
+INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow zulu');
+SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'today';
one
-----
1
(1 row)
-SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = datetime 'tomorrow';
+SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'tomorrow';
one
-----
1
(1 row)
-SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = datetime 'yesterday';
+SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'yesterday';
one
-----
1
(1 row)
-SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = datetime 'today' + timespan '1 day';
+SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'today' + interval '1 day';
one
-----
1
(1 row)
-SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = datetime 'today' - timespan '1 day';
+SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'today' - interval '1 day';
one
-----
1
(1 row)
-SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = datetime 'now';
+SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'now';
one
-----
1
(1 row)
-DELETE FROM DATETIME_TBL;
+DELETE FROM TIMESTAMP_TBL;
-- verify uniform transaction time within transaction block
-INSERT INTO DATETIME_TBL VALUES ('current');
+INSERT INTO TIMESTAMP_TBL VALUES ('current');
BEGIN;
-INSERT INTO DATETIME_TBL VALUES ('now');
-SELECT count(*) AS two FROM DATETIME_TBL WHERE d1 = datetime 'now';
+INSERT INTO TIMESTAMP_TBL VALUES ('now');
+SELECT count(*) AS two FROM TIMESTAMP_TBL WHERE d1 = timestamp 'now';
two
-----
2
(1 row)
END;
-DELETE FROM DATETIME_TBL;
+DELETE FROM TIMESTAMP_TBL;
-- Special values
-INSERT INTO DATETIME_TBL VALUES ('invalid');
-INSERT INTO DATETIME_TBL VALUES ('-infinity');
-INSERT INTO DATETIME_TBL VALUES ('infinity');
-INSERT INTO DATETIME_TBL VALUES ('epoch');
+INSERT INTO TIMESTAMP_TBL VALUES ('invalid');
+INSERT INTO TIMESTAMP_TBL VALUES ('-infinity');
+INSERT INTO TIMESTAMP_TBL VALUES ('infinity');
+INSERT INTO TIMESTAMP_TBL VALUES ('epoch');
-- Postgres v6.0 standard output format
-INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
-INSERT INTO DATETIME_TBL VALUES ('Invalid Abstime');
-INSERT INTO DATETIME_TBL VALUES ('Undefined Abstime');
+INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('Invalid Abstime');
+INSERT INTO TIMESTAMP_TBL VALUES ('Undefined Abstime');
-- Variations on Postgres v6.1 standard output format
-INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01.000001 1997 PST');
-INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01.999999 1997 PST');
-INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01.4 1997 PST');
-INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01.5 1997 PST');
-INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01.6 1997 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.000001 1997 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.999999 1997 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.4 1997 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.5 1997 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.6 1997 PST');
-- ISO 8601 format
-INSERT INTO DATETIME_TBL VALUES ('1997-01-02');
-INSERT INTO DATETIME_TBL VALUES ('1997-01-02 03:04:05');
-INSERT INTO DATETIME_TBL VALUES ('1997-02-10 17:32:01-08');
-INSERT INTO DATETIME_TBL VALUES ('1997-02-10 17:32:01-0800');
-INSERT INTO DATETIME_TBL VALUES ('1997-02-10 17:32:01 -08:00');
-INSERT INTO DATETIME_TBL VALUES ('19970210 173201 -0800');
-INSERT INTO DATETIME_TBL VALUES ('1997-06-10 17:32:01 -07:00');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-01-02');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-01-02 03:04:05');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01-08');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01-0800');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01 -08:00');
+INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 -0800');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 17:32:01 -07:00');
+-- POSIX format
+INSERT INTO TIMESTAMP_TBL VALUES ('2000-02-15 23:14:30 GMT+8');
+INSERT INTO TIMESTAMP_TBL VALUES ('2000-02-15 23:14:30 EST+3');
+ERROR: Bad timestamp external representation '2000-02-15 23:14:30 EST+3'
-- Variations for acceptable input formats
-INSERT INTO DATETIME_TBL VALUES ('Feb 10 17:32:01 1997 -0800');
-INSERT INTO DATETIME_TBL VALUES ('Feb 10 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 10 5:32PM 1997');
-INSERT INTO DATETIME_TBL VALUES ('1997/02/10 17:32:01-0800');
-INSERT INTO DATETIME_TBL VALUES ('1997-02-10 17:32:01 PST');
-INSERT INTO DATETIME_TBL VALUES ('Feb-10-1997 17:32:01 PST');
-INSERT INTO DATETIME_TBL VALUES ('02-10-1997 17:32:01 PST');
-INSERT INTO DATETIME_TBL VALUES ('19970210 173201 PST');
-INSERT INTO DATETIME_TBL VALUES ('97FEB10 5:32:01PM UTC');
-INSERT INTO DATETIME_TBL VALUES ('97/02/10 17:32:01 UTC');
-INSERT INTO DATETIME_TBL VALUES ('97.041 17:32:01 UTC');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997 -0800');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 5:32PM 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997/02/10 17:32:01-0800');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb-10-1997 17:32:01 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('02-10-1997 17:32:01 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('97FEB10 5:32:01PM UTC');
+INSERT INTO TIMESTAMP_TBL VALUES ('97/02/10 17:32:01 UTC');
+INSERT INTO TIMESTAMP_TBL VALUES ('97.041 17:32:01 UTC');
-- Check date conversion and date arithmetic
-INSERT INTO DATETIME_TBL VALUES ('1997-06-10 18:32:01 PDT');
-INSERT INTO DATETIME_TBL VALUES ('Feb 10 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 11 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 12 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 13 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 14 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 15 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 0097 BC');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 0097');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 0597');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1097');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1697');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1797');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1897');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 2097');
-INSERT INTO DATETIME_TBL VALUES ('Feb 28 17:32:01 1996');
-INSERT INTO DATETIME_TBL VALUES ('Feb 29 17:32:01 1996');
-INSERT INTO DATETIME_TBL VALUES ('Mar 01 17:32:01 1996');
-INSERT INTO DATETIME_TBL VALUES ('Dec 30 17:32:01 1996');
-INSERT INTO DATETIME_TBL VALUES ('Dec 31 17:32:01 1996');
-INSERT INTO DATETIME_TBL VALUES ('Jan 01 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 28 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 29 17:32:01 1997');
-ERROR: Bad datetime external representation 'Feb 29 17:32:01 1997'
-INSERT INTO DATETIME_TBL VALUES ('Mar 01 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Dec 30 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Dec 31 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Dec 31 17:32:01 1999');
-INSERT INTO DATETIME_TBL VALUES ('Jan 01 17:32:01 2000');
-INSERT INTO DATETIME_TBL VALUES ('Dec 31 17:32:01 2000');
-INSERT INTO DATETIME_TBL VALUES ('Jan 01 17:32:01 2001');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 18:32:01 PDT');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 11 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 12 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 13 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 14 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 15 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 0097 BC');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 0097');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 0597');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1097');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1697');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1797');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1897');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 2097');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 28 17:32:01 1996');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 29 17:32:01 1996');
+INSERT INTO TIMESTAMP_TBL VALUES ('Mar 01 17:32:01 1996');
+INSERT INTO TIMESTAMP_TBL VALUES ('Dec 30 17:32:01 1996');
+INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1996');
+INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 28 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 29 17:32:01 1997');
+ERROR: Bad timestamp external representation 'Feb 29 17:32:01 1997'
+INSERT INTO TIMESTAMP_TBL VALUES ('Mar 01 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Dec 30 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1999');
+INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2000');
+INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 2000');
+INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2001');
-- Currently unsupported syntax and ranges
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 -0097');
-ERROR: Bad datetime external representation 'Feb 16 17:32:01 -0097'
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 5097 BC');
-ERROR: Datetime out of range 'Feb 16 17:32:01 5097 BC'
-SELECT '' AS sixtythree, d1 FROM DATETIME_TBL;
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 -0097');
+ERROR: Bad timestamp external representation 'Feb 16 17:32:01 -0097'
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 5097 BC');
+ERROR: Timestamp out of range 'Feb 16 17:32:01 5097 BC'
+SELECT '' AS sixtythree, d1 FROM TIMESTAMP_TBL;
sixtythree | d1
------------+---------------------------------
| invalid
@@ -193,6 +197,7 @@ SELECT '' AS sixtythree, d1 FROM DATETIME_TBL;
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Tue Jun 10 17:32:01 1997 PDT
+ | Tue Feb 15 23:14:30 2000 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:00 1997 PST
@@ -235,11 +240,11 @@ SELECT '' AS sixtythree, d1 FROM DATETIME_TBL;
| Sat Jan 01 17:32:01 2000 PST
| Sun Dec 31 17:32:01 2000 PST
| Mon Jan 01 17:32:01 2001 PST
-(61 rows)
+(62 rows)
-- Demonstrate functions and operators
-SELECT '' AS fortythree, d1 FROM DATETIME_TBL
- WHERE d1 > datetime '1997-01-02' and d1 != datetime 'current';
+SELECT '' AS fortythree, d1 FROM TIMESTAMP_TBL
+ WHERE d1 > timestamp '1997-01-02' and d1 != timestamp 'current';
fortythree | d1
------------+---------------------------------
| infinity
@@ -255,6 +260,7 @@ SELECT '' AS fortythree, d1 FROM DATETIME_TBL
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Tue Jun 10 17:32:01 1997 PDT
+ | Tue Feb 15 23:14:30 2000 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:00 1997 PST
@@ -284,10 +290,10 @@ SELECT '' AS fortythree, d1 FROM DATETIME_TBL
| Sat Jan 01 17:32:01 2000 PST
| Sun Dec 31 17:32:01 2000 PST
| Mon Jan 01 17:32:01 2001 PST
-(42 rows)
+(43 rows)
-SELECT '' AS fifteen, d1 FROM DATETIME_TBL
- WHERE d1 < datetime '1997-01-02' and d1 != datetime 'current';
+SELECT '' AS fifteen, d1 FROM TIMESTAMP_TBL
+ WHERE d1 < timestamp '1997-01-02' and d1 != timestamp 'current';
fifteen | d1
---------+------------------------------
| -infinity
@@ -307,15 +313,15 @@ SELECT '' AS fifteen, d1 FROM DATETIME_TBL
| Wed Jan 01 17:32:01 1997 PST
(15 rows)
-SELECT '' AS one, d1 FROM DATETIME_TBL
- WHERE d1 = datetime '1997-01-02' and d1 != datetime 'current';
+SELECT '' AS one, d1 FROM TIMESTAMP_TBL
+ WHERE d1 = timestamp '1997-01-02' and d1 != timestamp 'current';
one | d1
-----+------------------------------
| Thu Jan 02 00:00:00 1997 PST
(1 row)
-SELECT '' AS fiftyeight, d1 FROM DATETIME_TBL
- WHERE d1 != datetime '1997-01-02' and d1 != datetime 'current';
+SELECT '' AS fiftyeight, d1 FROM TIMESTAMP_TBL
+ WHERE d1 != timestamp '1997-01-02' and d1 != timestamp 'current';
fiftyeight | d1
------------+---------------------------------
| -infinity
@@ -333,6 +339,7 @@ SELECT '' AS fiftyeight, d1 FROM DATETIME_TBL
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Tue Jun 10 17:32:01 1997 PDT
+ | Tue Feb 15 23:14:30 2000 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:00 1997 PST
@@ -375,10 +382,10 @@ SELECT '' AS fiftyeight, d1 FROM DATETIME_TBL
| Sat Jan 01 17:32:01 2000 PST
| Sun Dec 31 17:32:01 2000 PST
| Mon Jan 01 17:32:01 2001 PST
-(57 rows)
+(58 rows)
-SELECT '' AS sixteen, d1 FROM DATETIME_TBL
- WHERE d1 <= datetime '1997-01-02' and d1 != datetime 'current';
+SELECT '' AS sixteen, d1 FROM TIMESTAMP_TBL
+ WHERE d1 <= timestamp '1997-01-02' and d1 != timestamp 'current';
sixteen | d1
---------+------------------------------
| -infinity
@@ -399,8 +406,8 @@ SELECT '' AS sixteen, d1 FROM DATETIME_TBL
| Wed Jan 01 17:32:01 1997 PST
(16 rows)
-SELECT '' AS fortyfour, d1 FROM DATETIME_TBL
- WHERE d1 >= datetime '1997-01-02' and d1 != datetime 'current';
+SELECT '' AS fortyfour, d1 FROM TIMESTAMP_TBL
+ WHERE d1 >= timestamp '1997-01-02' and d1 != timestamp 'current';
fortyfour | d1
-----------+---------------------------------
| infinity
@@ -417,6 +424,7 @@ SELECT '' AS fortyfour, d1 FROM DATETIME_TBL
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Tue Jun 10 17:32:01 1997 PDT
+ | Tue Feb 15 23:14:30 2000 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:01 1997 PST
| Mon Feb 10 17:32:00 1997 PST
@@ -446,9 +454,9 @@ SELECT '' AS fortyfour, d1 FROM DATETIME_TBL
| Sat Jan 01 17:32:01 2000 PST
| Sun Dec 31 17:32:01 2000 PST
| Mon Jan 01 17:32:01 2001 PST
-(43 rows)
+(44 rows)
-SELECT '' AS sixtythree, d1 + timespan '1 year' AS one_year FROM DATETIME_TBL;
+SELECT '' AS sixtythree, d1 + interval '1 year' AS one_year FROM TIMESTAMP_TBL;
sixtythree | one_year
------------+---------------------------------
| invalid
@@ -470,6 +478,7 @@ SELECT '' AS sixtythree, d1 + timespan '1 year' AS one_year FROM DATETIME_TBL;
| Tue Feb 10 17:32:01 1998 PST
| Tue Feb 10 17:32:01 1998 PST
| Wed Jun 10 17:32:01 1998 PDT
+ | Thu Feb 15 23:14:30 2001 PST
| Tue Feb 10 17:32:01 1998 PST
| Tue Feb 10 17:32:01 1998 PST
| Tue Feb 10 17:32:00 1998 PST
@@ -512,9 +521,9 @@ SELECT '' AS sixtythree, d1 + timespan '1 year' AS one_year FROM DATETIME_TBL;
| Mon Jan 01 17:32:01 2001 PST
| Mon Dec 31 17:32:01 2001 PST
| Tue Jan 01 17:32:01 2002 PST
-(61 rows)
+(62 rows)
-SELECT '' AS sixtythree, d1 - timespan '1 year' AS one_year FROM DATETIME_TBL;
+SELECT '' AS sixtythree, d1 - interval '1 year' AS one_year FROM TIMESTAMP_TBL;
sixtythree | one_year
------------+---------------------------------
| invalid
@@ -536,6 +545,7 @@ SELECT '' AS sixtythree, d1 - timespan '1 year' AS one_year FROM DATETIME_TBL;
| Sat Feb 10 17:32:01 1996 PST
| Sat Feb 10 17:32:01 1996 PST
| Mon Jun 10 17:32:01 1996 PDT
+ | Mon Feb 15 23:14:30 1999 PST
| Sat Feb 10 17:32:01 1996 PST
| Sat Feb 10 17:32:01 1996 PST
| Sat Feb 10 17:32:00 1996 PST
@@ -578,13 +588,13 @@ SELECT '' AS sixtythree, d1 - timespan '1 year' AS one_year FROM DATETIME_TBL;
| Fri Jan 01 17:32:01 1999 PST
| Fri Dec 31 17:32:01 1999 PST
| Sat Jan 01 17:32:01 2000 PST
-(61 rows)
+(62 rows)
-- Casting within a BETWEEN qualifier should probably be allowed by the parser. - tgl 97/04/26
---SELECT '' AS fifty, d1 - datetime '1997-01-02' AS diff
--- FROM DATETIME_TBL WHERE d1 BETWEEN datetime '1902-01-01' AND datetime '2038-01-01';
-SELECT '' AS fifty, d1 - datetime '1997-01-02' AS diff
- FROM DATETIME_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
+--SELECT '' AS fifty, d1 - timestamp '1997-01-02' AS diff
+-- FROM TIMESTAMP_TBL WHERE d1 BETWEEN timestamp '1902-01-01' AND timestamp '2038-01-01';
+SELECT '' AS fifty, d1 - timestamp '1997-01-02' AS diff
+ FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
fifty | diff
-------+----------------------------------------
| @ 9863 days 8 hours ago
@@ -601,6 +611,7 @@ SELECT '' AS fifty, d1 - datetime '1997-01-02' AS diff
| @ 39 days 17 hours 32 mins 1 sec
| @ 39 days 17 hours 32 mins 1 sec
| @ 159 days 16 hours 32 mins 1 sec
+ | @ 1139 days 23 hours 14 mins 30 secs
| @ 39 days 17 hours 32 mins 1 sec
| @ 39 days 17 hours 32 mins 1 sec
| @ 39 days 17 hours 32 mins
@@ -635,12 +646,12 @@ SELECT '' AS fifty, d1 - datetime '1997-01-02' AS diff
| @ 1094 days 17 hours 32 mins 1 sec
| @ 1459 days 17 hours 32 mins 1 sec
| @ 1460 days 17 hours 32 mins 1 sec
-(48 rows)
+(49 rows)
SELECT '' AS fortynine, date_part( 'year', d1) AS year, date_part( 'month', d1) AS month,
date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour,
date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second
- FROM DATETIME_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
+ FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
fortynine | year | month | day | hour | minute | second
-----------+------+-------+-----+------+--------+----------
| 1969 | 12 | 31 | 16 | 0 | 0
@@ -657,6 +668,7 @@ SELECT '' AS fortynine, date_part( 'year', d1) AS year, date_part( 'month', d1)
| 1997 | 2 | 10 | 17 | 32 | 1
| 1997 | 2 | 10 | 17 | 32 | 1
| 1997 | 6 | 10 | 17 | 32 | 1
+ | 2000 | 2 | 15 | 23 | 14 | 30
| 1997 | 2 | 10 | 17 | 32 | 1
| 1997 | 2 | 10 | 17 | 32 | 1
| 1997 | 2 | 10 | 17 | 32 | 0
@@ -691,11 +703,11 @@ SELECT '' AS fortynine, date_part( 'year', d1) AS year, date_part( 'month', d1)
| 2000 | 1 | 1 | 17 | 32 | 1
| 2000 | 12 | 31 | 17 | 32 | 1
| 2001 | 1 | 1 | 17 | 32 | 1
-(48 rows)
+(49 rows)
SELECT '' AS fortynine, date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
date_part( 'usec', d1) AS usec
- FROM DATETIME_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
+ FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
fortynine | quarter | msec | usec
-----------+---------+----------------------+-------------------
| 4 | 0 | 0
@@ -723,6 +735,7 @@ SELECT '' AS fortynine, date_part( 'quarter', d1) AS quarter, date_part( 'msec',
| 1 | 0 | 0
| 1 | 0 | 0
| 1 | 0 | 0
+ | 1 | 0 | 0
| 2 | 0 | 0
| 1 | 0 | 0
| 1 | 0 | 0
@@ -746,5 +759,5 @@ SELECT '' AS fortynine, date_part( 'quarter', d1) AS quarter, date_part( 'msec',
| 1 | 0 | 0
| 4 | 0 | 0
| 1 | 0 | 0
-(48 rows)
+(49 rows)
diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source
index 3b8db49106c..a3098387750 100644
--- a/src/test/regress/output/misc.source
+++ b/src/test/regress/output/misc.source
@@ -557,7 +557,6 @@ SELECT user_relns() AS user_relns
city
copy_tbl
d_star
- datetime_tbl
default_seq
default_tbl
defaultexpr_tbl
@@ -582,6 +581,7 @@ SELECT user_relns() AS user_relns
int2_tbl
int4_tbl
int8_tbl
+ interval_tbl
iportaltest
join3_tbl
join4_tbl
@@ -616,7 +616,7 @@ SELECT user_relns() AS user_relns
tenk1
tenk2
text_tbl
- timespan_tbl
+ timestamp_tbl
tinterval_tbl
toyemp
varchar_tbl
diff --git a/src/test/regress/sql/datetime.sql b/src/test/regress/sql/datetime.sql
deleted file mode 100644
index 3b9400c4b69..00000000000
--- a/src/test/regress/sql/datetime.sql
+++ /dev/null
@@ -1,165 +0,0 @@
---
--- DATETIME
---
-
--- Shorthand values
--- Not directly usable for regression testing since these are not constants.
--- So, just try to test parser and hope for the best - tgl 97/04/26
-
-SELECT (datetime 'today' = (datetime 'yesterday' + timespan '1 day')) as "True";
-SELECT (datetime 'today' = (datetime 'tomorrow' - timespan '1 day')) as "True";
-SELECT (datetime 'tomorrow' = (datetime 'yesterday' + timespan '2 days')) as "True";
-SELECT (datetime 'current' = 'now') as "True";
-SELECT (datetime 'now' - 'current') AS "ZeroSecs";
-
-SET DateStyle = 'Postgres,noneuropean';
-SELECT datetime('1994-01-01', '11:00') AS "Jan_01_1994_11am";
-
-CREATE TABLE DATETIME_TBL( d1 datetime);
-
-INSERT INTO DATETIME_TBL VALUES ('current');
-INSERT INTO DATETIME_TBL VALUES ('today');
-INSERT INTO DATETIME_TBL VALUES ('yesterday');
-INSERT INTO DATETIME_TBL VALUES ('tomorrow');
-INSERT INTO DATETIME_TBL VALUES ('tomorrow EST');
-INSERT INTO DATETIME_TBL VALUES ('tomorrow zulu');
-
-SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = datetime 'today';
-SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = datetime 'tomorrow';
-SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = datetime 'yesterday';
-SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = datetime 'today' + timespan '1 day';
-SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = datetime 'today' - timespan '1 day';
-
-SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = datetime 'now';
-
-DELETE FROM DATETIME_TBL;
-
--- verify uniform transaction time within transaction block
-INSERT INTO DATETIME_TBL VALUES ('current');
-BEGIN;
-INSERT INTO DATETIME_TBL VALUES ('now');
-SELECT count(*) AS two FROM DATETIME_TBL WHERE d1 = datetime 'now';
-END;
-DELETE FROM DATETIME_TBL;
-
--- Special values
-INSERT INTO DATETIME_TBL VALUES ('invalid');
-INSERT INTO DATETIME_TBL VALUES ('-infinity');
-INSERT INTO DATETIME_TBL VALUES ('infinity');
-INSERT INTO DATETIME_TBL VALUES ('epoch');
-
--- Postgres v6.0 standard output format
-INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
-INSERT INTO DATETIME_TBL VALUES ('Invalid Abstime');
-INSERT INTO DATETIME_TBL VALUES ('Undefined Abstime');
-
--- Variations on Postgres v6.1 standard output format
-INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01.000001 1997 PST');
-INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01.999999 1997 PST');
-INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01.4 1997 PST');
-INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01.5 1997 PST');
-INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01.6 1997 PST');
-
--- ISO 8601 format
-INSERT INTO DATETIME_TBL VALUES ('1997-01-02');
-INSERT INTO DATETIME_TBL VALUES ('1997-01-02 03:04:05');
-INSERT INTO DATETIME_TBL VALUES ('1997-02-10 17:32:01-08');
-INSERT INTO DATETIME_TBL VALUES ('1997-02-10 17:32:01-0800');
-INSERT INTO DATETIME_TBL VALUES ('1997-02-10 17:32:01 -08:00');
-INSERT INTO DATETIME_TBL VALUES ('19970210 173201 -0800');
-INSERT INTO DATETIME_TBL VALUES ('1997-06-10 17:32:01 -07:00');
-
--- Variations for acceptable input formats
-INSERT INTO DATETIME_TBL VALUES ('Feb 10 17:32:01 1997 -0800');
-INSERT INTO DATETIME_TBL VALUES ('Feb 10 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 10 5:32PM 1997');
-INSERT INTO DATETIME_TBL VALUES ('1997/02/10 17:32:01-0800');
-INSERT INTO DATETIME_TBL VALUES ('1997-02-10 17:32:01 PST');
-INSERT INTO DATETIME_TBL VALUES ('Feb-10-1997 17:32:01 PST');
-INSERT INTO DATETIME_TBL VALUES ('02-10-1997 17:32:01 PST');
-INSERT INTO DATETIME_TBL VALUES ('19970210 173201 PST');
-INSERT INTO DATETIME_TBL VALUES ('97FEB10 5:32:01PM UTC');
-INSERT INTO DATETIME_TBL VALUES ('97/02/10 17:32:01 UTC');
-INSERT INTO DATETIME_TBL VALUES ('97.041 17:32:01 UTC');
-
--- Check date conversion and date arithmetic
-INSERT INTO DATETIME_TBL VALUES ('1997-06-10 18:32:01 PDT');
-
-INSERT INTO DATETIME_TBL VALUES ('Feb 10 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 11 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 12 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 13 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 14 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 15 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1997');
-
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 0097 BC');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 0097');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 0597');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1097');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1697');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1797');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1897');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 2097');
-
-INSERT INTO DATETIME_TBL VALUES ('Feb 28 17:32:01 1996');
-INSERT INTO DATETIME_TBL VALUES ('Feb 29 17:32:01 1996');
-INSERT INTO DATETIME_TBL VALUES ('Mar 01 17:32:01 1996');
-INSERT INTO DATETIME_TBL VALUES ('Dec 30 17:32:01 1996');
-INSERT INTO DATETIME_TBL VALUES ('Dec 31 17:32:01 1996');
-INSERT INTO DATETIME_TBL VALUES ('Jan 01 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 28 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Feb 29 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Mar 01 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Dec 30 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Dec 31 17:32:01 1997');
-INSERT INTO DATETIME_TBL VALUES ('Dec 31 17:32:01 1999');
-INSERT INTO DATETIME_TBL VALUES ('Jan 01 17:32:01 2000');
-INSERT INTO DATETIME_TBL VALUES ('Dec 31 17:32:01 2000');
-INSERT INTO DATETIME_TBL VALUES ('Jan 01 17:32:01 2001');
-
--- Currently unsupported syntax and ranges
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 -0097');
-INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 5097 BC');
-
-SELECT '' AS sixtythree, d1 FROM DATETIME_TBL;
-
--- Demonstrate functions and operators
-SELECT '' AS fortythree, d1 FROM DATETIME_TBL
- WHERE d1 > datetime '1997-01-02' and d1 != datetime 'current';
-
-SELECT '' AS fifteen, d1 FROM DATETIME_TBL
- WHERE d1 < datetime '1997-01-02' and d1 != datetime 'current';
-
-SELECT '' AS one, d1 FROM DATETIME_TBL
- WHERE d1 = datetime '1997-01-02' and d1 != datetime 'current';
-
-SELECT '' AS fiftyeight, d1 FROM DATETIME_TBL
- WHERE d1 != datetime '1997-01-02' and d1 != datetime 'current';
-
-SELECT '' AS sixteen, d1 FROM DATETIME_TBL
- WHERE d1 <= datetime '1997-01-02' and d1 != datetime 'current';
-
-SELECT '' AS fortyfour, d1 FROM DATETIME_TBL
- WHERE d1 >= datetime '1997-01-02' and d1 != datetime 'current';
-
-SELECT '' AS sixtythree, d1 + timespan '1 year' AS one_year FROM DATETIME_TBL;
-
-SELECT '' AS sixtythree, d1 - timespan '1 year' AS one_year FROM DATETIME_TBL;
-
--- Casting within a BETWEEN qualifier should probably be allowed by the parser. - tgl 97/04/26
---SELECT '' AS fifty, d1 - datetime '1997-01-02' AS diff
--- FROM DATETIME_TBL WHERE d1 BETWEEN datetime '1902-01-01' AND datetime '2038-01-01';
-SELECT '' AS fifty, d1 - datetime '1997-01-02' AS diff
- FROM DATETIME_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
-
-SELECT '' AS fortynine, date_part( 'year', d1) AS year, date_part( 'month', d1) AS month,
- date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour,
- date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second
- FROM DATETIME_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
-
-SELECT '' AS fortynine, date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
- date_part( 'usec', d1) AS usec
- FROM DATETIME_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
-
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 247910ffb64..baa59c02f30 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -3,52 +3,52 @@
--
--
--- datetime, timespan arithmetic
+-- timestamp, interval arithmetic
--
-SELECT datetime '1996-03-01' - timespan '1 second' AS "Feb 29";
-SELECT datetime '1999-03-01' - timespan '1 second' AS "Feb 28";
-SELECT datetime '2000-03-01' - timespan '1 second' AS "Feb 29";
-SELECT datetime '1999-12-01' + timespan '1 month - 1 second' AS "Dec 31";
+SELECT timestamp '1996-03-01' - interval '1 second' AS "Feb 29";
+SELECT timestamp '1999-03-01' - interval '1 second' AS "Feb 28";
+SELECT timestamp '2000-03-01' - interval '1 second' AS "Feb 29";
+SELECT timestamp '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
-CREATE TABLE TEMP_DATETIME (f1 datetime);
+CREATE TABLE TEMP_TIMESTAMP (f1 timestamp);
-- get some candidate input values
-INSERT INTO TEMP_DATETIME (f1)
- SELECT d1 FROM DATETIME_TBL
+INSERT INTO TEMP_TIMESTAMP (f1)
+ SELECT d1 FROM TIMESTAMP_TBL
WHERE d1 BETWEEN '13-jun-1957' AND '1-jan-1997'
OR d1 BETWEEN '1-jan-1999' AND '1-jan-2010';
-SELECT '' AS ten, f1 AS datetime
- FROM TEMP_DATETIME
- ORDER BY datetime;
+SELECT '' AS "11", f1 AS timestamp
+ FROM TEMP_TIMESTAMP
+ ORDER BY timestamp;
-SELECT '' AS hundred, d.f1 AS datetime, t.f1 AS timespan, d.f1 + t.f1 AS plus
- FROM TEMP_DATETIME d, TIMESPAN_TBL t
- ORDER BY plus, datetime, timespan;
+SELECT '' AS "110", d.f1 AS timestamp, t.f1 AS interval, d.f1 + t.f1 AS plus
+ FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
+ ORDER BY plus, timestamp, interval;
-SELECT '' AS hundred, d.f1 AS datetime, t.f1 AS timespan, d.f1 - t.f1 AS minus
- FROM TEMP_DATETIME d, TIMESPAN_TBL t
+SELECT '' AS "110", d.f1 AS timestamp, t.f1 AS interval, d.f1 - t.f1 AS minus
+ FROM TEMP_TIMESTAMP d, INTERVAL_TBL t
WHERE isfinite(d.f1)
- ORDER BY minus, datetime, timespan;
+ ORDER BY minus, timestamp, interval;
-SELECT '' AS ten, d.f1 AS datetime, datetime '1980-01-06 00:00 GMT' AS gpstime_zero,
- d.f1 - datetime '1980-01-06 00:00 GMT' AS difference
- FROM TEMP_DATETIME d
+SELECT '' AS "11", d.f1 AS timestamp, timestamp '1980-01-06 00:00 GMT' AS gpstime_zero,
+ d.f1 - timestamp '1980-01-06 00:00 GMT' AS difference
+ FROM TEMP_TIMESTAMP d
ORDER BY difference;
-SELECT '' AS hundred, d1.f1 AS datetime1, d2.f1 AS datetime2, d1.f1 - d2.f1 AS difference
- FROM TEMP_DATETIME d1, TEMP_DATETIME d2
- ORDER BY datetime1, datetime2, difference;
+SELECT '' AS "121", d1.f1 AS timestamp1, d2.f1 AS timestamp2, d1.f1 - d2.f1 AS difference
+ FROM TEMP_TIMESTAMP d1, TEMP_TIMESTAMP d2
+ ORDER BY timestamp1, timestamp2, difference;
-SELECT '' as fifty, d1 as datetime,
+SELECT '' as fifty, d1 as timestamp,
date_part('year', d1) AS year, date_part('month', d1) AS month,
date_part('day',d1) AS day, date_part('hour', d1) AS hour,
date_part('minute', d1) AS minute, date_part('second', d1) AS second
- FROM DATETIME_TBL
+ FROM TIMESTAMP_TBL
WHERE isfinite(d1) and d1 >= '1-jan-1900 GMT'
- ORDER BY datetime;
+ ORDER BY timestamp;
--
-- abstime, reltime arithmetic
@@ -63,52 +63,52 @@ SELECT '' AS four, f1 AS abstime,
ORDER BY abstime;
--
--- conversions
+-- Conversions
--
-SELECT '' AS ten, f1 AS datetime, date( f1) AS date
- FROM TEMP_DATETIME
- WHERE f1 <> datetime 'current'
+SELECT '' AS "11", f1 AS timestamp, date( f1) AS date
+ FROM TEMP_TIMESTAMP
+ WHERE f1 <> timestamp 'current'
ORDER BY date;
-SELECT '' AS ten, f1 AS datetime, abstime( f1) AS abstime
- FROM TEMP_DATETIME
+SELECT '' AS "11", f1 AS timestamp, abstime( f1) AS abstime
+ FROM TEMP_TIMESTAMP
ORDER BY abstime;
-SELECT '' AS five, f1 AS abstime, date( f1) AS date
+SELECT '' AS four, f1 AS abstime, date( f1) AS date
FROM ABSTIME_TBL
WHERE isfinite(f1) AND f1 <> abstime 'current'
ORDER BY date;
-SELECT '' AS five, d1 AS datetime, abstime(d1) AS abstime
- FROM DATETIME_TBL WHERE NOT isfinite(d1);
+SELECT '' AS five, d1 AS timestamp, abstime(d1) AS abstime
+ FROM TIMESTAMP_TBL WHERE NOT isfinite(d1);
-SELECT '' AS three, f1 as abstime, datetime(f1) AS datetime
+SELECT '' AS three, f1 as abstime, timestamp(f1) AS timestamp
FROM ABSTIME_TBL WHERE NOT isfinite(f1);
-SELECT '' AS ten, f1 AS timespan, reltime( f1) AS reltime
- FROM TIMESPAN_TBL;
+SELECT '' AS ten, f1 AS interval, reltime( f1) AS reltime
+ FROM INTERVAL_TBL;
-SELECT '' AS six, f1 as reltime, timespan( f1) AS timespan
+SELECT '' AS six, f1 as reltime, interval( f1) AS interval
FROM RELTIME_TBL;
-DROP TABLE TEMP_DATETIME;
+DROP TABLE TEMP_TIMESTAMP;
--
--- formats
+-- Formats
--
SET DateStyle TO 'US,Postgres';
SHOW DateStyle;
-SELECT '' AS sixty_two, d1 AS us_postgres FROM DATETIME_TBL;
+SELECT '' AS sixty_two, d1 AS us_postgres FROM TIMESTAMP_TBL;
SELECT '' AS eight, f1 AS us_postgres FROM ABSTIME_TBL;
SET DateStyle TO 'US,ISO';
-SELECT '' AS sixty_two, d1 AS us_iso FROM DATETIME_TBL;
+SELECT '' AS sixty_two, d1 AS us_iso FROM TIMESTAMP_TBL;
SELECT '' AS eight, f1 AS us_iso FROM ABSTIME_TBL;
@@ -116,7 +116,7 @@ SET DateStyle TO 'US,SQL';
SHOW DateStyle;
-SELECT '' AS sixty_two, d1 AS us_sql FROM DATETIME_TBL;
+SELECT '' AS sixty_two, d1 AS us_sql FROM TIMESTAMP_TBL;
SELECT '' AS eight, f1 AS us_sql FROM ABSTIME_TBL;
@@ -124,11 +124,11 @@ SET DateStyle TO 'European,Postgres';
SHOW DateStyle;
-INSERT INTO DATETIME_TBL VALUES('13/06/1957');
+INSERT INTO TIMESTAMP_TBL VALUES('13/06/1957');
-SELECT count(*) as one FROM DATETIME_TBL WHERE d1 = 'Jun 13 1957';
+SELECT count(*) as one FROM TIMESTAMP_TBL WHERE d1 = 'Jun 13 1957';
-SELECT '' AS sixty_three, d1 AS european_postgres FROM DATETIME_TBL;
+SELECT '' AS sixty_three, d1 AS european_postgres FROM TIMESTAMP_TBL;
SELECT '' AS eight, f1 AS european_postgres FROM ABSTIME_TBL;
@@ -136,7 +136,7 @@ SET DateStyle TO 'European,ISO';
SHOW DateStyle;
-SELECT '' AS sixty_three, d1 AS european_iso FROM DATETIME_TBL;
+SELECT '' AS sixty_three, d1 AS european_iso FROM TIMESTAMP_TBL;
SELECT '' AS eight, f1 AS european_iso FROM ABSTIME_TBL;
@@ -144,65 +144,7 @@ SET DateStyle TO 'European,SQL';
SHOW DateStyle;
-SELECT '' AS sixty_three, d1 AS european_sql FROM DATETIME_TBL;
-
-SELECT '' AS eight, f1 AS european_sql FROM ABSTIME_TBL;
-
-RESET DateStyle;
-
-SHOW DateStyle;
-
---
--- formats
---
-
-SET DateStyle TO 'US,Postgres';
-
-SHOW DateStyle;
-
-SELECT '' AS sixty_two, d1 AS us_postgres FROM DATETIME_TBL;
-
-SELECT '' AS eight, f1 AS us_postgres FROM ABSTIME_TBL;
-
-SET DateStyle TO 'US,ISO';
-
-SELECT '' AS sixty_two, d1 AS us_iso FROM DATETIME_TBL;
-
-SELECT '' AS eight, f1 AS us_iso FROM ABSTIME_TBL;
-
-SET DateStyle TO 'US,SQL';
-
-SHOW DateStyle;
-
-SELECT '' AS sixty_two, d1 AS us_sql FROM DATETIME_TBL;
-
-SELECT '' AS eight, f1 AS us_sql FROM ABSTIME_TBL;
-
-SET DateStyle TO 'European,Postgres';
-
-SHOW DateStyle;
-
-INSERT INTO DATETIME_TBL VALUES('13/06/1957');
-
-SELECT count(*) as one FROM DATETIME_TBL WHERE d1 = 'Jun 13 1957';
-
-SELECT '' AS sixty_three, d1 AS european_postgres FROM DATETIME_TBL;
-
-SELECT '' AS eight, f1 AS european_postgres FROM ABSTIME_TBL;
-
-SET DateStyle TO 'European,ISO';
-
-SHOW DateStyle;
-
-SELECT '' AS sixty_three, d1 AS european_iso FROM DATETIME_TBL;
-
-SELECT '' AS eight, f1 AS european_iso FROM ABSTIME_TBL;
-
-SET DateStyle TO 'European,SQL';
-
-SHOW DateStyle;
-
-SELECT '' AS sixty_three, d1 AS european_sql FROM DATETIME_TBL;
+SELECT '' AS sixty_three, d1 AS european_sql FROM TIMESTAMP_TBL;
SELECT '' AS eight, f1 AS european_sql FROM ABSTIME_TBL;
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
new file mode 100644
index 00000000000..fc75302efdc
--- /dev/null
+++ b/src/test/regress/sql/interval.sql
@@ -0,0 +1,48 @@
+--
+-- INTERVAL
+--
+
+CREATE TABLE INTERVAL_TBL (f1 interval);
+
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 1 minute');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 5 hour');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 10 day');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 34 year');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 3 months');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 14 seconds ago');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('6 years');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours');
+
+-- badly formatted interval
+INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
+
+-- test interval operators
+
+SELECT '' AS ten, INTERVAL_TBL.*;
+
+SELECT '' AS nine, INTERVAL_TBL.*
+ WHERE INTERVAL_TBL.f1 <> interval '@ 10 days';
+
+SELECT '' AS three, INTERVAL_TBL.*
+ WHERE INTERVAL_TBL.f1 <= interval '@ 5 hours';
+
+SELECT '' AS three, INTERVAL_TBL.*
+ WHERE INTERVAL_TBL.f1 < interval '@ 1 day';
+
+SELECT '' AS one, INTERVAL_TBL.*
+ WHERE INTERVAL_TBL.f1 = interval '@ 34 years';
+
+SELECT '' AS five, INTERVAL_TBL.*
+ WHERE INTERVAL_TBL.f1 >= interval '@ 1 month';
+
+SELECT '' AS nine, INTERVAL_TBL.*
+ WHERE INTERVAL_TBL.f1 > interval '@ 3 seconds ago';
+
+SELECT '' AS fortyfive, r1.*, r2.*
+ FROM INTERVAL_TBL r1, INTERVAL_TBL r2
+ WHERE r1.f1 > r2.f1
+ ORDER BY r1.f1, r2.f1;
+
diff --git a/src/test/regress/sql/run_check.tests b/src/test/regress/sql/run_check.tests
index 2de51d3a48c..33d89042376 100644
--- a/src/test/regress/sql/run_check.tests
+++ b/src/test/regress/sql/run_check.tests
@@ -29,8 +29,8 @@ parallel group2
test path
test polygon
test circle
- test timespan
- test datetime
+ test interval
+ test timestamp
test reltime
test tinterval
test inet
@@ -42,7 +42,7 @@ endparallel
test abstime # Depends on the reltime test
test geometry # Depends on point, lseg, box, path, polygon and circle
-test horology # Depends on timespan, datetime, reltime and abstime
+test horology # Depends on interval, timestamp, reltime and abstime
# ----------
# These four each depend on the previous one
diff --git a/src/test/regress/sql/tests b/src/test/regress/sql/tests
index aaf239b418c..69f928b005f 100644
--- a/src/test/regress/sql/tests
+++ b/src/test/regress/sql/tests
@@ -18,8 +18,8 @@ path
polygon
circle
geometry
-timespan
-datetime
+interval
+timestamp
reltime
abstime
tinterval
diff --git a/src/test/regress/sql/timespan.sql b/src/test/regress/sql/timespan.sql
deleted file mode 100644
index 0ce18514d8b..00000000000
--- a/src/test/regress/sql/timespan.sql
+++ /dev/null
@@ -1,48 +0,0 @@
---
--- TIMESPAN
---
-
-CREATE TABLE TIMESPAN_TBL (f1 timespan);
-
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('@ 1 minute');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('@ 5 hour');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('@ 10 day');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('@ 34 year');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('@ 3 months');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('@ 14 seconds ago');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('6 years');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('5 months');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('5 months 12 hours');
-
--- badly formatted timespan
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('badly formatted timespan');
-INSERT INTO TIMESPAN_TBL (f1) VALUES ('@ 30 eons ago');
-
--- test timespan operators
-
-SELECT '' AS ten, TIMESPAN_TBL.*;
-
-SELECT '' AS nine, TIMESPAN_TBL.*
- WHERE TIMESPAN_TBL.f1 <> timespan '@ 10 days';
-
-SELECT '' AS three, TIMESPAN_TBL.*
- WHERE TIMESPAN_TBL.f1 <= timespan '@ 5 hours';
-
-SELECT '' AS three, TIMESPAN_TBL.*
- WHERE TIMESPAN_TBL.f1 < timespan '@ 1 day';
-
-SELECT '' AS one, TIMESPAN_TBL.*
- WHERE TIMESPAN_TBL.f1 = timespan '@ 34 years';
-
-SELECT '' AS five, TIMESPAN_TBL.*
- WHERE TIMESPAN_TBL.f1 >= timespan '@ 1 month';
-
-SELECT '' AS nine, TIMESPAN_TBL.*
- WHERE TIMESPAN_TBL.f1 > timespan '@ 3 seconds ago';
-
-SELECT '' AS fortyfive, r1.*, r2.*
- FROM TIMESPAN_TBL r1, TIMESPAN_TBL r2
- WHERE r1.f1 > r2.f1
- ORDER BY r1.f1, r2.f1;
-
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
new file mode 100644
index 00000000000..264d40a8761
--- /dev/null
+++ b/src/test/regress/sql/timestamp.sql
@@ -0,0 +1,169 @@
+--
+-- DATETIME
+--
+
+-- Shorthand values
+-- Not directly usable for regression testing since these are not constants.
+-- So, just try to test parser and hope for the best - tgl 97/04/26
+
+SELECT (timestamp 'today' = (timestamp 'yesterday' + interval '1 day')) as "True";
+SELECT (timestamp 'today' = (timestamp 'tomorrow' - interval '1 day')) as "True";
+SELECT (timestamp 'tomorrow' = (timestamp 'yesterday' + interval '2 days')) as "True";
+SELECT (timestamp 'current' = 'now') as "True";
+SELECT (timestamp 'now' - 'current') AS "ZeroSecs";
+
+SET DateStyle = 'Postgres,noneuropean';
+SELECT timestamp('1994-01-01', '11:00') AS "Jan_01_1994_11am";
+
+CREATE TABLE TIMESTAMP_TBL ( d1 timestamp);
+
+INSERT INTO TIMESTAMP_TBL VALUES ('current');
+INSERT INTO TIMESTAMP_TBL VALUES ('today');
+INSERT INTO TIMESTAMP_TBL VALUES ('yesterday');
+INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow');
+INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow EST');
+INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow zulu');
+
+SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'today';
+SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'tomorrow';
+SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'yesterday';
+SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'today' + interval '1 day';
+SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'today' - interval '1 day';
+
+SELECT count(*) AS one FROM TIMESTAMP_TBL WHERE d1 = timestamp 'now';
+
+DELETE FROM TIMESTAMP_TBL;
+
+-- verify uniform transaction time within transaction block
+INSERT INTO TIMESTAMP_TBL VALUES ('current');
+BEGIN;
+INSERT INTO TIMESTAMP_TBL VALUES ('now');
+SELECT count(*) AS two FROM TIMESTAMP_TBL WHERE d1 = timestamp 'now';
+END;
+DELETE FROM TIMESTAMP_TBL;
+
+-- Special values
+INSERT INTO TIMESTAMP_TBL VALUES ('invalid');
+INSERT INTO TIMESTAMP_TBL VALUES ('-infinity');
+INSERT INTO TIMESTAMP_TBL VALUES ('infinity');
+INSERT INTO TIMESTAMP_TBL VALUES ('epoch');
+
+-- Postgres v6.0 standard output format
+INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('Invalid Abstime');
+INSERT INTO TIMESTAMP_TBL VALUES ('Undefined Abstime');
+
+-- Variations on Postgres v6.1 standard output format
+INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.000001 1997 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.999999 1997 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.4 1997 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.5 1997 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.6 1997 PST');
+
+-- ISO 8601 format
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-01-02');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-01-02 03:04:05');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01-08');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01-0800');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01 -08:00');
+INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 -0800');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 17:32:01 -07:00');
+
+-- POSIX format
+INSERT INTO TIMESTAMP_TBL VALUES ('2000-02-15 23:14:30 GMT+8');
+INSERT INTO TIMESTAMP_TBL VALUES ('2000-02-15 23:14:30 EST+3');
+
+-- Variations for acceptable input formats
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997 -0800');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 5:32PM 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997/02/10 17:32:01-0800');
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb-10-1997 17:32:01 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('02-10-1997 17:32:01 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 PST');
+INSERT INTO TIMESTAMP_TBL VALUES ('97FEB10 5:32:01PM UTC');
+INSERT INTO TIMESTAMP_TBL VALUES ('97/02/10 17:32:01 UTC');
+INSERT INTO TIMESTAMP_TBL VALUES ('97.041 17:32:01 UTC');
+
+-- Check date conversion and date arithmetic
+INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 18:32:01 PDT');
+
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 11 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 12 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 13 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 14 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 15 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1997');
+
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 0097 BC');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 0097');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 0597');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1097');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1697');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1797');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1897');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 2097');
+
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 28 17:32:01 1996');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 29 17:32:01 1996');
+INSERT INTO TIMESTAMP_TBL VALUES ('Mar 01 17:32:01 1996');
+INSERT INTO TIMESTAMP_TBL VALUES ('Dec 30 17:32:01 1996');
+INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1996');
+INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 28 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 29 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Mar 01 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Dec 30 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1997');
+INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1999');
+INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2000');
+INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 2000');
+INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2001');
+
+-- Currently unsupported syntax and ranges
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 -0097');
+INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 5097 BC');
+
+SELECT '' AS sixtythree, d1 FROM TIMESTAMP_TBL;
+
+-- Demonstrate functions and operators
+SELECT '' AS fortythree, d1 FROM TIMESTAMP_TBL
+ WHERE d1 > timestamp '1997-01-02' and d1 != timestamp 'current';
+
+SELECT '' AS fifteen, d1 FROM TIMESTAMP_TBL
+ WHERE d1 < timestamp '1997-01-02' and d1 != timestamp 'current';
+
+SELECT '' AS one, d1 FROM TIMESTAMP_TBL
+ WHERE d1 = timestamp '1997-01-02' and d1 != timestamp 'current';
+
+SELECT '' AS fiftyeight, d1 FROM TIMESTAMP_TBL
+ WHERE d1 != timestamp '1997-01-02' and d1 != timestamp 'current';
+
+SELECT '' AS sixteen, d1 FROM TIMESTAMP_TBL
+ WHERE d1 <= timestamp '1997-01-02' and d1 != timestamp 'current';
+
+SELECT '' AS fortyfour, d1 FROM TIMESTAMP_TBL
+ WHERE d1 >= timestamp '1997-01-02' and d1 != timestamp 'current';
+
+SELECT '' AS sixtythree, d1 + interval '1 year' AS one_year FROM TIMESTAMP_TBL;
+
+SELECT '' AS sixtythree, d1 - interval '1 year' AS one_year FROM TIMESTAMP_TBL;
+
+-- Casting within a BETWEEN qualifier should probably be allowed by the parser. - tgl 97/04/26
+--SELECT '' AS fifty, d1 - timestamp '1997-01-02' AS diff
+-- FROM TIMESTAMP_TBL WHERE d1 BETWEEN timestamp '1902-01-01' AND timestamp '2038-01-01';
+SELECT '' AS fifty, d1 - timestamp '1997-01-02' AS diff
+ FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
+
+SELECT '' AS fortynine, date_part( 'year', d1) AS year, date_part( 'month', d1) AS month,
+ date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour,
+ date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second
+ FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
+
+SELECT '' AS fortynine, date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
+ date_part( 'usec', d1) AS usec
+ FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
+
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-26 15:33:00 +0000