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

Commit6be39d7

Browse files
committed
Fix extraction of week and quarter fields from intervals.
"EXTRACT(WEEK FROM interval_value)" formerly threw an error.Define it as "tm->tm_mday / 7". (With C99 division semantics,this gives consistent results for negative intervals.)"EXTRACT(QUARTER FROM interval_value)" has been implementedall along, but it formerly gave extremely strange results fornegative intervals. Fix it so that the output for -N monthsis the negative of the output for N months.Per bug #18348 from Michael Bondarenko and subsequent discussion.Discussion:https://postgr.es/m/18348-b097a3587dfde8a4@postgresql.org
1 parent108d2ad commit6be39d7

File tree

4 files changed

+94
-16
lines changed

4 files changed

+94
-16
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10432,12 +10432,16 @@ SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
1043210432
<term><literal>quarter</literal></term>
1043310433
<listitem>
1043410434
<para>
10435-
The quarter of the year (1&ndash;4) that the date is in
10435+
The quarter of the year (1&ndash;4) that the date is in;
10436+
for <type>interval</type> values, the month field divided by 3
10437+
plus 1
1043610438
</para>
1043710439

1043810440
<screen>
1043910441
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
1044010442
<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
10443+
SELECT EXTRACT(QUARTER FROM INTERVAL '1 year 6 months');
10444+
<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
1044110445
</screen>
1044210446
</listitem>
1044310447
</varlistentry>
@@ -10508,9 +10512,16 @@ SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
1050810512
<literal>week</literal> to get consistent results.
1050910513
</para>
1051010514

10515+
<para>
10516+
For <type>interval</type> values, the week field is simply the number
10517+
of integral days divided by 7.
10518+
</para>
10519+
1051110520
<screen>
1051210521
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
1051310522
<lineannotation>Result: </lineannotation><computeroutput>7</computeroutput>
10523+
SELECT EXTRACT(WEEK FROM INTERVAL '13 days 24 hours');
10524+
<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
1051410525
</screen>
1051510526
</listitem>
1051610527
</varlistentry>

‎src/backend/utils/adt/timestamp.c‎

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5899,6 +5899,7 @@ NonFiniteIntervalPart(int type, int unit, char *lowunits, bool isNegative)
58995899
caseDTK_MILLISEC:
59005900
caseDTK_SECOND:
59015901
caseDTK_MINUTE:
5902+
caseDTK_WEEK:
59025903
caseDTK_MONTH:
59035904
caseDTK_QUARTER:
59045905
return0.0;
@@ -6018,12 +6019,27 @@ interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
60186019
intresult=tm->tm_mday;
60196020
break;
60206021

6022+
caseDTK_WEEK:
6023+
intresult=tm->tm_mday /7;
6024+
break;
6025+
60216026
caseDTK_MONTH:
60226027
intresult=tm->tm_mon;
60236028
break;
60246029

60256030
caseDTK_QUARTER:
6026-
intresult= (tm->tm_mon /3)+1;
6031+
6032+
/*
6033+
* We want to maintain the rule that a field extracted from a
6034+
* negative interval is the negative of the field's value for
6035+
* the sign-reversed interval. The broken-down tm_year and
6036+
* tm_mon aren't very helpful for that, so work from
6037+
* interval->month.
6038+
*/
6039+
if (interval->month >=0)
6040+
intresult= (tm->tm_mon /3)+1;
6041+
else
6042+
intresult=-(((-interval->month %MONTHS_PER_YEAR) /3)+1);
60276043
break;
60286044

60296045
caseDTK_YEAR:

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

