aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/test/regress/expected/date.out192
-rw-r--r--src/test/regress/expected/expressions.out4
-rw-r--r--src/test/regress/sql/date.sql49
3 files changed, 228 insertions, 17 deletions
diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out
index 4cdf1635f2a..d035fe1f1e0 100644
--- a/src/test/regress/expected/date.out
+++ b/src/test/regress/expected/date.out
@@ -21,9 +21,10 @@ INSERT INTO DATE_TBL VALUES ('2000-04-03');
INSERT INTO DATE_TBL VALUES ('2038-04-08');
INSERT INTO DATE_TBL VALUES ('2039-04-09');
INSERT INTO DATE_TBL VALUES ('2040-04-10');
-SELECT f1 AS "Fifteen" FROM DATE_TBL;
- Fifteen
-------------
+INSERT INTO DATE_TBL VALUES ('2040-04-10 BC');
+SELECT f1 FROM DATE_TBL;
+ f1
+---------------
04-09-1957
06-13-1957
02-28-1996
@@ -39,11 +40,12 @@ SELECT f1 AS "Fifteen" FROM DATE_TBL;
04-08-2038
04-09-2039
04-10-2040
-(15 rows)
+ 04-10-2040 BC
+(16 rows)
-SELECT f1 AS "Nine" FROM DATE_TBL WHERE f1 < '2000-01-01';
- Nine
-------------
+SELECT f1 FROM DATE_TBL WHERE f1 < '2000-01-01';
+ f1
+---------------
04-09-1957
06-13-1957
02-28-1996
@@ -53,11 +55,12 @@ SELECT f1 AS "Nine" FROM DATE_TBL WHERE f1 < '2000-01-01';
02-28-1997
03-01-1997
03-02-1997
-(9 rows)
+ 04-10-2040 BC
+(10 rows)
-SELECT f1 AS "Three" FROM DATE_TBL
+SELECT f1 FROM DATE_TBL
WHERE f1 BETWEEN '2000-01-01' AND '2001-01-01';
- Three
+ f1
------------
04-01-2000
04-02-2000
@@ -860,7 +863,8 @@ SELECT f1 - date '2000-01-01' AS "Days From 2K" FROM DATE_TBL;
13977
14343
14710
-(15 rows)
+ -1475115
+(16 rows)
SELECT f1 - date 'epoch' AS "Days From Epoch" FROM DATE_TBL;
Days From Epoch
@@ -880,7 +884,8 @@ SELECT f1 - date 'epoch' AS "Days From Epoch" FROM DATE_TBL;
24934
25300
25667
-(15 rows)
+ -1464158
+(16 rows)
SELECT date 'yesterday' - date 'today' AS "One day";
One day
@@ -921,6 +926,43 @@ SELECT date 'tomorrow' - date 'yesterday' AS "Two days";
--
-- test extract!
--
+SELECT f1 as "date",
+ date_part('year', f1) AS year,
+ date_part('month', f1) AS month,
+ date_part('day', f1) AS day,
+ date_part('quarter', f1) AS quarter,
+ date_part('decade', f1) AS decade,
+ date_part('century', f1) AS century,
+ date_part('millennium', f1) AS millennium,
+ date_part('isoyear', f1) AS isoyear,
+ date_part('week', f1) AS week,
+ date_part('dow', f1) AS dow,
+ date_part('isodow', f1) AS isodow,
+ date_part('doy', f1) AS doy,
+ date_part('julian', f1) AS julian,
+ date_part('epoch', f1) AS epoch
+ FROM date_tbl;
+ date | year | month | day | quarter | decade | century | millennium | isoyear | week | dow | isodow | doy | julian | epoch
+---------------+-------+-------+-----+---------+--------+---------+------------+---------+------+-----+--------+-----+---------+---------------
+ 04-09-1957 | 1957 | 4 | 9 | 2 | 195 | 20 | 2 | 1957 | 15 | 2 | 2 | 99 | 2435938 | -401760000
+ 06-13-1957 | 1957 | 6 | 13 | 2 | 195 | 20 | 2 | 1957 | 24 | 4 | 4 | 164 | 2436003 | -396144000
+ 02-28-1996 | 1996 | 2 | 28 | 1 | 199 | 20 | 2 | 1996 | 9 | 3 | 3 | 59 | 2450142 | 825465600
+ 02-29-1996 | 1996 | 2 | 29 | 1 | 199 | 20 | 2 | 1996 | 9 | 4 | 4 | 60 | 2450143 | 825552000
+ 03-01-1996 | 1996 | 3 | 1 | 1 | 199 | 20 | 2 | 1996 | 9 | 5 | 5 | 61 | 2450144 | 825638400
+ 03-02-1996 | 1996 | 3 | 2 | 1 | 199 | 20 | 2 | 1996 | 9 | 6 | 6 | 62 | 2450145 | 825724800
+ 02-28-1997 | 1997 | 2 | 28 | 1 | 199 | 20 | 2 | 1997 | 9 | 5 | 5 | 59 | 2450508 | 857088000
+ 03-01-1997 | 1997 | 3 | 1 | 1 | 199 | 20 | 2 | 1997 | 9 | 6 | 6 | 60 | 2450509 | 857174400
+ 03-02-1997 | 1997 | 3 | 2 | 1 | 199 | 20 | 2 | 1997 | 9 | 0 | 7 | 61 | 2450510 | 857260800
+ 04-01-2000 | 2000 | 4 | 1 | 2 | 200 | 20 | 2 | 2000 | 13 | 6 | 6 | 92 | 2451636 | 954547200
+ 04-02-2000 | 2000 | 4 | 2 | 2 | 200 | 20 | 2 | 2000 | 13 | 0 | 7 | 93 | 2451637 | 954633600
+ 04-03-2000 | 2000 | 4 | 3 | 2 | 200 | 20 | 2 | 2000 | 14 | 1 | 1 | 94 | 2451638 | 954720000
+ 04-08-2038 | 2038 | 4 | 8 | 2 | 203 | 21 | 3 | 2038 | 14 | 4 | 4 | 98 | 2465522 | 2154297600
+ 04-09-2039 | 2039 | 4 | 9 | 2 | 203 | 21 | 3 | 2039 | 14 | 6 | 6 | 99 | 2465888 | 2185920000
+ 04-10-2040 | 2040 | 4 | 10 | 2 | 204 | 21 | 3 | 2040 | 15 | 2 | 2 | 101 | 2466255 | 2217628800
+ 04-10-2040 BC | -2040 | 4 | 10 | 2 | -204 | -21 | -3 | -2040 | 15 | 1 | 1 | 100 | 976430 | -126503251200
+(16 rows)
+
+--
-- epoch
--
SELECT EXTRACT(EPOCH FROM DATE '1970-01-01'); -- 0
@@ -1112,6 +1154,132 @@ SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20
(1 row)
--
+-- all possible fields
+--
+SELECT EXTRACT(MICROSECONDS FROM DATE '2020-08-11');
+ date_part
+-----------
+ 0
+(1 row)
+
+SELECT EXTRACT(MILLISECONDS FROM DATE '2020-08-11');
+ date_part
+-----------
+ 0
+(1 row)
+
+SELECT EXTRACT(SECOND FROM DATE '2020-08-11');
+ date_part
+-----------
+ 0
+(1 row)
+
+SELECT EXTRACT(MINUTE FROM DATE '2020-08-11');
+ date_part
+-----------
+ 0
+(1 row)
+
+SELECT EXTRACT(HOUR FROM DATE '2020-08-11');
+ date_part
+-----------
+ 0
+(1 row)
+
+SELECT EXTRACT(DAY FROM DATE '2020-08-11');
+ date_part
+-----------
+ 11
+(1 row)
+
+SELECT EXTRACT(MONTH FROM DATE '2020-08-11');
+ date_part
+-----------
+ 8
+(1 row)
+
+SELECT EXTRACT(YEAR FROM DATE '2020-08-11');
+ date_part
+-----------
+ 2020
+(1 row)
+
+SELECT EXTRACT(DECADE FROM DATE '2020-08-11');
+ date_part
+-----------
+ 202
+(1 row)
+
+SELECT EXTRACT(CENTURY FROM DATE '2020-08-11');
+ date_part
+-----------
+ 21
+(1 row)
+
+SELECT EXTRACT(MILLENNIUM FROM DATE '2020-08-11');
+ date_part
+-----------
+ 3
+(1 row)
+
+SELECT EXTRACT(ISOYEAR FROM DATE '2020-08-11');
+ date_part
+-----------
+ 2020
+(1 row)
+
+SELECT EXTRACT(QUARTER FROM DATE '2020-08-11');
+ date_part
+-----------
+ 3
+(1 row)
+
+SELECT EXTRACT(WEEK FROM DATE '2020-08-11');
+ date_part
+-----------
+ 33
+(1 row)
+
+SELECT EXTRACT(DOW FROM DATE '2020-08-11');
+ date_part
+-----------
+ 2
+(1 row)
+
+SELECT EXTRACT(ISODOW FROM DATE '2020-08-11');
+ date_part
+-----------
+ 2
+(1 row)
+
+SELECT EXTRACT(DOY FROM DATE '2020-08-11');
+ date_part
+-----------
+ 224
+(1 row)
+
+SELECT EXTRACT(TIMEZONE FROM DATE '2020-08-11');
+ERROR: timestamp units "timezone" not supported
+CONTEXT: SQL function "date_part" statement 1
+SELECT EXTRACT(TIMEZONE_M FROM DATE '2020-08-11');
+ERROR: timestamp units "timezone_m" not supported
+CONTEXT: SQL function "date_part" statement 1
+SELECT EXTRACT(TIMEZONE_H FROM DATE '2020-08-11');
+ERROR: timestamp units "timezone_h" not supported
+CONTEXT: SQL function "date_part" statement 1
+SELECT EXTRACT(EPOCH FROM DATE '2020-08-11');
+ date_part
+------------
+ 1597104000
+(1 row)
+
+SELECT EXTRACT(JULIAN FROM DATE '2020-08-11');
+ date_part
+-----------
+ 2459073
+(1 row)
+
+--
-- test trunc function!
--
SELECT DATE_TRUNC('MILLENNIUM', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1001
diff --git a/src/test/regress/expected/expressions.out b/src/test/regress/expected/expressions.out
index 4f4deaec223..05a6eb07b2e 100644
--- a/src/test/regress/expected/expressions.out
+++ b/src/test/regress/expected/expressions.out
@@ -121,7 +121,7 @@ select count(*) from date_tbl
where f1 not between '1997-01-01' and '1998-01-01';
count
-------
- 12
+ 13
(1 row)
explain (costs off)
@@ -155,6 +155,6 @@ select count(*) from date_tbl
where f1 not between symmetric '1997-01-01' and '1998-01-01';
count
-------
- 12
+ 13
(1 row)
diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql
index 1c3adf70ced..488f5faa076 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -20,12 +20,13 @@ INSERT INTO DATE_TBL VALUES ('2000-04-03');
INSERT INTO DATE_TBL VALUES ('2038-04-08');
INSERT INTO DATE_TBL VALUES ('2039-04-09');
INSERT INTO DATE_TBL VALUES ('2040-04-10');
+INSERT INTO DATE_TBL VALUES ('2040-04-10 BC');
-SELECT f1 AS "Fifteen" FROM DATE_TBL;
+SELECT f1 FROM DATE_TBL;
-SELECT f1 AS "Nine" FROM DATE_TBL WHERE f1 < '2000-01-01';
+SELECT f1 FROM DATE_TBL WHERE f1 < '2000-01-01';
-SELECT f1 AS "Three" FROM DATE_TBL
+SELECT f1 FROM DATE_TBL
WHERE f1 BETWEEN '2000-01-01' AND '2001-01-01';
--
@@ -218,6 +219,23 @@ SELECT date 'tomorrow' - date 'yesterday' AS "Two days";
--
-- test extract!
--
+SELECT f1 as "date",
+ date_part('year', f1) AS year,
+ date_part('month', f1) AS month,
+ date_part('day', f1) AS day,
+ date_part('quarter', f1) AS quarter,
+ date_part('decade', f1) AS decade,
+ date_part('century', f1) AS century,
+ date_part('millennium', f1) AS millennium,
+ date_part('isoyear', f1) AS isoyear,
+ date_part('week', f1) AS week,
+ date_part('dow', f1) AS dow,
+ date_part('isodow', f1) AS isodow,
+ date_part('doy', f1) AS doy,
+ date_part('julian', f1) AS julian,
+ date_part('epoch', f1) AS epoch
+ FROM date_tbl;
+--
-- epoch
--
SELECT EXTRACT(EPOCH FROM DATE '1970-01-01'); -- 0
@@ -264,6 +282,31 @@ SELECT EXTRACT(DECADE FROM DATE '0012-12-31 BC'); -- -2
SELECT EXTRACT(CENTURY FROM NOW())>=21 AS True; -- true
SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20
--
+-- all possible fields
+--
+SELECT EXTRACT(MICROSECONDS FROM DATE '2020-08-11');
+SELECT EXTRACT(MILLISECONDS FROM DATE '2020-08-11');
+SELECT EXTRACT(SECOND FROM DATE '2020-08-11');
+SELECT EXTRACT(MINUTE FROM DATE '2020-08-11');
+SELECT EXTRACT(HOUR FROM DATE '2020-08-11');
+SELECT EXTRACT(DAY FROM DATE '2020-08-11');
+SELECT EXTRACT(MONTH FROM DATE '2020-08-11');
+SELECT EXTRACT(YEAR FROM DATE '2020-08-11');
+SELECT EXTRACT(DECADE FROM DATE '2020-08-11');
+SELECT EXTRACT(CENTURY FROM DATE '2020-08-11');
+SELECT EXTRACT(MILLENNIUM FROM DATE '2020-08-11');
+SELECT EXTRACT(ISOYEAR FROM DATE '2020-08-11');
+SELECT EXTRACT(QUARTER FROM DATE '2020-08-11');
+SELECT EXTRACT(WEEK FROM DATE '2020-08-11');
+SELECT EXTRACT(DOW FROM DATE '2020-08-11');
+SELECT EXTRACT(ISODOW FROM DATE '2020-08-11');
+SELECT EXTRACT(DOY FROM DATE '2020-08-11');
+SELECT EXTRACT(TIMEZONE FROM DATE '2020-08-11');
+SELECT EXTRACT(TIMEZONE_M FROM DATE '2020-08-11');
+SELECT EXTRACT(TIMEZONE_H FROM DATE '2020-08-11');
+SELECT EXTRACT(EPOCH FROM DATE '2020-08-11');
+SELECT EXTRACT(JULIAN FROM DATE '2020-08-11');
+--
-- test trunc function!
--
SELECT DATE_TRUNC('MILLENNIUM', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1001
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-23 05:32:16 +0000