🌐 AI搜索 & 代理 主页
Skip to content

Commit 378badc

Browse files
peterepetere
committed
Add test coverage for EXTRACT()
The variants for time and timetz had zero test coverage, the variant for interval only very little. This adds practically full coverage for those functions. Reviewed-by: Vik Fearing <vik@postgresfriends.org> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://www.postgresql.org/message-id/flat/c3306ac7-fcae-a1b8-1e30-6a379d605bcb%402ndquadrant.com
1 parent cc07264 commit 378badc

File tree

8 files changed

+250
-30
lines changed

8 files changed

+250
-30
lines changed

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

Lines changed: 0 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -1111,31 +1111,6 @@ SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20
11111111
20
11121112
(1 row)
11131113

1114-
-- on an interval
1115-
SELECT EXTRACT(CENTURY FROM INTERVAL '100 y'); -- 1
1116-
date_part
1117-
-----------
1118-
1
1119-
(1 row)
1120-
1121-
SELECT EXTRACT(CENTURY FROM INTERVAL '99 y'); -- 0
1122-
date_part
1123-
-----------
1124-
0
1125-
(1 row)
1126-
1127-
SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y'); -- 0
1128-
date_part
1129-
-----------
1130-
0
1131-
(1 row)
1132-
1133-
SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y'); -- -1
1134-
date_part
1135-
-----------
1136-
-1
1137-
(1 row)
1138-
11391114
--
11401115
-- test trunc function!
11411116
--

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

Lines changed: 84 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -930,3 +930,87 @@ select make_interval(secs := 7e12);
930930
@ 1944444444 hours 26 mins 40 secs
931931
(1 row)
932932

