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

Commit378badc

Browse files
committed
Add test coverage for EXTRACT()
The variants for time and timetz had zero test coverage, the variantfor interval only very little. This adds practically full coveragefor 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 parentcc07264 commit378badc

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(CENTURYFROM NOW())>=21AS True;-- true
265265
SELECT EXTRACT(CENTURYFROMTIMESTAMP'1970-03-20 04:30:00.00000');-- 20
266-
-- on an interval
267-
SELECT EXTRACT(CENTURYFROM INTERVAL'100 y');-- 1
268-
SELECT EXTRACT(CENTURYFROM INTERVAL'99 y');-- 0
269-
SELECT EXTRACT(CENTURYFROM INTERVAL'-99 y');-- 0
270-
SELECT EXTRACT(CENTURYFROM 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(MICROSECONDFROM f1)AS MICROSECOND,
320+
EXTRACT(MILLISECONDFROM f1)AS MILLISECOND,
321+
EXTRACT(SECONDFROM f1)AS SECOND,
322+
EXTRACT(MINUTEFROM f1)AS MINUTE,
323+
EXTRACT(HOURFROM f1)AS HOUR,
324+
EXTRACT(DAYFROM f1)AS DAY,
325+
EXTRACT(MONTHFROM f1)AS MONTH,
326+
EXTRACT(QUARTERFROM f1)AS QUARTER,
327+
EXTRACT(YEARFROM f1)AS YEAR,
328+
EXTRACT(DECADEFROM f1)AS DECADE,
329+
EXTRACT(CENTURYFROM f1)AS CENTURY,
330+
EXTRACT(MILLENNIUMFROM f1)AS MILLENNIUM,
331+
EXTRACT(EPOCHFROM f1)AS EPOCH
332+
FROM INTERVAL_TBL;
333+
334+
SELECT EXTRACT(FORTNIGHTFROM INTERVAL'2 days');-- error
335+
SELECT EXTRACT(TIMEZONEFROM INTERVAL'2 days');-- error
336+
337+
SELECT EXTRACT(DECADEFROM INTERVAL'100 y');
338+
SELECT EXTRACT(DECADEFROM INTERVAL'99 y');
339+
SELECT EXTRACT(DECADEFROM INTERVAL'-99 y');
340+
SELECT EXTRACT(DECADEFROM INTERVAL'-100 y');
341+
342+
SELECT EXTRACT(CENTURYFROM INTERVAL'100 y');
343+
SELECT EXTRACT(CENTURYFROM INTERVAL'99 y');
344+
SELECT EXTRACT(CENTURYFROM INTERVAL'-99 y');
345+
SELECT EXTRACT(CENTURYFROM 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(MICROSECONDFROMTIME'2020-05-26 13:30:25.575401');
58+
SELECT EXTRACT(MILLISECONDFROMTIME'2020-05-26 13:30:25.575401');
59+
SELECT EXTRACT(SECONDFROMTIME'2020-05-26 13:30:25.575401');
60+
SELECT EXTRACT(MINUTEFROMTIME'2020-05-26 13:30:25.575401');
61+
SELECT EXTRACT(HOURFROMTIME'2020-05-26 13:30:25.575401');
62+
SELECT EXTRACT(DAYFROMTIME'2020-05-26 13:30:25.575401');-- error
63+
SELECT EXTRACT(FORTNIGHTFROMTIME'2020-05-26 13:30:25.575401');-- error
64+
SELECT EXTRACT(TIMEZONEFROMTIME'2020-05-26 13:30:25.575401');-- error
65+
SELECT EXTRACT(EPOCHFROMTIME'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(MICROSECONDFROMTIME WITH TIME ZONE'2020-05-26 13:30:25.575401-04');
63+
SELECT EXTRACT(MILLISECONDFROMTIME WITH TIME ZONE'2020-05-26 13:30:25.575401-04');
64+
SELECT EXTRACT(SECONDFROMTIME WITH TIME ZONE'2020-05-26 13:30:25.575401-04');
65+
SELECT EXTRACT(MINUTEFROMTIME WITH TIME ZONE'2020-05-26 13:30:25.575401-04');
66+
SELECT EXTRACT(HOURFROMTIME WITH TIME ZONE'2020-05-26 13:30:25.575401-04');
67+
SELECT EXTRACT(DAYFROMTIME WITH TIME ZONE'2020-05-26 13:30:25.575401-04');-- error
68+
SELECT EXTRACT(FORTNIGHTFROMTIME WITH TIME ZONE'2020-05-26 13:30:25.575401-04');-- error
69+
SELECT EXTRACT(TIMEZONEFROMTIME WITH TIME ZONE'2020-05-26 13:30:25.575401-04');
70+
SELECT EXTRACT(TIMEZONE_HOURFROMTIME WITH TIME ZONE'2020-05-26 13:30:25.575401-04');
71+
SELECT EXTRACT(TIMEZONE_MINUTEFROMTIME WITH TIME ZONE'2020-05-26 13:30:25.575401-04');
72+
SELECT EXTRACT(EPOCHFROMTIME WITH TIME ZONE'2020-05-26 13:30:25.575401-04');

0 commit comments

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