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

Commit540612f

Browse files
committed
Add more tests for EXTRACT of date type
EXTRACT of date type is implemented as a wrapper around EXTRACT oftimestamp, so the code is already tested there. But the externallyvisible behavior of EXTRACT on date is not recorded anywhere. Sincethere is some discussion about reimplementing or refactoring some ofthis, add some more explicit tests of EXTRACT on date, similar instructure to existing EXTRACT tests on other data types.Discussion:https://www.postgresql.org/message-id/flat/42b73d2d-da12-ba9f-570a-420e0cce19d9@phystech.edu
1 parent994a584 commit540612f

File tree

3 files changed

+228
-17
lines changed

3 files changed

+228
-17
lines changed

‎src/test/regress/expected/date.out

Lines changed: 180 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -21,9 +21,10 @@ INSERT INTO DATE_TBL VALUES ('2000-04-03');
2121
INSERT INTO DATE_TBL VALUES ('2038-04-08');
2222
INSERT INTO DATE_TBL VALUES ('2039-04-09');
2323
INSERT INTO DATE_TBL VALUES ('2040-04-10');
24-
SELECT f1 AS "Fifteen" FROM DATE_TBL;
25-
Fifteen
26-
------------
24+
INSERT INTO DATE_TBL VALUES ('2040-04-10 BC');
25+
SELECT f1 FROM DATE_TBL;
26+
f1
27+
---------------
2728
04-09-1957
2829
06-13-1957
2930
02-28-1996
@@ -39,11 +40,12 @@ SELECT f1 AS "Fifteen" FROM DATE_TBL;
3940
04-08-2038
4041
04-09-2039
4142
04-10-2040
42-
(15 rows)
43+
04-10-2040 BC
44+
(16 rows)
4345

44-
SELECT f1AS "Nine"FROM DATE_TBL WHERE f1 < '2000-01-01';
45-
Nine
46-
------------
46+
SELECT f1 FROM DATE_TBL WHERE f1 < '2000-01-01';
47+
f1
48+
---------------
4749
04-09-1957
4850
06-13-1957
4951
02-28-1996
@@ -53,11 +55,12 @@ SELECT f1 AS "Nine" FROM DATE_TBL WHERE f1 < '2000-01-01';
5355
02-28-1997
5456
03-01-1997
5557
03-02-1997
56-
(9 rows)
58+
04-10-2040 BC
59+
(10 rows)
5760

58-
SELECT f1AS "Three"FROM DATE_TBL
61+
SELECT f1 FROM DATE_TBL
5962
WHERE f1 BETWEEN '2000-01-01' AND '2001-01-01';
60-
Three
63+
f1
6164
------------
6265
04-01-2000
6366
04-02-2000
@@ -860,7 +863,8 @@ SELECT f1 - date '2000-01-01' AS "Days From 2K" FROM DATE_TBL;
860863
13977
861864
14343
862865
14710
863-
(15 rows)
866+
-1475115
867+
(16 rows)
864868

865869
SELECT f1 - date 'epoch' AS "Days From Epoch" FROM DATE_TBL;
866870
Days From Epoch
@@ -880,7 +884,8 @@ SELECT f1 - date 'epoch' AS "Days From Epoch" FROM DATE_TBL;
880884
24934
881885
25300
882886
25667
883-
(15 rows)
887+
-1464158
888+
(16 rows)
884889

885890
SELECT date 'yesterday' - date 'today' AS "One day";
886891
One day
@@ -920,6 +925,43 @@ SELECT date 'tomorrow' - date 'yesterday' AS "Two days";
920925

