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

Commitd69b163

Browse files
committed
Attached is the new patch. To summarize:
 - new function justify_interval(interval) - modified function justify_hours(interval) - modified function justify_days(interval)These functions are defined to meet the requirements as discussed inthis thread. Specifically: - justify_hours makes certain the sign bit on the hours matches the sign bit on the days. It only checks the sign bit on the days, and not the months, when determining if the hours should be positive or negative. After the call, -24 < hours < 24. - justify_days makes certain the sign bit on the days matches the sign bit on the months. It's behavior does not depend on the hours, nor does it modify the hours. After the call, -30 < days < 30. - justify_interval makes sure the sign bits on all three fields months, days, and hours are all the same. After the call, -24 < hours < 24 AND -30 < days < 30.Mark Dilger
1 parent19c21d1 commitd69b163

File tree

7 files changed

+141
-18
lines changed

7 files changed

+141
-18
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 17 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.309 2006/03/0604:53:50 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.310 2006/03/0622:49:15 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -5287,11 +5287,14 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
52875287
<indexterm>
52885288
<primary>isfinite</primary>
52895289
</indexterm>
5290+
<indexterm>
5291+
<primary>justify_days</primary>
5292+
</indexterm>
52905293
<indexterm>
52915294
<primary>justify_hours</primary>
52925295
</indexterm>
52935296
<indexterm>
5294-
<primary>justify_days</primary>
5297+
<primary>justify_interval</primary>
52955298
</indexterm>
52965299
<indexterm>
52975300
<primary>localtime</primary>
@@ -5429,6 +5432,14 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
54295432
<entry><literal>true</literal></entry>
54305433
</row>
54315434

5435+
<row>
5436+
<entry><literal><function>justify_days</function>(<type>interval</type>)</literal></entry>
5437+
<entry><type>interval</type></entry>
5438+
<entry>Adjust interval so 30-day time periods are represented as months</entry>
5439+
<entry><literal>justify_days(interval '30 days')</literal></entry>
5440+
<entry><literal>1 month</literal></entry>
5441+
</row>
5442+
54325443
<row>
54335444
<entry><literal><function>justify_hours</function>(<type>interval</type>)</literal></entry>
54345445
<entry><type>interval</type></entry>
@@ -5438,11 +5449,11 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
54385449
</row>
54395450

54405451
<row>
5441-
<entry><literal><function>justify_days</function>(<type>interval</type>)</literal></entry>
5452+
<entry><literal><function>justify_interval</function>(<type>interval</type>)</literal></entry>
54425453
<entry><type>interval</type></entry>
5443-
<entry>Adjust intervalso 30-day time periods are represented as months</entry>
5444-
<entry><literal>justify_days(interval '30 days')</literal></entry>
5445-
<entry><literal>1 month</literal></entry>
5454+
<entry>Adjust intervalusing <function>justify_days</> and <function>justify_hours</>, with additional sign adjustments</></entry>
5455+
<entry><literal>justify_interval(interval '1 mon -1 hour')</literal></entry>
5456+
<entry><literal>29 days 23:00:00</literal></entry>
54465457
</row>
54475458

54485459
<row>
@@ -5486,13 +5497,6 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
54865497
</tgroup>
54875498
</table>
54885499

5489-
<para>
5490-
If you are using both <function>justify_hours</> and
5491-
<function>justify_days</>, it is best to use <function>justify_hours</>
5492-
first so any additional days will be included in the
5493-
<function>justify_days</> calculation.
5494-
</para>
5495-
54965500
<para>
54975501
In addition to these functions, the SQL <literal>OVERLAPS</> operator is
54985502
supported:

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

Lines changed: 107 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.161 2006/03/05 15:58:44 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.162 2006/03/06 22:49:16 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1974,6 +1974,82 @@ timestamp_mi(PG_FUNCTION_ARGS)
19741974
PG_RETURN_INTERVAL_P(result);
19751975
}
19761976

1977+
/*
1978+
* interval_justify_interval()
1979+
*
1980+
* Adjust interval so 'month', 'day', and 'time' portions are within
1981+
* customary bounds. Specifically:
1982+
*
1983+
* 0 <= abs(time) < 24 hours
1984+
* 0 <= abs(day) < 30 days
1985+
*
1986+
* Also, the sign bit on all three fields is made equal, so either
1987+
* all three fields are negative or all are positive.
1988+
*/
1989+
Datum
1990+
interval_justify_interval(PG_FUNCTION_ARGS)
1991+
{
1992+
Interval*span=PG_GETARG_INTERVAL_P(0);
1993+
Interval*result;
1994+
1995+
#ifdefHAVE_INT64_TIMESTAMP
1996+
int64wholeday;
1997+
#else
1998+
doublewholeday;
1999+
#endif
2000+
int32wholemonth;
2001+
2002+
result= (Interval*)palloc(sizeof(Interval));
2003+
result->month=span->month;
2004+
result->day=span->day;
2005+
result->time=span->time;
2006+
2007+
#ifdefHAVE_INT64_TIMESTAMP
2008+
TMODULO(result->time,wholeday,USECS_PER_DAY);
2009+
#else
2010+
TMODULO(result->time,wholeday, (double)SECS_PER_DAY);
2011+
#endif
2012+
result->day+=wholeday;/* could overflow... */
2013+
2014+
wholemonth=result->day /DAYS_PER_MONTH;
2015+
result->day-=wholemonth*DAYS_PER_MONTH;
2016+
result->month+=wholemonth;
2017+
2018+
if (result->month>0&&
2019+
(result->day<0|| (result->day==0&&result->time<0)))
2020+
{
2021+
result->day+=DAYS_PER_MONTH;
2022+
result->month--;
2023+
}
2024+
elseif (result->month<0&&
2025+
(result->day>0|| (result->day==0&&result->time>0)))
2026+
{
2027+
result->day-=DAYS_PER_MONTH;
2028+
result->month++;
2029+
}
2030+
2031+
if (result->day>0&&result->time<0)
2032+
{
2033+
#ifdefHAVE_INT64_TIMESTAMP
2034+
result->time+=USECS_PER_DAY;
2035+
#else
2036+
result->time+= (double)SECS_PER_DAY;
2037+
#endif
2038+
result->day--;
2039+
}
2040+
elseif (result->day<0&&result->time>0)
2041+
{
2042+
#ifdefHAVE_INT64_TIMESTAMP
2043+
result->time-=USECS_PER_DAY;
2044+
#else
2045+
result->time-= (double)SECS_PER_DAY;
2046+
#endif
2047+
result->day++;
2048+
}
2049+
2050+
PG_RETURN_INTERVAL_P(result);
2051+
}
2052+
19772053
/*
19782054
*interval_justify_hours()
19792055
*
@@ -2006,6 +2082,25 @@ interval_justify_hours(PG_FUNCTION_ARGS)
20062082
#endif
20072083
result->day+=wholeday;/* could overflow... */
20082084

2085+
if (result->day>0&&result->time<0)
2086+
{
2087+
#ifdefHAVE_INT64_TIMESTAMP
2088+
result->time+=USECS_PER_DAY;
2089+
#else
2090+
result->time+= (double)SECS_PER_DAY;
2091+
#endif
2092+
result->day--;
2093+
}
2094+
elseif (result->day<0&&result->time>0)
2095+
{
2096+
#ifdefHAVE_INT64_TIMESTAMP
2097+
result->time-=USECS_PER_DAY;
2098+
#else
2099+
result->time-= (double)SECS_PER_DAY;
2100+
#endif
2101+
result->day++;
2102+
}
2103+
20092104
PG_RETURN_INTERVAL_P(result);
20102105
}
20112106

