During the discussion in [0] I noticed that the extract()/date_part()
variants for time, timetz, and interval had virtually no test coverage.
So I put some more tests together, which should be useful if we decide
to make any changes in this area per [0].
[0]:
https://www.postgresql.org/message-id/flat/42b73d2d-da12-ba9f-570a-420e0cce19d9%40phystech.edu
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 81aec8d934ba5a8d7e11b6c5999fd94e7fd30a45 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 9 Jun 2020 13:28:26 +0200
Subject: [PATCH] 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.
---
src/test/regress/expected/date.out | 25 --------
src/test/regress/expected/interval.out | 84 ++++++++++++++++++++++++++
src/test/regress/expected/time.out | 45 ++++++++++++++
src/test/regress/expected/timetz.out | 61 +++++++++++++++++++
src/test/regress/sql/date.sql | 5 --
src/test/regress/sql/interval.sql | 32 ++++++++++
src/test/regress/sql/time.sql | 13 ++++
src/test/regress/sql/timetz.sql | 15 +++++
8 files changed, 250 insertions(+), 30 deletions(-)
diff --git a/src/test/regress/expected/date.out
b/src/test/regress/expected/date.out
index 4686d0d8ca..4cdf1635f2 100644
--- a/src/test/regress/expected/date.out
+++ b/src/test/regress/expected/date.out
@@ -1111,31 +1111,6 @@ SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20
04:30:00.00000'); -- 20
20
(1 row)
--- on an interval
-SELECT EXTRACT(CENTURY FROM INTERVAL '100 y'); -- 1
- date_part
------------
- 1
-(1 row)
-
-SELECT EXTRACT(CENTURY FROM INTERVAL '99 y'); -- 0
- date_part
------------
- 0
-(1 row)
-
-SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y'); -- 0
- date_part
------------
- 0
-(1 row)
-
-SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y'); -- -1
- date_part
------------
- -1
-(1 row)
-
--
-- test trunc function!
--
diff --git a/src/test/regress/expected/interval.out
b/src/test/regress/expected/interval.out
index f772909e49..fde4be5271 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -930,3 +930,87 @@ select make_interval(secs := 7e12);
@ 1944444444 hours 26 mins 40 secs
(1 row)
+--
+-- test EXTRACT
+--
+SELECT f1,
+ EXTRACT(MICROSECOND FROM f1) AS MICROSECOND,
+ EXTRACT(MILLISECOND FROM f1) AS MILLISECOND,
+ EXTRACT(SECOND FROM f1) AS SECOND,
+ EXTRACT(MINUTE FROM f1) AS MINUTE,
+ EXTRACT(HOUR FROM f1) AS HOUR,
+ EXTRACT(DAY FROM f1) AS DAY,
+ EXTRACT(MONTH FROM f1) AS MONTH,
+ EXTRACT(QUARTER FROM f1) AS QUARTER,
+ EXTRACT(YEAR FROM f1) AS YEAR,
+ EXTRACT(DECADE FROM f1) AS DECADE,
+ EXTRACT(CENTURY FROM f1) AS CENTURY,
+ EXTRACT(MILLENNIUM FROM f1) AS MILLENNIUM,
+ EXTRACT(EPOCH FROM f1) AS EPOCH
+ FROM INTERVAL_TBL;
+ f1 | microsecond | millisecond | second | minute |
hour | day | month | quarter | year | decade | century | millennium | epoch
+-------------------------------+-------------+-------------+--------+--------+------+-----+-------+---------+------+--------+---------+------------+------------
+ @ 1 min | 0 | 0 | 0 | 1 |
0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 60
+ @ 5 hours | 0 | 0 | 0 | 0 |
5 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 18000
+ @ 10 days | 0 | 0 | 0 | 0 |
0 | 10 | 0 | 1 | 0 | 0 | 0 | 0 | 864000
+ @ 34 years | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 1 | 34 | 3 | 0 | 0 | 1072958400
+ @ 3 mons | 0 | 0 | 0 | 0 |
0 | 0 | 3 | 2 | 0 | 0 | 0 | 0 | 7776000
+ @ 14 secs ago | -14000000 | -14000 | -14 | 0 |
0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -14
+ @ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000 | 4 | 3 |
2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 93784
+ @ 6 years | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 1 | 6 | 0 | 0 | 0 | 189345600
+ @ 5 mons | 0 | 0 | 0 | 0 |
0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 12960000
+ @ 5 mons 12 hours | 0 | 0 | 0 | 0 |
12 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 13003200
+(10 rows)
+
+SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days'); -- error
+ERROR: interval units "fortnight" not recognized
+SELECT EXTRACT(TIMEZONE FROM INTERVAL '2 days'); -- error
+ERROR: interval units "timezone" not supported
+SELECT EXTRACT(DECADE FROM INTERVAL '100 y');
+ date_part
+-----------
+ 10
+(1 row)
+
+SELECT EXTRACT(DECADE FROM INTERVAL '99 y');
+ date_part
+-----------
+ 9
+(1 row)
+
+SELECT EXTRACT(DECADE FROM INTERVAL '-99 y');
+ date_part
+-----------
+ -9
+(1 row)
+
+SELECT EXTRACT(DECADE FROM INTERVAL '-100 y');
+ date_part
+-----------
+ -10
+(1 row)
+
+SELECT EXTRACT(CENTURY FROM INTERVAL '100 y');
+ date_part
+-----------
+ 1
+(1 row)
+
+SELECT EXTRACT(CENTURY FROM INTERVAL '99 y');
+ date_part
+-----------
+ 0
+(1 row)
+
+SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y');
+ date_part
+-----------
+ 0
+(1 row)
+
+SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y');
+ date_part
+-----------
+ -1
+(1 row)
+
diff --git a/src/test/regress/expected/time.out
b/src/test/regress/expected/time.out
index 780d7f5455..5303cc0c94 100644
--- a/src/test/regress/expected/time.out
+++ b/src/test/regress/expected/time.out
@@ -127,3 +127,48 @@ ERROR: operator is not unique: time without time zone +
time without time zone
LINE 1: SELECT f1 + time '00:01' AS "Illegal" FROM TIME_TBL;
^
HINT: Could not choose a best candidate operator. You might need to add
explicit type casts.
+--
+-- test EXTRACT
+--
+SELECT EXTRACT(MICROSECOND FROM TIME '2020-05-26 13:30:25.575401');
+ date_part
+-----------
+ 25575401
+(1 row)
+
+SELECT EXTRACT(MILLISECOND FROM TIME '2020-05-26 13:30:25.575401');
+ date_part
+-----------
+ 25575.401
+(1 row)
+
+SELECT EXTRACT(SECOND FROM TIME '2020-05-26 13:30:25.575401');
+ date_part
+-----------
+ 25.575401
+(1 row)
+
+SELECT EXTRACT(MINUTE FROM TIME '2020-05-26 13:30:25.575401');
+ date_part
+-----------
+ 30
+(1 row)
+
+SELECT EXTRACT(HOUR FROM TIME '2020-05-26 13:30:25.575401');
+ date_part
+-----------
+ 13
+(1 row)
+
+SELECT EXTRACT(DAY FROM TIME '2020-05-26 13:30:25.575401'); -- error
+ERROR: "time" units "day" not recognized
+SELECT EXTRACT(FORTNIGHT FROM TIME '2020-05-26 13:30:25.575401'); -- error
+ERROR: "time" units "fortnight" not recognized
+SELECT EXTRACT(TIMEZONE FROM TIME '2020-05-26 13:30:25.575401'); -- error
+ERROR: "time" units "timezone" not recognized
+SELECT EXTRACT(EPOCH FROM TIME '2020-05-26 13:30:25.575401');
+ date_part
+--------------
+ 48625.575401
+(1 row)
+
diff --git a/src/test/regress/expected/timetz.out
b/src/test/regress/expected/timetz.out
index 6be408f528..038bb5fa09 100644
--- a/src/test/regress/expected/timetz.out
+++ b/src/test/regress/expected/timetz.out
@@ -144,3 +144,64 @@ ERROR: operator does not exist: time with time zone +
time with time zone
LINE 1: SELECT f1 + time with time zone '00:01' AS "Illegal" FROM TI...
^
HINT: No operator matches the given name and argument types. You might need
to add explicit type casts.
+--
+-- test EXTRACT
+--
+SELECT EXTRACT(MICROSECOND FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+ date_part
+-----------
+ 25575401
+(1 row)
+
+SELECT EXTRACT(MILLISECOND FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+ date_part
+-----------
+ 25575.401
+(1 row)
+
+SELECT EXTRACT(SECOND FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+ date_part
+-----------
+ 25.575401
+(1 row)
+
+SELECT EXTRACT(MINUTE FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+ date_part
+-----------
+ 30
+(1 row)
+
+SELECT EXTRACT(HOUR FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+ date_part
+-----------
+ 13
+(1 row)
+
+SELECT EXTRACT(DAY FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04'); -- error
+ERROR: "time with time zone" units "day" not recognized
+SELECT EXTRACT(FORTNIGHT FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04'); -- error
+ERROR: "time with time zone" units "fortnight" not recognized
+SELECT EXTRACT(TIMEZONE FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+ date_part
+-----------
+ -14400
+(1 row)
+
+SELECT EXTRACT(TIMEZONE_HOUR FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+ date_part
+-----------
+ -4
+(1 row)
+
+SELECT EXTRACT(TIMEZONE_MINUTE FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+ date_part
+-----------
+ 0
+(1 row)
+
+SELECT EXTRACT(EPOCH FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+ date_part
+--------------
+ 63025.575401
+(1 row)
+
diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql
index 4c5b94a14a..1c3adf70ce 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -263,11 +263,6 @@ CREATE TABLE DATE_TBL (f1 date);
-- on a timestamp.
SELECT EXTRACT(CENTURY FROM NOW())>=21 AS True; -- true
SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20
--- on an interval
-SELECT EXTRACT(CENTURY FROM INTERVAL '100 y'); -- 1
-SELECT EXTRACT(CENTURY FROM INTERVAL '99 y'); -- 0
-SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y'); -- 0
-SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y'); -- -1
--
-- test trunc function!
--
diff --git a/src/test/regress/sql/interval.sql
b/src/test/regress/sql/interval.sql
index eb1e84f053..fe6d096a21 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -311,3 +311,35 @@ CREATE TABLE INTERVAL_MULDIV_TBL (span interval);
select make_interval(secs := 'inf');
select make_interval(secs := 'NaN');
select make_interval(secs := 7e12);
+
+--
+-- test EXTRACT
+--
+SELECT f1,
+ EXTRACT(MICROSECOND FROM f1) AS MICROSECOND,
+ EXTRACT(MILLISECOND FROM f1) AS MILLISECOND,
+ EXTRACT(SECOND FROM f1) AS SECOND,
+ EXTRACT(MINUTE FROM f1) AS MINUTE,
+ EXTRACT(HOUR FROM f1) AS HOUR,
+ EXTRACT(DAY FROM f1) AS DAY,
+ EXTRACT(MONTH FROM f1) AS MONTH,
+ EXTRACT(QUARTER FROM f1) AS QUARTER,
+ EXTRACT(YEAR FROM f1) AS YEAR,
+ EXTRACT(DECADE FROM f1) AS DECADE,
+ EXTRACT(CENTURY FROM f1) AS CENTURY,
+ EXTRACT(MILLENNIUM FROM f1) AS MILLENNIUM,
+ EXTRACT(EPOCH FROM f1) AS EPOCH
+ FROM INTERVAL_TBL;
+
+SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days'); -- error
+SELECT EXTRACT(TIMEZONE FROM INTERVAL '2 days'); -- error
+
+SELECT EXTRACT(DECADE FROM INTERVAL '100 y');
+SELECT EXTRACT(DECADE FROM INTERVAL '99 y');
+SELECT EXTRACT(DECADE FROM INTERVAL '-99 y');
+SELECT EXTRACT(DECADE FROM INTERVAL '-100 y');
+
+SELECT EXTRACT(CENTURY FROM INTERVAL '100 y');
+SELECT EXTRACT(CENTURY FROM INTERVAL '99 y');
+SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y');
+SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y');
diff --git a/src/test/regress/sql/time.sql b/src/test/regress/sql/time.sql
index ea5f8b639f..688bd6b75b 100644
--- a/src/test/regress/sql/time.sql
+++ b/src/test/regress/sql/time.sql
@@ -50,3 +50,16 @@ CREATE TABLE TIME_TBL (f1 time(2));
-- where we do mixed-type arithmetic. - thomas 2000-12-02
SELECT f1 + time '00:01' AS "Illegal" FROM TIME_TBL;
+
+--
+-- test EXTRACT
+--
+SELECT EXTRACT(MICROSECOND FROM TIME '2020-05-26 13:30:25.575401');
+SELECT EXTRACT(MILLISECOND FROM TIME '2020-05-26 13:30:25.575401');
+SELECT EXTRACT(SECOND FROM TIME '2020-05-26 13:30:25.575401');
+SELECT EXTRACT(MINUTE FROM TIME '2020-05-26 13:30:25.575401');
+SELECT EXTRACT(HOUR FROM TIME '2020-05-26 13:30:25.575401');
+SELECT EXTRACT(DAY FROM TIME '2020-05-26 13:30:25.575401'); -- error
+SELECT EXTRACT(FORTNIGHT FROM TIME '2020-05-26 13:30:25.575401'); -- error
+SELECT EXTRACT(TIMEZONE FROM TIME '2020-05-26 13:30:25.575401'); -- error
+SELECT EXTRACT(EPOCH FROM TIME '2020-05-26 13:30:25.575401');
diff --git a/src/test/regress/sql/timetz.sql b/src/test/regress/sql/timetz.sql
index a1fa4ef3b7..b699e4b03c 100644
--- a/src/test/regress/sql/timetz.sql
+++ b/src/test/regress/sql/timetz.sql
@@ -55,3 +55,18 @@ CREATE TABLE TIMETZ_TBL (f1 time(2) with time zone);
-- where we do mixed-type arithmetic. - thomas 2000-12-02
SELECT f1 + time with time zone '00:01' AS "Illegal" FROM TIMETZ_TBL;
+
+--
+-- test EXTRACT
+--
+SELECT EXTRACT(MICROSECOND FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+SELECT EXTRACT(MILLISECOND FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+SELECT EXTRACT(SECOND FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+SELECT EXTRACT(MINUTE FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+SELECT EXTRACT(HOUR FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+SELECT EXTRACT(DAY FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04'); -- error
+SELECT EXTRACT(FORTNIGHT FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04'); -- error
+SELECT EXTRACT(TIMEZONE FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+SELECT EXTRACT(TIMEZONE_HOUR FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+SELECT EXTRACT(TIMEZONE_MINUTE FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
+SELECT EXTRACT(EPOCH FROM TIME WITH TIME ZONE '2020-05-26
13:30:25.575401-04');
--
2.27.0
