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

Commit0d93504

Browse files
author
Thomas G. Lockhart
committed
Add regression tests for date, time, and time with time zone types.
Modify date->timestamp conversion to use mktime(). This should do better than before around Daylight Savings Time transitions.
1 parent4d745da commit0d93504

File tree

9 files changed

+512
-1
lines changed

9 files changed

+512
-1
lines changed

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

Lines changed: 144 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,144 @@
1+
--
2+
-- DATE
3+
--
4+
CREATE TABLE DATE_TBL (f1 date);
5+
INSERT INTO DATE_TBL VALUES ('1957-04-09');
6+
INSERT INTO DATE_TBL VALUES ('1957-06-13');
7+
INSERT INTO DATE_TBL VALUES ('1996-02-28');
8+
INSERT INTO DATE_TBL VALUES ('1996-02-29');
9+
INSERT INTO DATE_TBL VALUES ('1996-03-01');
10+
INSERT INTO DATE_TBL VALUES ('1996-03-02');
11+
INSERT INTO DATE_TBL VALUES ('1997-02-28');
12+
INSERT INTO DATE_TBL VALUES ('1997-02-29');
13+
ERROR: Bad date external representation '1997-02-29'
14+
INSERT INTO DATE_TBL VALUES ('1997-03-01');
15+
INSERT INTO DATE_TBL VALUES ('1997-03-02');
16+
INSERT INTO DATE_TBL VALUES ('2000-04-01');
17+
INSERT INTO DATE_TBL VALUES ('2000-04-02');
18+
INSERT INTO DATE_TBL VALUES ('2000-04-03');
19+
INSERT INTO DATE_TBL VALUES ('2038-04-08');
20+
INSERT INTO DATE_TBL VALUES ('2039-04-09');
21+
INSERT INTO DATE_TBL VALUES ('2040-04-10');
22+
SELECT f1 AS "Fifteen" FROM DATE_TBL;
23+
Fifteen
24+
------------
25+
04-09-1957
26+
06-13-1957
27+
02-28-1996
28+
02-29-1996
29+
03-01-1996
30+
03-02-1996
31+
02-28-1997
32+
03-01-1997
33+
03-02-1997
34+
04-01-2000
35+
04-02-2000
36+
04-03-2000
37+
04-08-2038
38+
04-09-2039
39+
04-10-2040
40+
(15 rows)
41+
42+
SELECT f1 AS "Nine" FROM DATE_TBL WHERE f1 < '2000-01-01';
43+
Nine
44+
------------
45+
04-09-1957
46+
06-13-1957
47+
02-28-1996
48+
02-29-1996
49+
03-01-1996
50+
03-02-1996
51+
02-28-1997
52+
03-01-1997
53+
03-02-1997
54+
(9 rows)
55+
56+
SELECT f1 AS "Three" FROM DATE_TBL
57+
WHERE f1 BETWEEN '2000-01-01' AND '2001-01-01';
58+
Three
59+
------------
60+
04-01-2000
61+
04-02-2000
62+
04-03-2000
63+
(3 rows)
64+
65+
--
66+
-- Simple math
67+
-- Leave most of it for the horology tests
68+
--
69+
SELECT f1 - date '2000-01-01' AS "Days From 2K" FROM DATE_TBL;
70+
Days From 2K
71+
--------------
72+
-15607
73+
-15542
74+
-1403
75+
-1402
76+
-1401
77+
-1400
78+
-1037
79+
-1036
80+
-1035
81+
91
82+
92
83+
93
84+
13977
85+
14343
86+
14710
87+
(15 rows)
88+
89+
SELECT f1 - date 'epoch' AS "Days From Epoch" FROM DATE_TBL;
90+
Days From Epoch
91+
-----------------
92+
-4650
93+
-4585
94+
9554
95+
9555
96+
9556
97+
9557
98+
9920
99+
9921
100+
9922
101+
11048
102+
11049
103+
11050
104+
24934
105+
25300
106+
25667
107+
(15 rows)
108+
109+
SELECT date 'yesterday' - date 'today' AS "One day";
110+
One day
111+
---------
112+
-1
113+
(1 row)
114+
115+
SELECT date 'today' - date 'tomorrow' AS "One day";
116+
One day
117+
---------
118+
-1
119+
(1 row)
120+
121+
SELECT date 'yesterday' - date 'tomorrow' AS "Two days";
122+
Two days
123+
----------
124+
-2
125+
(1 row)
126+
127+
SELECT date 'tomorrow' - date 'today' AS "One day";
128+
One day
129+
---------
130+
1
131+
(1 row)
132+
133+
SELECT date 'today' - date 'yesterday' AS "One day";
134+
One day
135+
---------
136+
1
137+
(1 row)
138+
139+
SELECT date 'tomorrow' - date 'yesterday' AS "Two days";
140+
Two days
141+
----------
142+
2
143+
(1 row)
144+

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

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,48 @@
22
-- HOROLOGY
33
--
44
--
5+
-- date, time arithmetic
6+
--
7+
SELECT date '1981-02-03' + time '04:05:06' AS "Date + Time";
8+
Date + Time
9+
------------------------------
10+
Tue Feb 03 04:05:06 1981 PST
11+
(1 row)
12+
13+
SELECT date '1991-02-03' + time with time zone '04:05:06 PST' AS "Date + Time PST";
14+
Date + Time PST
15+
------------------------------
16+
Sun Feb 03 04:05:06 1991 PST
17+
(1 row)
18+
19+
SELECT date '2001-02-03' + time with time zone '04:05:06 UTC' AS "Date + Time UTC";
20+
Date + Time UTC
21+
------------------------------
22+
Fri Feb 02 20:05:06 2001 PST
23+
(1 row)
24+
25+
SELECT date '1991-02-03' + interval '2 years' AS "Add Two Years";
26+
Add Two Years
27+
------------------------------
28+
Wed Feb 03 00:00:00 1993 PST
29+
(1 row)
30+
31+
SELECT date '2001-12-13' - interval '2 years' AS "Subtract Two Years";
32+
Subtract Two Years
33+
------------------------------
34+
Mon Dec 13 00:00:00 1999 PST
35+
(1 row)
36+
37+
SELECT date '1991-02-03' - time '04:05:06' AS "Subtract Time";
38+
Subtract Time
39+
------------------------------
40+
Sat Feb 02 19:54:54 1991 PST
41+
(1 row)
42+
43+
SELECT date '1991-02-03' - time with time zone '04:05:06 UTC' AS "Subtract Time UTC";
44+
ERROR: Unable to identify an operator '-' for types 'date' and 'timetz'
45+
You will have to retype this query using an explicit cast
46+
--
547
-- timestamp, interval arithmetic
648
--
749
SELECT timestamp '1996-03-01' - interval '1 second' AS "Feb 29";

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

