aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/test/regress/expected/date.out25
-rw-r--r--src/test/regress/expected/interval.out84
-rw-r--r--src/test/regress/expected/time.out45
-rw-r--r--src/test/regress/expected/timetz.out61
-rw-r--r--src/test/regress/sql/date.sql5
-rw-r--r--src/test/regress/sql/interval.sql32
-rw-r--r--src/test/regress/sql/time.sql13
-rw-r--r--src/test/regress/sql/timetz.sql15
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');
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-22 08:40:45 +0000