921926
--
922927
-- test extract!
928+
--
929+
SELECT f1 as "date",
930+
date_part('year', f1) AS year,
931+
date_part('month', f1) AS month,
932+
date_part('day', f1) AS day,
933+
date_part('quarter', f1) AS quarter,
934+
date_part('decade', f1) AS decade,
935+
date_part('century', f1) AS century,
936+
date_part('millennium', f1) AS millennium,
937+
date_part('isoyear', f1) AS isoyear,
938+
date_part('week', f1) AS week,
939+
date_part('dow', f1) AS dow,
940+
date_part('isodow', f1) AS isodow,
941+
date_part('doy', f1) AS doy,
942+
date_part('julian', f1) AS julian,
943+
date_part('epoch', f1) AS epoch
944+
FROM date_tbl;
945+
date | year | month | day | quarter | decade | century | millennium | isoyear | week | dow | isodow | doy | julian | epoch
946+
---------------+-------+-------+-----+---------+--------+---------+------------+---------+------+-----+--------+-----+---------+---------------
947+
04-09-1957 | 1957 | 4 | 9 | 2 | 195 | 20 | 2 | 1957 | 15 | 2 | 2 | 99 | 2435938 | -401760000
948+
06-13-1957 | 1957 | 6 | 13 | 2 | 195 | 20 | 2 | 1957 | 24 | 4 | 4 | 164 | 2436003 | -396144000
949+
02-28-1996 | 1996 | 2 | 28 | 1 | 199 | 20 | 2 | 1996 | 9 | 3 | 3 | 59 | 2450142 | 825465600
950+
02-29-1996 | 1996 | 2 | 29 | 1 | 199 | 20 | 2 | 1996 | 9 | 4 | 4 | 60 | 2450143 | 825552000
951+
03-01-1996 | 1996 | 3 | 1 | 1 | 199 | 20 | 2 | 1996 | 9 | 5 | 5 | 61 | 2450144 | 825638400
952+
03-02-1996 | 1996 | 3 | 2 | 1 | 199 | 20 | 2 | 1996 | 9 | 6 | 6 | 62 | 2450145 | 825724800
953+
02-28-1997 | 1997 | 2 | 28 | 1 | 199 | 20 | 2 | 1997 | 9 | 5 | 5 | 59 | 2450508 | 857088000
954+
03-01-1997 | 1997 | 3 | 1 | 1 | 199 | 20 | 2 | 1997 | 9 | 6 | 6 | 60 | 2450509 | 857174400
955+
03-02-1997 | 1997 | 3 | 2 | 1 | 199 | 20 | 2 | 1997 | 9 | 0 | 7 | 61 | 2450510 | 857260800
956+
04-01-2000 | 2000 | 4 | 1 | 2 | 200 | 20 | 2 | 2000 | 13 | 6 | 6 | 92 | 2451636 | 954547200
957+
04-02-2000 | 2000 | 4 | 2 | 2 | 200 | 20 | 2 | 2000 | 13 | 0 | 7 | 93 | 2451637 | 954633600
958+
04-03-2000 | 2000 | 4 | 3 | 2 | 200 | 20 | 2 | 2000 | 14 | 1 | 1 | 94 | 2451638 | 954720000
959+
04-08-2038 | 2038 | 4 | 8 | 2 | 203 | 21 | 3 | 2038 | 14 | 4 | 4 | 98 | 2465522 | 2154297600
960+
04-09-2039 | 2039 | 4 | 9 | 2 | 203 | 21 | 3 | 2039 | 14 | 6 | 6 | 99 | 2465888 | 2185920000
961+
04-10-2040 | 2040 | 4 | 10 | 2 | 204 | 21 | 3 | 2040 | 15 | 2 | 2 | 101 | 2466255 | 2217628800
962+
04-10-2040 BC | -2040 | 4 | 10 | 2 | -204 | -21 | -3 | -2040 | 15 | 1 | 1 | 100 | 976430 | -126503251200
963+
(16 rows)
964+
923965
--
924966
-- epoch
925967
--
@@ -1111,6 +1153,132 @@ SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20
11111153
20
11121154
(1 row)
11131155

1156+
--
1157+
-- all possible fields
1158+
--
1159+
SELECT EXTRACT(MICROSECONDS FROM DATE '2020-08-11');
1160+
date_part
1161+
-----------
1162+
0
1163+
(1 row)
1164+
1165+
SELECT EXTRACT(MILLISECONDS FROM DATE '2020-08-11');
1166+
date_part
1167+
-----------
1168+
0
1169+
(1 row)
1170+
1171+
SELECT EXTRACT(SECOND FROM DATE '2020-08-11');
1172+
date_part
1173+
-----------
1174+
0
1175+
(1 row)
1176+
1177+
SELECT EXTRACT(MINUTE FROM DATE '2020-08-11');
1178+
date_part
1179+
-----------
1180+
0
1181+
(1 row)
1182+
1183+
SELECT EXTRACT(HOUR FROM DATE '2020-08-11');
1184+
date_part
1185+
-----------
1186+
0
1187+
(1 row)
1188+
1189+
SELECT EXTRACT(DAY FROM DATE '2020-08-11');
1190+
date_part
1191+
-----------
1192+
11
1193+
(1 row)
1194+
1195+
SELECT EXTRACT(MONTH FROM DATE '2020-08-11');
1196+
date_part
1197+
-----------
1198+
8
1199+
(1 row)
1200+
1201+
SELECT EXTRACT(YEAR FROM DATE '2020-08-11');
1202+
date_part
1203+
-----------
1204+
2020
1205+
(1 row)
1206+
1207+
SELECT EXTRACT(DECADE FROM DATE '2020-08-11');
1208+
date_part
1209+
-----------
1210+
202
1211+
(1 row)
1212+
1213+
SELECT EXTRACT(CENTURY FROM DATE '2020-08-11');
1214+
date_part
1215+
-----------
1216+
21
1217+
(1 row)
1218+
1219+
SELECT EXTRACT(MILLENNIUM FROM DATE '2020-08-11');
1220+
date_part
1221+
-----------
1222+
3
1223+
(1 row)
1224+
1225+
SELECT EXTRACT(ISOYEAR FROM DATE '2020-08-11');
1226+
date_part
1227+
-----------
1228+
2020
1229+
(1 row)
1230+
1231+
SELECT EXTRACT(QUARTER FROM DATE '2020-08-11');
1232+
date_part
1233+
-----------
1234+
3
1235+
(1 row)
1236+
1237+
SELECT EXTRACT(WEEK FROM DATE '2020-08-11');
1238+
date_part
1239+
-----------
1240+
33
1241+
(1 row)
1242+
1243+
SELECT EXTRACT(DOW FROM DATE '2020-08-11');
1244+
date_part
1245+
-----------
1246+
2
1247+
(1 row)
1248+
1249+
SELECT EXTRACT(ISODOW FROM DATE '2020-08-11');
1250+
date_part
1251+
-----------
1252+
2
1253+
(1 row)
1254+
1255+
SELECT EXTRACT(DOY FROM DATE '2020-08-11');
1256+
date_part
1257+
-----------
1258+
224
1259+
(1 row)
1260+
1261+
SELECT EXTRACT(TIMEZONE FROM DATE '2020-08-11');
1262+
ERROR: timestamp units "timezone" not supported
1263+
CONTEXT: SQL function "date_part" statement 1
1264+
SELECT EXTRACT(TIMEZONE_M FROM DATE '2020-08-11');
1265+
ERROR: timestamp units "timezone_m" not supported
1266+
CONTEXT: SQL function "date_part" statement 1
1267+
SELECT EXTRACT(TIMEZONE_H FROM DATE '2020-08-11');
1268+
ERROR: timestamp units "timezone_h" not supported
1269+
CONTEXT: SQL function "date_part" statement 1
1270+
SELECT EXTRACT(EPOCH FROM DATE '2020-08-11');
1271+
date_part
1272+
------------
1273+
1597104000
1274+
(1 row)
1275+
1276+
SELECT EXTRACT(JULIAN FROM DATE '2020-08-11');
1277+
date_part
1278+
-----------
1279+
2459073
1280+
(1 row)
1281+
11141282
--
11151283
-- test trunc function!
11161284
--

‎src/test/regress/expected/expressions.out

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -121,7 +121,7 @@ select count(*) from date_tbl
121121
where f1 not between '1997-01-01' and '1998-01-01';
122122
count
123123
-------
124-
12
124+
13
125125
(1 row)
126126

127127
explain (costs off)
@@ -155,6 +155,6 @@ select count(*) from date_tbl
155155
where f1 not between symmetric '1997-01-01' and '1998-01-01';
156156
count
157157
-------
158-
12
158+
13
159159
(1 row)
160160

‎src/test/regress/sql/date.sql

Lines changed: 46 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -20,12 +20,13 @@ INSERT INTO DATE_TBL VALUES ('2000-04-03');
2020
INSERT INTO DATE_TBLVALUES ('2038-04-08');
2121
INSERT INTO DATE_TBLVALUES ('2039-04-09');
2222
INSERT INTO DATE_TBLVALUES ('2040-04-10');
23+
INSERT INTO DATE_TBLVALUES ('2040-04-10 BC');
2324