Lines changed: 183 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,183 @@
1+
--
2+
-- TIME
3+
--
4+
CREATE TABLE TIME_TBL (f1 time, f2 time with time zone);
5+
INSERT INTO TIME_TBL VALUES ('00:00', '00:00 PDT');
6+
INSERT INTO TIME_TBL VALUES ('01:00', '01:00 PDT');
7+
INSERT INTO TIME_TBL VALUES ('02:03', '02:03 PDT');
8+
INSERT INTO TIME_TBL VALUES ('11:59', '11:59 PDT');
9+
INSERT INTO TIME_TBL VALUES ('12:00', '12:00 PDT');
10+
INSERT INTO TIME_TBL VALUES ('12:01', '12:01 PDT');
11+
INSERT INTO TIME_TBL VALUES ('23:59', '23:59 PDT');
12+
INSERT INTO TIME_TBL VALUES ('11:59:59.99 PM', '11:59:59.99 PM PDT');
13+
SELECT f1 AS "Time", f2 AS "Time TZ" FROM TIME_TBL;
14+
Time | Time TZ
15+
----------+-------------
16+
00:00:00 | 00:00:00-07
17+
01:00:00 | 01:00:00-07
18+
02:03:00 | 02:03:00-07
19+
11:59:00 | 11:59:00-07
20+
12:00:00 | 12:00:00-07
21+
12:01:00 | 12:01:00-07
22+
23:59:00 | 23:59:00-07
23+
23:59:59 | 23:59:59-07
24+
(8 rows)
25+
26+
SELECT f1 AS "Three" FROM TIME_TBL WHERE f1 < '05:06:07';
27+
Three
28+
----------
29+
00:00:00
30+
01:00:00
31+
02:03:00
32+
(3 rows)
33+
34+
SELECT f1 AS "Five" FROM TIME_TBL WHERE f1 > '05:06:07';
35+
Five
36+
----------
37+
11:59:00
38+
12:00:00
39+
12:01:00
40+
23:59:00
41+
23:59:59
42+
(5 rows)
43+
44+
SELECT f1 AS "None" FROM TIME_TBL WHERE f1 < '00:00';
45+
None
46+
------
47+
(0 rows)
48+
49+
SELECT f1 AS "Eight" FROM TIME_TBL WHERE f1 >= '00:00';
50+
Eight
51+
----------
52+
00:00:00
53+
01:00:00
54+
02:03:00
55+
11:59:00
56+
12:00:00
57+
12:01:00
58+
23:59:00
59+
23:59:59
60+
(8 rows)
61+
62+
--
63+
-- TIME simple math
64+
--
65+
SELECT f1 + time '00:01' AS "Eight" FROM TIME_TBL;
66+
Eight
67+
--------------------
68+
@ 1 min
69+
@ 1 hour 1 min
70+
@ 2 hours 4 mins
71+
@ 12 hours
72+
@ 12 hours 1 min
73+
@ 12 hours 2 mins
74+
@ 1 day
75+
@ 1 day 59.99 secs
76+
(8 rows)
77+
78+
SELECT f1 + time '01:00' AS "Eight" FROM TIME_TBL;
79+
Eight
80+
----------------------------
81+
@ 1 hour
82+
@ 2 hours
83+
@ 3 hours 3 mins
84+
@ 12 hours 59 mins
85+
@ 13 hours
86+
@ 13 hours 1 min
87+
@ 1 day 59 mins
88+
@ 1 day 59 mins 59.99 secs
89+
(8 rows)
90+
91+
SELECT f1 + time '00:00:01.11' AS "Eight" FROM TIME_TBL;
92+
Eight
93+
------------------------------
94+
@ 1.11 secs
95+
@ 1 hour 1.11 secs
96+
@ 2 hours 3 mins 1.11 secs
97+
@ 11 hours 59 mins 1.11 secs
98+
@ 12 hours 1.11 secs
99+
@ 12 hours 1 min 1.11 secs
100+
@ 23 hours 59 mins 1.11 secs
101+
@ 1 day 1.10 secs
102+
(8 rows)
103+
104+
SELECT f1 + time '00:00:59.99' AS "Eight" FROM TIME_TBL;
105+
Eight
106+
-------------------------------
107+
@ 59.99 secs
108+
@ 1 hour 59.99 secs
109+
@ 2 hours 3 mins 59.99 secs
110+
@ 11 hours 59 mins 59.99 secs
111+
@ 12 hours 59.99 secs
112+
@ 12 hours 1 min 59.99 secs
113+
@ 23 hours 59 mins 59.99 secs
114+
@ 1 day 59.98 secs
115+
(8 rows)
116+
117+
SELECT f1 - '00:01' AS "Eight" FROM TIME_TBL;
118+
Eight
119+
-------------------------------
120+
@ 1 min ago
121+
@ 59 mins
122+
@ 2 hours 2 mins
123+
@ 11 hours 58 mins
124+
@ 11 hours 59 mins
125+
@ 12 hours
126+
@ 23 hours 58 mins
127+
@ 23 hours 58 mins 59.99 secs
128+
(8 rows)
129+
130+
SELECT f1 - '01:00' AS "Eight" FROM TIME_TBL;
131+
Eight
132+
-------------------------------
133+
@ 1 hour ago
134+
@ 0
135+
@ 1 hour 3 mins
136+
@ 10 hours 59 mins
137+
@ 11 hours
138+
@ 11 hours 1 min
139+
@ 22 hours 59 mins
140+
@ 22 hours 59 mins 59.99 secs
141+
(8 rows)
142+
143+
SELECT f1 - '00:00:01.11' AS "Eight" FROM TIME_TBL;
144+
Eight
145+
-------------------------------
146+
@ 1.11 secs ago
147+
@ 59 mins 58.89 secs
148+
@ 2 hours 2 mins 58.89 secs
149+
@ 11 hours 58 mins 58.89 secs
150+
@ 11 hours 59 mins 58.89 secs
151+
@ 12 hours 58.89 secs
152+
@ 23 hours 58 mins 58.89 secs
153+
@ 23 hours 59 mins 58.88 secs
154+
(8 rows)
155+
156+
SELECT f1 - '00:00:59.99' AS "Eight" FROM TIME_TBL;
157+
Eight
158+
------------------------------
159+
@ 59.99 secs ago
160+
@ 59 mins 0.01 secs
161+
@ 2 hours 2 mins 0.01 secs
162+
@ 11 hours 58 mins 0.01 secs
163+
@ 11 hours 59 mins 0.01 secs
164+
@ 12 hours 0.01 secs
165+
@ 23 hours 58 mins 0.01 secs
166+
@ 23 hours 59 mins
167+
(8 rows)
168+
169+
--
170+
-- TIME WITH TIME ZONE simple math
171+
--
172+
/*
173+
-- Not yet implemented
174+
-- Thomas 2000-09-09
175+
SELECT f2 + time '00:01' AS "" FROM TIME_TBL;
176+
SELECT f2 + time '01:00' AS "" FROM TIME_TBL;
177+
SELECT f2 + time '00:00:01.11' AS "" FROM TIME_TBL;
178+
SELECT f2 + '00:00:59.99' AS "" FROM TIME_TBL;
179+
SELECT f2 - '00:01' AS "" FROM TIME_TBL;
180+
SELECT f2 - '01:00' AS "" FROM TIME_TBL;
181+
SELECT f2 - '00:00:01.11' AS "" FROM TIME_TBL;
182+
SELECT f2 - '00:00:59.99' AS "" FROM TIME_TBL;
183+
*/

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp