Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitd831055

Browse files
author
Thomas G. Lockhart
committed
Verified output from new psql.
Include a few new tests for datetime/timespan arithmetic.
1 parent24e8ca6 commitd831055

21 files changed

+5368
-4848
lines changed
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+
WHEREabstime'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+
WHEREabstime'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+
WHEREabstime'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)
[8]ページ先頭
©2009-2025 Movatter.jp