933+
--
934+
-- test EXTRACT
935+
--
936+
SELECT f1,
937+
EXTRACT(MICROSECOND FROM f1) AS MICROSECOND,
938+
EXTRACT(MILLISECOND FROM f1) AS MILLISECOND,
939+
EXTRACT(SECOND FROM f1) AS SECOND,
940+
EXTRACT(MINUTE FROM f1) AS MINUTE,
941+
EXTRACT(HOUR FROM f1) AS HOUR,
942+
EXTRACT(DAY FROM f1) AS DAY,
943+
EXTRACT(MONTH FROM f1) AS MONTH,
944+
EXTRACT(QUARTER FROM f1) AS QUARTER,
945+
EXTRACT(YEAR FROM f1) AS YEAR,
946+
EXTRACT(DECADE FROM f1) AS DECADE,
947+
EXTRACT(CENTURY FROM f1) AS CENTURY,
948+
EXTRACT(MILLENNIUM FROM f1) AS MILLENNIUM,
949+
EXTRACT(EPOCH FROM f1) AS EPOCH
950+
FROM INTERVAL_TBL;
951+
f1 | microsecond | millisecond | second | minute | hour | day | month | quarter | year | decade | century | millennium | epoch
952+
-------------------------------+-------------+-------------+--------+--------+------+-----+-------+---------+------+--------+---------+------------+------------
953+
@ 1 min | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 60
954+
@ 5 hours | 0 | 0 | 0 | 0 | 5 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 18000
955+
@ 10 days | 0 | 0 | 0 | 0 | 0 | 10 | 0 | 1 | 0 | 0 | 0 | 0 | 864000
956+
@ 34 years | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 34 | 3 | 0 | 0 | 1072958400
957+
@ 3 mons | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 2 | 0 | 0 | 0 | 0 | 7776000
958+
@ 14 secs ago | -14000000 | -14000 | -14 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -14
959+
@ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000 | 4 | 3 | 2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 93784
960+
@ 6 years | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 6 | 0 | 0 | 0 | 189345600
961+
@ 5 mons | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 12960000
962+
@ 5 mons 12 hours | 0 | 0 | 0 | 0 | 12 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 13003200
963+
(10 rows)
964+
965+
SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days'); -- error
966+
ERROR: interval units "fortnight" not recognized
967+
SELECT EXTRACT(TIMEZONE FROM INTERVAL '2 days'); -- error
968+
ERROR: interval units "timezone" not supported
969+
SELECT EXTRACT(DECADE FROM INTERVAL '100 y');
970+
date_part
971+
-----------
972+
10
973+
(1 row)
974+
975+
SELECT EXTRACT(DECADE FROM INTERVAL '99 y');
976+
date_part
977+
-----------
978+
9
979+
(1 row)
980+
981+
SELECT EXTRACT(DECADE FROM INTERVAL '-99 y');
982+
date_part
983+
-----------
984+
-9
985+
(1 row)
986+
987+
SELECT EXTRACT(DECADE FROM INTERVAL '-100 y');
988+
date_part
989+
-----------
990+
-10
991+
(1 row)
992+
993+
SELECT EXTRACT(CENTURY FROM INTERVAL '100 y');
994+
date_part
995+
-----------
996+
1
997+
(1 row)
998+
999+
SELECT EXTRACT(CENTURY FROM INTERVAL '99 y');
1000+
date_part
1001+
-----------
1002+
0
1003+
(1 row)
1004+
1005+
SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y');
1006+
date_part
1007+
-----------
1008+
0
1009+
(1 row)
1010+
1011+
SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y');
1012+
date_part
1013+
-----------
1014+
-1
1015+
(1 row)
1016+

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

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -127,3 +127,48 @@ ERROR: operator is not unique: time without time zone + time without time zone
127127
LINE 1: SELECT f1 + time '00:01' AS "Illegal" FROM TIME_TBL;
128128
^
129129
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
130+
--
131+
-- test EXTRACT
132+
--
133+
SELECT EXTRACT(MICROSECOND FROM TIME '2020-05-26 13:30:25.575401');
134+
date_part
135+
-----------
136+
25575401
137+
(1 row)
138+
139+
SELECT EXTRACT(MILLISECOND FROM TIME '2020-05-26 13:30:25.575401');
140+
date_part
141+
-----------
142+
25575.401
143+
(1 row)
144+
145+
SELECT EXTRACT(SECOND FROM TIME '2020-05-26 13:30:25.575401');
146+
date_part
147+
-----------
148+
25.575401
149+
(1 row)
150+
151+
SELECT EXTRACT(MINUTE FROM TIME '2020-05-26 13:30:25.575401');
152+
date_part
153+
-----------
154+
30
155+
(1 row)
156+
157+
SELECT EXTRACT(HOUR FROM TIME '2020-05-26 13:30:25.575401');
158+
date_part
159+
-----------
160+
13
161+
(1 row)
162+
163+
SELECT EXTRACT(DAY FROM TIME '2020-05-26 13:30:25.575401'); -- error
164+
ERROR: "time" units "day" not recognized
165+
SELECT EXTRACT(FORTNIGHT FROM TIME '2020-05-26 13:30:25.575401'); -- error
166+
ERROR: "time" units "fortnight" not recognized
167+
SELECT EXTRACT(TIMEZONE FROM TIME '2020-05-26 13:30:25.575401'); -- error
168+
ERROR: "time" units "timezone" not recognized
169+
SELECT EXTRACT(EPOCH FROM TIME '2020-05-26 13:30:25.575401');
170+
date_part
171+
--------------
172+
48625.575401
173+
(1 row)
174+

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

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -144,3 +144,64 @@ ERROR: operator does not exist: time with time zone + time with time zone
144144
LINE 1: SELECT f1 + time with time zone '00:01' AS "Illegal" FROM TI...
145145
^
146146
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
147+
--
148+
-- test EXTRACT
149+
--
150+
SELECT EXTRACT(MICROSECOND FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
151+
date_part
152+
-----------
153+
25575401
154+
(1 row)
155+
156+
SELECT EXTRACT(MILLISECOND FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
157+
date_part
158+
-----------
159+
25575.401
160+
(1 row)
161+
162+
SELECT EXTRACT(SECOND FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
163+
date_part
164+
-----------
165+
25.575401
166+
(1 row)
167+
168+
SELECT EXTRACT(MINUTE FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
169+
date_part
170+
-----------
171+
30
172+
(1 row)
173+
174+
SELECT EXTRACT(HOUR FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
175+
date_part
176+
-----------
177+
13
178+
(1 row)
179+
180+
SELECT EXTRACT(DAY FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); -- error
181+
ERROR: "time with time zone" units "day" not recognized
182+
SELECT EXTRACT(FORTNIGHT FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); -- error
183+
ERROR: "time with time zone" units "fortnight" not recognized
184+
SELECT EXTRACT(TIMEZONE FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
185+
date_part
186+
-----------
187+
-14400
188+
(1 row)
189+
190+
SELECT EXTRACT(TIMEZONE_HOUR FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
191+
date_part
192+
-----------
193+
-4
194+
(1 row)
195+
196+
SELECT EXTRACT(TIMEZONE_MINUTE FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
197+
date_part
198+
-----------
199+
0
200+
(1 row)
201+
202+
SELECT EXTRACT(EPOCH FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
203+
date_part
204+
--------------
205+
63025.575401
206+
(1 row)
207+

‎src/test/regress/sql/date.sql‎

Lines changed: 0 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -263,11 +263,6 @@ SELECT EXTRACT(DECADE FROM DATE '0012-12-31 BC'); -- -2
263263
-- on a timestamp.
264264
SELECT EXTRACT(CENTURY FROM NOW())>=21 AS True; -- true
265265
SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20
266-
-- on an interval
267-
SELECT EXTRACT(CENTURY FROM INTERVAL '100 y'); -- 1
268-
SELECT EXTRACT(CENTURY FROM INTERVAL '99 y'); -- 0
269-
SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y'); -- 0
270-
SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y'); -- -1
271266
--
272267
-- test trunc function!
273268
--

‎src/test/regress/sql/interval.sql‎

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -311,3 +311,35 @@ select make_interval(months := 'NaN'::float::int);
311311
select make_interval(secs := 'inf');
312312
select make_interval(secs := 'NaN');
313313
select make_interval(secs := 7e12);
314+
315+
--
316+
-- test EXTRACT
317+
--
318+
SELECT f1,
319+
EXTRACT(MICROSECOND FROM f1) AS MICROSECOND,
320+
EXTRACT(MILLISECOND FROM f1) AS MILLISECOND,
321+
EXTRACT(SECOND FROM f1) AS SECOND,
322+
EXTRACT(MINUTE FROM f1) AS MINUTE,
323+
EXTRACT(HOUR FROM f1) AS HOUR,
324+
EXTRACT(DAY FROM f1) AS DAY,
325+
EXTRACT(MONTH FROM f1) AS MONTH,
326+
EXTRACT(QUARTER FROM f1) AS QUARTER,
327+
EXTRACT(YEAR FROM f1) AS YEAR,
328+
EXTRACT(DECADE FROM f1) AS DECADE,
329+
EXTRACT(CENTURY FROM f1) AS CENTURY,
330+
EXTRACT(MILLENNIUM FROM f1) AS MILLENNIUM,
331+
EXTRACT(EPOCH FROM f1) AS EPOCH
332+
FROM INTERVAL_TBL;
333+
334+
SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days'); -- error
335+
SELECT EXTRACT(TIMEZONE FROM INTERVAL '2 days'); -- error
336+
337+
SELECT EXTRACT(DECADE FROM INTERVAL '100 y');
338+
SELECT EXTRACT(DECADE FROM INTERVAL '99 y');
339+
SELECT EXTRACT(DECADE FROM INTERVAL '-99 y');
340+
SELECT EXTRACT(DECADE FROM INTERVAL '-100 y');
341+
342+
SELECT EXTRACT(CENTURY FROM INTERVAL '100 y');
343+
SELECT EXTRACT(CENTURY FROM INTERVAL '99 y');
344+
SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y');
345+
SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y');

‎src/test/regress/sql/time.sql‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -50,3 +50,16 @@ SELECT '25:00:00'::time; -- not allowed
5050
-- where we do mixed-type arithmetic. - thomas 2000-12-02
5151

5252
SELECT f1 + time '00:01' AS "Illegal" FROM TIME_TBL;
53+
54+
--
55+
-- test EXTRACT
56+
--
57+
SELECT EXTRACT(MICROSECOND FROM TIME '2020-05-26 13:30:25.575401');
58+
SELECT EXTRACT(MILLISECOND FROM TIME '2020-05-26 13:30:25.575401');
59+
SELECT EXTRACT(SECOND FROM TIME '2020-05-26 13:30:25.575401');
60+
SELECT EXTRACT(MINUTE FROM TIME '2020-05-26 13:30:25.575401');
61+
SELECT EXTRACT(HOUR FROM TIME '2020-05-26 13:30:25.575401');
62+
SELECT EXTRACT(DAY FROM TIME '2020-05-26 13:30:25.575401'); -- error
63+
SELECT EXTRACT(FORTNIGHT FROM TIME '2020-05-26 13:30:25.575401'); -- error
64+
SELECT EXTRACT(TIMEZONE FROM TIME '2020-05-26 13:30:25.575401'); -- error
65+
SELECT EXTRACT(EPOCH FROM TIME '2020-05-26 13:30:25.575401');

‎src/test/regress/sql/timetz.sql‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,3 +55,18 @@ SELECT '25:00:00'::timetz; -- not allowed
5555
-- where we do mixed-type arithmetic. - thomas 2000-12-02
5656

5757
SELECT f1 + time with time zone '00:01' AS "Illegal" FROM TIMETZ_TBL;
58+
59+
--
60+
-- test EXTRACT
61+
--
62+
SELECT EXTRACT(MICROSECOND FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
63+
SELECT EXTRACT(MILLISECOND FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
64+
SELECT EXTRACT(SECOND FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
65+
SELECT EXTRACT(MINUTE FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
66+
SELECT EXTRACT(HOUR FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
67+
SELECT EXTRACT(DAY FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); -- error
68+
SELECT EXTRACT(FORTNIGHT FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04'); -- error
69+
SELECT EXTRACT(TIMEZONE FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
70+
SELECT EXTRACT(TIMEZONE_HOUR FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
71+
SELECT EXTRACT(TIMEZONE_MINUTE FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
72+
SELECT EXTRACT(EPOCH FROM TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');

0 commit comments

Comments
 (0)