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

8 files changed, 250 insertions, 30 deletions

diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out
index 4686d0d8cab..4cdf1635f2a 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 f772909e49c..fde4be52711 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 780d7f54557..5303cc0c947 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 6be408f5282..038bb5fa094 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 4c5b94a14ad..1c3adf70ced 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -263,11 +263,6 @@ SELECT EXTRACT(DECADE FROM DATE '0012-12-31 BC'); --  -2
 -- 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 eb1e84f053e..fe6d096a216 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -311,3 +311,35 @@ select make_interval(months := 'NaN'::float::int);
 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 ea5f8b639f0..688bd6b75ba 100644
--- a/src/test/regress/sql/time.sql
+++ b/src/test/regress/sql/time.sql
@@ -50,3 +50,16 @@ SELECT '25:00:00'::time;  -- not allowed
 -- 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 a1fa4ef3b7f..b699e4b03c4 100644
--- a/src/test/regress/sql/timetz.sql
+++ b/src/test/regress/sql/timetz.sql
@@ -55,3 +55,18 @@ SELECT '25:00:00'::timetz;  -- not allowed
 -- 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');