24-
SELECT f1AS"Fifteen"FROM DATE_TBL;
25+
SELECT f1FROM DATE_TBL;
2526

26-
SELECT f1AS"Nine"FROM DATE_TBLWHERE f1<'2000-01-01';
27+
SELECT f1FROM DATE_TBLWHERE f1<'2000-01-01';
2728

28-
SELECT f1AS"Three"FROM DATE_TBL
29+
SELECT f1FROM DATE_TBL
2930
WHERE f1 BETWEEN'2000-01-01'AND'2001-01-01';
3031

3132
--
@@ -218,6 +219,23 @@ SELECT date 'tomorrow' - date 'yesterday' AS "Two days";
218219
--
219220
-- test extract!
220221
--
222+
SELECT f1as"date",
223+
date_part('year', f1)AS year,
224+
date_part('month', f1)AS month,
225+
date_part('day', f1)AS day,
226+
date_part('quarter', f1)AS quarter,
227+
date_part('decade', f1)AS decade,
228+
date_part('century', f1)AS century,
229+
date_part('millennium', f1)AS millennium,
230+
date_part('isoyear', f1)AS isoyear,
231+
date_part('week', f1)AS week,
232+
date_part('dow', f1)AS dow,
233+
date_part('isodow', f1)AS isodow,
234+
date_part('doy', f1)AS doy,
235+
date_part('julian', f1)AS julian,
236+
date_part('epoch', f1)AS epoch
237+
FROM date_tbl;
238+
--
221239
-- epoch
222240
--
223241
SELECT EXTRACT(EPOCHFROMDATE'1970-01-01');-- 0
@@ -264,6 +282,31 @@ SELECT EXTRACT(DECADE FROM DATE '0012-12-31 BC'); -- -2
264282
SELECT EXTRACT(CENTURYFROM NOW())>=21AS True;-- true
265283
SELECT EXTRACT(CENTURYFROMTIMESTAMP'1970-03-20 04:30:00.00000');-- 20
266284
--
285+
-- all possible fields
286+
--
287+
SELECT EXTRACT(MICROSECONDSFROMDATE'2020-08-11');
288+
SELECT EXTRACT(MILLISECONDSFROMDATE'2020-08-11');
289+
SELECT EXTRACT(SECONDFROMDATE'2020-08-11');
290+
SELECT EXTRACT(MINUTEFROMDATE'2020-08-11');
291+
SELECT EXTRACT(HOURFROMDATE'2020-08-11');
292+
SELECT EXTRACT(DAYFROMDATE'2020-08-11');
293+
SELECT EXTRACT(MONTHFROMDATE'2020-08-11');
294+
SELECT EXTRACT(YEARFROMDATE'2020-08-11');
295+
SELECT EXTRACT(DECADEFROMDATE'2020-08-11');
296+
SELECT EXTRACT(CENTURYFROMDATE'2020-08-11');
297+
SELECT EXTRACT(MILLENNIUMFROMDATE'2020-08-11');
298+
SELECT EXTRACT(ISOYEARFROMDATE'2020-08-11');
299+
SELECT EXTRACT(QUARTERFROMDATE'2020-08-11');
300+
SELECT EXTRACT(WEEKFROMDATE'2020-08-11');
301+
SELECT EXTRACT(DOWFROMDATE'2020-08-11');
302+
SELECT EXTRACT(ISODOWFROMDATE'2020-08-11');
303+
SELECT EXTRACT(DOYFROMDATE'2020-08-11');
304+
SELECT EXTRACT(TIMEZONEFROMDATE'2020-08-11');
305+
SELECT EXTRACT(TIMEZONE_MFROMDATE'2020-08-11');
306+
SELECT EXTRACT(TIMEZONE_HFROMDATE'2020-08-11');
307+
SELECT EXTRACT(EPOCHFROMDATE'2020-08-11');
308+
SELECT EXTRACT(JULIANFROMDATE'2020-08-11');
309+
--
267310
-- test trunc function!
268311
--
269312
SELECT DATE_TRUNC('MILLENNIUM',TIMESTAMP'1970-03-20 04:30:00.00000');-- 1001

0 commit comments

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