@@ -2031,6 +2126,17 @@ interval_justify_days(PG_FUNCTION_ARGS)
20312126
result->day-=wholemonth*DAYS_PER_MONTH;
20322127
result->month+=wholemonth;
20332128

2129+
if (result->month>0&&result->day<0)
2130+
{
2131+
result->day+=DAYS_PER_MONTH;
2132+
result->month--;
2133+
}
2134+
elseif (result->month<0&&result->day>0)
2135+
{
2136+
result->day-=DAYS_PER_MONTH;
2137+
result->month++;
2138+
}
2139+
20342140
PG_RETURN_INTERVAL_P(result);
20352141
}
20362142

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.318 2006/03/05 15:58:54 momjian Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.319 2006/03/06 22:49:16 momjian Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200602281
56+
#defineCATALOG_VERSION_NO200603061
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.400 2006/03/05 15:58:54 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.401 2006/03/06 22:49:16 momjian Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -1462,6 +1462,8 @@ DATA(insert OID = 1173 ( timestamptz PGNSP PGUID 12 f f t f i 1 1184 "702" _
14621462
DESCR("convert abstime to timestamp with time zone");
14631463
DATA(insertOID=1174 (timestamptzPGNSPPGUID12fftfs11184"1082"_null__null__null_date_timestamptz-_null_ ));
14641464
DESCR("convert date to timestamp with time zone");
1465+
DATA(insertOID=2711 (justify_intervalPGNSPPGUID12fftfi11186"1186"_null__null__null_interval_justify_interval-_null_ ));
1466+
DESCR("promote groups of 24 hours to numbers of days and promote groups of 30 days to numbers of months");
14651467
DATA(insertOID=1175 (justify_hoursPGNSPPGUID12fftfi11186"1186"_null__null__null_interval_justify_hours-_null_ ));
14661468
DESCR("promote groups of 24 hours to numbers of days");
14671469
DATA(insertOID=1295 (justify_daysPGNSPPGUID12fftfi11186"1186"_null__null__null_interval_justify_days-_null_ ));

‎src/include/utils/timestamp.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $PostgreSQL: pgsql/src/include/utils/timestamp.h,v 1.58 2006/03/05 15:59:08 momjian Exp $
9+
* $PostgreSQL: pgsql/src/include/utils/timestamp.h,v 1.59 2006/03/06 22:49:17 momjian Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -234,6 +234,7 @@ extern Datum interval_cmp(PG_FUNCTION_ARGS);
234234
externDatuminterval_hash(PG_FUNCTION_ARGS);
235235
externDatuminterval_smaller(PG_FUNCTION_ARGS);
236236
externDatuminterval_larger(PG_FUNCTION_ARGS);
237+
externDatuminterval_justify_interval(PG_FUNCTION_ARGS);
237238
externDatuminterval_justify_hours(PG_FUNCTION_ARGS);
238239
externDatuminterval_justify_days(PG_FUNCTION_ARGS);
239240

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -241,3 +241,10 @@ SELECT justify_days(interval '6 months 36 days 5 hours 4 minutes 3 seconds') as
241241
@ 7 mons 6 days 5 hours 4 mins 3 secs
242242
(1 row)
243243

244+
-- test justify_interval()
245+
SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
246+
1 month -1 hour
247+
--------------------
248+
@ 29 days 23 hours
249+
(1 row)
250+

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -76,3 +76,6 @@ select '4 millenniums 5 centuries 4 decades 1 year 4 months 4 days 17 minutes 31
7676
SELECT justify_hours(interval'6 months 3 days 52 hours 3 minutes 2 seconds')as"6 mons 5 days 4 hours 3 mins 2 seconds";
7777
SELECT justify_days(interval'6 months 36 days 5 hours 4 minutes 3 seconds')as"7 mons 6 days 5 hours 4 mins 3 seconds";
7878

79+
-- test justify_interval()
80+
81+
SELECT justify_interval(interval'1 month -1 hour')as"1 month -1 hour";

0 commit comments

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