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

Commitb2b6548

Browse files
committed
Please find attached diffs for documentation and simple regression
tests for the new interval->day changes. I added tests forjustify_hours() and justify_days() to interval.sql, as they takeinterval input and produce interval output. If there's a moreappropriate place for them, please let me know.Michael Glaesemann
1 parent5b0bfec commitb2b6548

File tree

5 files changed

+75
-1
lines changed

5 files changed

+75
-1
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.273 2005/07/29 14:46:56 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.274 2005/07/30 14:52:04 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -4903,6 +4903,24 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
49034903
such pair.
49044904
</para>
49054905

4906+
<para>
4907+
When adding an <type>interval</type> value to (or subtracting an
4908+
<type>interval</type> value from) a <type>timestamp with time zone</type>
4909+
value, the days component advances (or decrements) the date of the
4910+
<type>timestamp with time zone<type> by the indicated number of days.
4911+
Across daylight saving time changes (with the session tiem zone set to a
4912+
time zone that recognizes DST), this means <literal>interval '1 day'</literal>
4913+
does not necessarily equal <literal>interval '24 hours'</literal>.
4914+
For example, with the session time zone set to <literal>CST7CDT</literal>
4915+
<literal>timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' </literal>
4916+
will produce <literal>timestamp with time zone '2005-04-03 12:00-06'</literal>,
4917+
while adding <literal>interval '24 hours'</literal> to the same initial
4918+
<type>timestamp with time zone</type> produces
4919+
<literal>timestamp with time zone '2005-04-03 13:00-06'</literal>, as there is
4920+
a change in daylight saving time at <literal>2005-04-03 02:00</literal> in time zone
4921+
<literal>CST7CDT</literal>.
4922+
</para>
4923+
49064924
<table id="operators-datetime-table">
49074925
<title>Date/Time Operators</title>
49084926

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

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -598,6 +598,33 @@ SELECT (timestamp with time zone 'tomorrow' > 'now') as "True";
598598
t
599599
(1 row)
600600

601+
-- timestamp with time zone, interval arithmetic around DST change
602+
SET TIME ZONE 'CST7CDT';
603+
SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' as "Apr 3, 12:00";
604+
Apr 3, 12:00
605+
------------------------------
606+
Sun Apr 03 12:00:00 2005 CDT
607+
(1 row)
608+
609+
SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '24 hours' as "Apr 3, 13:00";
610+
Apr 3, 13:00
611+
------------------------------
612+
Sun Apr 03 13:00:00 2005 CDT
613+
(1 row)
614+
615+
SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '1 day' as "Apr 2, 12:00";
616+
Apr 2, 12:00
617+
------------------------------
618+
Sat Apr 02 12:00:00 2005 CST
619+
(1 row)
620+
621+
SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '24 hours' as "Apr 2, 11:00";
622+
Apr 2, 11:00
623+
------------------------------
624+
Sat Apr 02 11:00:00 2005 CST
625+
(1 row)
626+
627+
RESET TIME ZONE;
601628
SELECT timestamptz(date '1994-01-01', time '11:00') AS "Jan_01_1994_10am";
602629
Jan_01_1994_10am
603630
------------------------------

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -228,3 +228,16 @@ select '4 millenniums 5 centuries 4 decades 1 year 4 months 4 days 17 minutes 31
228228
@ 4541 years 4 mons 4 days 17 mins 31 secs
229229
(1 row)
230230

231+
-- test justify_hours() and justify_days()
232+
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";
233+
6 mons 5 days 4 hours 3 mins 2 seconds
234+
----------------------------------------
235+
@ 6 mons 5 days 4 hours 3 mins 2 secs
236+
(1 row)
237+
238+
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";
239+
7 mons 6 days 5 hours 4 mins 3 seconds
240+
----------------------------------------
241+
@ 7 mons 6 days 5 hours 4 mins 3 secs
242+
(1 row)
243+

‎src/test/regress/sql/horology.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -114,6 +114,15 @@ SELECT (timestamp with time zone 'today' = (timestamp with time zone 'tomorrow'
114114
SELECT (timestamp with time zone'tomorrow'= (timestamp with time zone'yesterday'+ interval'2 days'))as"True";
115115
SELECT (timestamp with time zone'tomorrow'>'now')as"True";
116116

117+
-- timestamp with time zone, interval arithmetic around DST change
118+
SETTIME ZONE'CST7CDT';
119+
SELECTtimestamp with time zone'2005-04-02 12:00-07'+ interval'1 day'as"Apr 3, 12:00";
120+
SELECTtimestamp with time zone'2005-04-02 12:00-07'+ interval'24 hours'as"Apr 3, 13:00";
121+
SELECTtimestamp with time zone'2005-04-03 12:00-06'- interval'1 day'as"Apr 2, 12:00";
122+
SELECTtimestamp with time zone'2005-04-03 12:00-06'- interval'24 hours'as"Apr 2, 11:00";
123+
RESETTIME ZONE;
124+
125+
117126
SELECTtimestamptz(date'1994-01-01',time'11:00')AS"Jan_01_1994_10am";
118127
SELECTtimestamptz(date'1994-01-01',time'10:00')AS"Jan_01_1994_9am";
119128
SELECTtimestamptz(date'1994-01-01',time with time zone'11:00-8')AS"Jan_01_1994_11am";

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -69,3 +69,10 @@ select avg(f1) from interval_tbl;
6969

7070
-- test long interval input
7171
select'4 millenniums 5 centuries 4 decades 1 year 4 months 4 days 17 minutes 31 seconds'::interval;
72+
73+
74+
-- test justify_hours() and justify_days()
75+
76+
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";
77+
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";
78+

0 commit comments

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