Lines changed: 47 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1834,6 +1834,7 @@ SELECT f1,
18341834
EXTRACT(MINUTE FROM f1) AS MINUTE,
18351835
EXTRACT(HOUR FROM f1) AS HOUR,
18361836
EXTRACT(DAY FROM f1) AS DAY,
1837+
EXTRACT(WEEK FROM f1) AS WEEK,
18371838
EXTRACT(MONTH FROM f1) AS MONTH,
18381839
EXTRACT(QUARTER FROM f1) AS QUARTER,
18391840
EXTRACT(YEAR FROM f1) AS YEAR,
@@ -1842,20 +1843,52 @@ SELECT f1,
18421843
EXTRACT(MILLENNIUM FROM f1) AS MILLENNIUM,
18431844
EXTRACT(EPOCH FROM f1) AS EPOCH
18441845
FROM INTERVAL_TBL;
1845-
f1 | microsecond | millisecond | second | minute | hour | day | month | quarter | year | decade | century | millennium | epoch
1846-
-------------------------------+-------------+-------------+------------+--------+-----------+-----------+-------+---------+-----------+-----------+-----------+------------+-------------------
1847-
@ 1 min | 0 | 0.000 | 0.000000 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 60.000000
1848-
@ 5 hours | 0 | 0.000 | 0.000000 | 0 | 5 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 18000.000000
1849-
@ 10 days | 0 | 0.000 | 0.000000 | 0 | 0 | 10 | 0 | 1 | 0 | 0 | 0 | 0 | 864000.000000
1850-
@ 34 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 34 | 3 | 0 | 0 | 1072958400.000000
1851-
@ 3 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 3 | 2 | 0 | 0 | 0 | 0 | 7776000.000000
1852-
@ 14 secs ago | -14000000 | -14000.000 | -14.000000 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -14.000000
1853-
@ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000.000 | 4.000000 | 3 | 2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 93784.000000
1854-
@ 6 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 6 | 0 | 0 | 0 | 189345600.000000
1855-
@ 5 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 12960000.000000
1856-
@ 5 mons 12 hours | 0 | 0.000 | 0.000000 | 0 | 12 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 13003200.000000
1857-
infinity | | | | | Infinity | Infinity | | | Infinity | Infinity | Infinity | Infinity | Infinity
1858-
-infinity | | | | | -Infinity | -Infinity | | | -Infinity | -Infinity | -Infinity | -Infinity | -Infinity
1846+
f1 | microsecond | millisecond | second | minute | hour | day | week | month | quarter | year | decade | century | millennium | epoch
1847+
-------------------------------+-------------+-------------+------------+--------+-----------+-----------+------+-------+---------+-----------+-----------+-----------+------------+-------------------
1848+
@ 1 min | 0 | 0.000 | 0.000000 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 60.000000
1849+
@ 5 hours | 0 | 0.000 | 0.000000 | 0 | 5 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 18000.000000
1850+
@ 10 days | 0 | 0.000 | 0.000000 | 0 | 0 | 10 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 864000.000000
1851+
@ 34 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 1 | 34 | 3 | 0 | 0 | 1072958400.000000
1852+
@ 3 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 3 | 2 | 0 | 0 | 0 | 0 | 7776000.000000
1853+
@ 14 secs ago | -14000000 | -14000.000 | -14.000000 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -14.000000
1854+
@ 1 day 2 hours 3 mins 4 secs | 4000000 | 4000.000 | 4.000000 | 3 | 2 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 93784.000000
1855+
@ 6 years | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 1 | 6 | 0 | 0 | 0 | 189345600.000000
1856+
@ 5 mons | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 12960000.000000
1857+
@ 5 mons 12 hours | 0 | 0.000 | 0.000000 | 0 | 12 | 0 | 0 | 5 | 2 | 0 | 0 | 0 | 0 | 13003200.000000
1858+
infinity | | | | | Infinity | Infinity | | | | Infinity | Infinity | Infinity | Infinity | Infinity
1859+
-infinity | | | | | -Infinity | -Infinity | | | | -Infinity | -Infinity | -Infinity | -Infinity | -Infinity
1860+
(12 rows)
1861+
1862+
SELECT -f1,
1863+
EXTRACT(MICROSECOND FROM -f1) AS MICROSECOND,
1864+
EXTRACT(MILLISECOND FROM -f1) AS MILLISECOND,
1865+
EXTRACT(SECOND FROM -f1) AS SECOND,
1866+
EXTRACT(MINUTE FROM -f1) AS MINUTE,
1867+
EXTRACT(HOUR FROM -f1) AS HOUR,
1868+
EXTRACT(DAY FROM -f1) AS DAY,
1869+
EXTRACT(WEEK FROM -f1) AS WEEK,
1870+
EXTRACT(MONTH FROM -f1) AS MONTH,
1871+
EXTRACT(QUARTER FROM -f1) AS QUARTER,
1872+
EXTRACT(YEAR FROM -f1) AS YEAR,
1873+
EXTRACT(DECADE FROM -f1) AS DECADE,
1874+
EXTRACT(CENTURY FROM -f1) AS CENTURY,
1875+
EXTRACT(MILLENNIUM FROM -f1) AS MILLENNIUM,
1876+
EXTRACT(EPOCH FROM -f1) AS EPOCH
1877+
FROM INTERVAL_TBL;
1878+
?column? | microsecond | millisecond | second | minute | hour | day | week | month | quarter | year | decade | century | millennium | epoch
1879+
-----------------------------------+-------------+-------------+-----------+--------+-----------+-----------+------+-------+---------+-----------+-----------+-----------+------------+--------------------
1880+
@ 1 min ago | 0 | 0.000 | 0.000000 | -1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -60.000000
1881+
@ 5 hours ago | 0 | 0.000 | 0.000000 | 0 | -5 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -18000.000000
1882+
@ 10 days ago | 0 | 0.000 | 0.000000 | 0 | 0 | -10 | -1 | 0 | 1 | 0 | 0 | 0 | 0 | -864000.000000
1883+
@ 34 years ago | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | -1 | -34 | -3 | 0 | 0 | -1072958400.000000
1884+
@ 3 mons ago | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | -3 | -2 | 0 | 0 | 0 | 0 | -7776000.000000
1885+
@ 14 secs | 14000000 | 14000.000 | 14.000000 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 14.000000
1886+
@ 1 day 2 hours 3 mins 4 secs ago | -4000000 | -4000.000 | -4.000000 | -3 | -2 | -1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | -93784.000000
1887+
@ 6 years ago | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | -1 | -6 | 0 | 0 | 0 | -189345600.000000
1888+
@ 5 mons ago | 0 | 0.000 | 0.000000 | 0 | 0 | 0 | 0 | -5 | -2 | 0 | 0 | 0 | 0 | -12960000.000000
1889+
@ 5 mons 12 hours ago | 0 | 0.000 | 0.000000 | 0 | -12 | 0 | 0 | -5 | -2 | 0 | 0 | 0 | 0 | -13003200.000000
1890+
-infinity | | | | | -Infinity | -Infinity | | | | -Infinity | -Infinity | -Infinity | -Infinity | -Infinity
1891+
infinity | | | | | Infinity | Infinity | | | | Infinity | Infinity | Infinity | Infinity | Infinity
18591892
(12 rows)
18601893

