Skip to content

Commit 0d93504

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 parent 4d745da commit 0d93504

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)