Skip to content

Commit d831055

Browse files
author
Thomas G. Lockhart
committed
Verified output from new psql.
Include a few new tests for datetime/timespan arithmetic.
1 parent 24e8ca6 commit d831055

21 files changed

+5368
-4848
lines changed

‎src/test/regress/expected/abstime.out

Lines changed: 144 additions & 128 deletions
Original file line numberDiff line numberDiff line change
@@ -1,159 +1,175 @@
1-
QUERY: CREATE TABLE ABSTIME_TBL (f1 abstime);
2-
QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('Jan 14, 1973 03:14:21');
3-
QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('Mon May 1 00:30:30 1995'::abstime);
4-
QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('epoch'::abstime);
5-
QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('current'::abstime);
6-
QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('infinity'::abstime);
7-
QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('-infinity'::abstime);
8-
QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('May 10, 1947 23:59:12');
9-
QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 35, 1946 10:00:00');
1+
--
2+
-- ABSTIME
3+
-- testing built-in time type abstime
4+
-- uses reltime and tinterval
5+
--
6+
--
7+
-- timezones may vary based not only on location but the operating
8+
-- system. the main correctness issue is that the OS may not get
9+
-- daylight savings time right for times prior to Unix epoch (jan 1 1970).
10+
--
11+
CREATE TABLE ABSTIME_TBL (f1 abstime);
12+
INSERT INTO ABSTIME_TBL (f1) VALUES ('Jan 14, 1973 03:14:21');
13+
-- was INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'now'):
14+
INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'Mon May 1 00:30:30 1995');
15+
INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'epoch');
16+
INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'current');
17+
INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'infinity');
18+
INSERT INTO ABSTIME_TBL (f1) VALUES (abstime '-infinity');
19+
INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'May 10, 1947 23:59:12');
20+
-- what happens if we specify slightly misformatted abstime?
21+
INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 35, 1946 10:00:00');
1022
ERROR: Bad abstime external representation 'Feb 35, 1946 10:00:00'
11-
QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 28, 1984 25:08:10');
23+
INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 28, 1984 25:08:10');
1224
ERROR: Bad abstime external representation 'Feb 28, 1984 25:08:10'
13-
QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('bad date format');
25+
-- badly formatted abstimes: these should result in invalid abstimes
26+
INSERT INTO ABSTIME_TBL (f1) VALUES ('bad date format');
1427
ERROR: Bad abstime external representation 'bad date format'
15-
QUERY: INSERT INTO ABSTIME_TBL (f1) VALUES ('Jun 10, 1843');
16-
QUERY: SELECT '' AS eight, ABSTIME_TBL.*;
17-
eight|f1
18-
-----+----------------------------
19-
|Sun Jan 14 03:14:21 1973 PST
20-
|Mon May 01 00:30:30 1995 PDT
21-
|epoch
22-
|current
23-
|infinity
24-
|-infinity
25-
|Sat May 10 23:59:12 1947 PST
26-
|invalid
28+
INSERT INTO ABSTIME_TBL (f1) VALUES ('Jun 10, 1843');
29+
-- test abstime operators
30+
SELECT '' AS eight, ABSTIME_TBL.*;
31+
eight | f1
32+
-------+------------------------------
33+
| Sun Jan 14 03:14:21 1973 PST
34+
| Mon May 01 00:30:30 1995 PDT
35+
| epoch
36+
| current
37+
| infinity
38+
| -infinity
39+
| Sat May 10 23:59:12 1947 PST
40+
| invalid
2741
(8 rows)
2842

29-
QUERY: SELECT '' AS six, ABSTIME_TBL.*
30-
WHERE ABSTIME_TBL.f1 < 'Jun 30, 2001'::abstime;
31-
six|f1
32-
---+----------------------------
33-
|Sun Jan 14 03:14:21 1973 PST
34-
|Mon May 01 00:30:30 1995 PDT
35-
|epoch
36-
|current
37-
|-infinity
38-
|Sat May 10 23:59:12 1947 PST
43+
SELECT '' AS six, ABSTIME_TBL.*
44+
WHERE ABSTIME_TBL.f1 < abstime 'Jun 30, 2001';
45+
six | f1
46+
-----+------------------------------
47+
| Sun Jan 14 03:14:21 1973 PST
48+
| Mon May 01 00:30:30 1995 PDT
49+
| epoch
50+
| current
51+
| -infinity
52+
| Sat May 10 23:59:12 1947 PST
3953
(6 rows)
4054

41-
QUERY: SELECT '' AS six, ABSTIME_TBL.*
42-
WHERE ABSTIME_TBL.f1 > '-infinity'::abstime;
43-
six|f1
44-
---+----------------------------
45-
|Sun Jan 14 03:14:21 1973 PST
46-
|Mon May 01 00:30:30 1995 PDT
47-
|epoch
48-
|current
49-
|infinity
50-
|Sat May 10 23:59:12 1947 PST
55+
SELECT '' AS six, ABSTIME_TBL.*
56+
WHERE ABSTIME_TBL.f1 > abstime '-infinity';
57+
six | f1
58+
-----+------------------------------
59+
| Sun Jan 14 03:14:21 1973 PST
60+
| Mon May 01 00:30:30 1995 PDT
61+
| epoch
62+
| current
63+
| infinity
64+
| Sat May 10 23:59:12 1947 PST
5165
(6 rows)
5266

53-
QUERY: SELECT '' AS six, ABSTIME_TBL.*
54-
WHERE 'May 10, 1947 23:59:12'::abstime <> ABSTIME_TBL.f1;
55-
six|f1
56-
---+----------------------------
57-
|Sun Jan 14 03:14:21 1973 PST
58-
|Mon May 01 00:30:30 1995 PDT
59-
|epoch
60-
|current
61-
|infinity
62-
|-infinity
67+
SELECT '' AS six, ABSTIME_TBL.*
68+
WHERE abstime 'May 10, 1947 23:59:12' <> ABSTIME_TBL.f1;
69+
six | f1
70+
-----+------------------------------
71+
| Sun Jan 14 03:14:21 1973 PST
72+
| Mon May 01 00:30:30 1995 PDT
73+
| epoch
74+
| current
75+
| infinity
76+
| -infinity
6377
(6 rows)
6478

65-
QUERY: SELECT '' AS one, ABSTIME_TBL.*
66-
WHERE 'current'::abstime = ABSTIME_TBL.f1;
67-
one|f1
68-
---+-------
69-
|current
79+
SELECT '' AS one, ABSTIME_TBL.*
80+
WHERE abstime 'current' = ABSTIME_TBL.f1;
81+
one | f1
82+
-----+---------
83+
| current
7084
(1 row)
7185

72-
QUERY: SELECT '' AS three, ABSTIME_TBL.*
73-
WHERE 'epoch'::abstime >= ABSTIME_TBL.f1;
74-
three|f1
75-
-----+----------------------------
76-
|epoch
77-
|-infinity
78-
|Sat May 10 23:59:12 1947 PST
86+
SELECT '' AS three, ABSTIME_TBL.*
87+
WHERE abstime 'epoch' >= ABSTIME_TBL.f1;
88+
three | f1
89+
-------+------------------------------
90+
| epoch
91+
| -infinity
92+
| Sat May 10 23:59:12 1947 PST
7993
(3 rows)
8094

81-
QUERY: SELECT '' AS four, ABSTIME_TBL.*
82-
WHERE ABSTIME_TBL.f1 <= 'Jan 14, 1973 03:14:21'::abstime;
83-
four|f1
84-
----+----------------------------
85-
|Sun Jan 14 03:14:21 1973 PST
86-
|epoch
87-
|-infinity
88-
|Sat May 10 23:59:12 1947 PST
95+
SELECT '' AS four, ABSTIME_TBL.*
96+
WHERE ABSTIME_TBL.f1 <= abstime 'Jan 14, 1973 03:14:21';
97+
four | f1
98+
------+------------------------------
99+
| Sun Jan 14 03:14:21 1973 PST
100+
| epoch
101+
| -infinity
102+
| Sat May 10 23:59:12 1947 PST
89103
(4 rows)
90104

91-
QUERY: SELECT '' AS four, ABSTIME_TBL.*
105+
SELECT '' AS four, ABSTIME_TBL.*
92106
WHERE ABSTIME_TBL.f1 <?>
93-
'["Apr 1 1950 00:00:00" "Dec 30 1999 23:00:00"]'::tinterval;
94-
four|f1
95-
----+----------------------------
96-
|Sun Jan 14 03:14:21 1973 PST
97-
|Mon May 01 00:30:30 1995 PDT
98-
|epoch
99-
|current
100-
(4 rows)
107+
tinterval '["Apr 1 1950 00:00:00" "Dec 30 1999 23:00:00"]';
108+
four | f1
109+
------+------------------------------
110+
| Sun Jan 14 03:14:21 1973 PST
111+
| Mon May 01 00:30:30 1995 PDT
112+
| epoch
113+
(3 rows)
101114

102-
QUERY: SELECT '' AS three, ABSTIME_TBL.*
103-
WHERE (ABSTIME_TBL.f1 + '@ 3 year'::reltime)
104-
< 'Jan 14 14:00:00 1977'::abstime;
105-
three|f1
106-
-----+----------------------------
107-
|Sun Jan 14 03:14:21 1973 PST
108-
|epoch
109-
|Sat May 10 23:59:12 1947 PST
115+
-- these four queries should return the same answer
116+
-- the "infinity" and "-infinity" tuples in ABSTIME_TBL cannot be added and
117+
-- therefore, should not show up in the results.
118+
SELECT '' AS three, ABSTIME_TBL.*
119+
WHERE (ABSTIME_TBL.f1 + reltime '@ 3 year') -- +3 years
120+
< abstime 'Jan 14 14:00:00 1977';
121+
three | f1
122+
-------+------------------------------
123+
| Sun Jan 14 03:14:21 1973 PST
124+
| epoch
125+
| Sat May 10 23:59:12 1947 PST
110126
(3 rows)
111127