18611894
SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days'); -- error

‎src/test/regress/sql/interval.sql‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -592,6 +592,7 @@ SELECT f1,
592592
EXTRACT(MINUTEFROM f1)AS MINUTE,
593593
EXTRACT(HOURFROM f1)AS HOUR,
594594
EXTRACT(DAYFROM f1)AS DAY,
595+
EXTRACT(WEEKFROM f1)AS WEEK,
595596
EXTRACT(MONTHFROM f1)AS MONTH,
596597
EXTRACT(QUARTERFROM f1)AS QUARTER,
597598
EXTRACT(YEARFROM f1)AS YEAR,
@@ -601,6 +602,23 @@ SELECT f1,
601602
EXTRACT(EPOCHFROM f1)AS EPOCH
602603
FROM INTERVAL_TBL;
603604

605+
SELECT-f1,
606+
EXTRACT(MICROSECONDFROM-f1)AS MICROSECOND,
607+
EXTRACT(MILLISECONDFROM-f1)AS MILLISECOND,
608+
EXTRACT(SECONDFROM-f1)AS SECOND,
609+
EXTRACT(MINUTEFROM-f1)AS MINUTE,
610+
EXTRACT(HOURFROM-f1)AS HOUR,
611+
EXTRACT(DAYFROM-f1)AS DAY,
612+
EXTRACT(WEEKFROM-f1)AS WEEK,
613+
EXTRACT(MONTHFROM-f1)AS MONTH,
614+
EXTRACT(QUARTERFROM-f1)AS QUARTER,
615+
EXTRACT(YEARFROM-f1)AS YEAR,
616+
EXTRACT(DECADEFROM-f1)AS DECADE,
617+
EXTRACT(CENTURYFROM-f1)AS CENTURY,
618+
EXTRACT(MILLENNIUMFROM-f1)AS MILLENNIUM,
619+
EXTRACT(EPOCHFROM-f1)AS EPOCH
620+
FROM INTERVAL_TBL;
621+
604622
SELECT EXTRACT(FORTNIGHTFROM INTERVAL'2 days');-- error
605623
SELECT EXTRACT(TIMEZONEFROM INTERVAL'2 days');-- error
606624

0 commit comments

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