112-
QUERY: SELECT '' AS three, ABSTIME_TBL.*
113-
WHERE (ABSTIME_TBL.f1 + '@ 3 year ago'::reltime)
114-
< 'Jan 14 14:00:00 1971'::abstime;
115-
three|f1
116-
-----+----------------------------
117-
|Sun Jan 14 03:14:21 1973 PST
118-
|epoch
119-
|Sat May 10 23:59:12 1947 PST
128+
SELECT '' AS three, ABSTIME_TBL.*
129+
WHERE (ABSTIME_TBL.f1 + reltime '@ 3 year ago') -- -3 years
130+
< abstime 'Jan 14 14:00:00 1971';
131+
three | f1
132+
-------+------------------------------
133+
| Sun Jan 14 03:14:21 1973 PST
134+
| epoch
135+
| Sat May 10 23:59:12 1947 PST
120136
(3 rows)
121137

122-
QUERY: SELECT '' AS three, ABSTIME_TBL.*
123-
WHERE (ABSTIME_TBL.f1 - '@ 3 year'::reltime)
124-
< 'Jan 14 14:00:00 1971'::abstime;
125-
three|f1
126-
-----+----------------------------
127-
|Sun Jan 14 03:14:21 1973 PST
128-
|epoch
129-
|Sat May 10 23:59:12 1947 PST
138+
SELECT '' AS three, ABSTIME_TBL.*
139+
WHERE (ABSTIME_TBL.f1 - reltime '@ 3 year') -- -(+3) years
140+
< abstime 'Jan 14 14:00:00 1971';
141+
three | f1
142+
-------+------------------------------
143+
| Sun Jan 14 03:14:21 1973 PST
144+
| epoch
145+
| Sat May 10 23:59:12 1947 PST
130146
(3 rows)
131147

132-
QUERY: SELECT '' AS three, ABSTIME_TBL.*
133-
WHERE (ABSTIME_TBL.f1 - '@ 3 year ago'::reltime)
134-
< 'Jan 14 14:00:00 1977'::abstime;
135-
three|f1
136-
-----+----------------------------
137-
|Sun Jan 14 03:14:21 1973 PST
138-
|epoch
139-
|Sat May 10 23:59:12 1947 PST
148+
SELECT '' AS three, ABSTIME_TBL.*
149+
WHERE (ABSTIME_TBL.f1 - reltime '@ 3 year ago') -- -(-3) years
150+
< abstime 'Jan 14 14:00:00 1977';
151+
three | f1
152+
-------+------------------------------
153+
| Sun Jan 14 03:14:21 1973 PST
154+
| epoch
155+
| Sat May 10 23:59:12 1947 PST
140156
(3 rows)
141157

142-
QUERY: SELECT '' AS ten, ABSTIME_TBL.f1 AS abstime, RELTIME_TBL.f1 AS reltime
158+
SELECT '' AS ten, ABSTIME_TBL.f1 AS abstime, RELTIME_TBL.f1 AS reltime
143159
WHERE (ABSTIME_TBL.f1 + RELTIME_TBL.f1)
144-
< 'Jan 14 14:00:00 1971'::abstime
160+
< abstime 'Jan 14 14:00:00 1971'
145161
ORDER BY abstime, reltime;
146-
ten|abstime |reltime
147-
---+----------------------------+-------------
148-
|Sat May 10 23:59:12 1947 PST|@ 14 secs ago
149-
|Sat May 10 23:59:12 1947 PST|@ 1 min
150-
|Sat May 10 23:59:12 1947 PST|@ 5 hours
151-
|Sat May 10 23:59:12 1947 PST|@ 10 days
152-
|Sat May 10 23:59:12 1947 PST|@ 3 mons
153-
|epoch |@ 14 secs ago
154-
|epoch |@ 1 min
155-
|epoch |@ 5 hours
156-
|epoch |@ 10 days
157-
|epoch |@ 3 mons
162+
ten | abstime | reltime
163+
-----+------------------------------+---------------
164+
| Sat May 10 23:59:12 1947 PST | @ 14 secs ago
165+
| Sat May 10 23:59:12 1947 PST | @ 1 min
166+
| Sat May 10 23:59:12 1947 PST | @ 5 hours
167+
| Sat May 10 23:59:12 1947 PST | @ 10 days
168+
| Sat May 10 23:59:12 1947 PST | @ 3 mons
169+
| epoch | @ 14 secs ago
170+
| epoch | @ 1 min
171+
| epoch | @ 5 hours
172+
| epoch | @ 10 days
173+
| epoch | @ 3 mons
158174
(10 rows)
159175

0 commit comments

Comments
 (0)