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

Commit1c20857

Browse files
committed
Docs: add paragraph about date/timestamp subtraction
per suggestion from Francisco Olart
1 parent9d323bd commit1c20857

File tree

1 file changed

+31
-1
lines changed

1 file changed

+31
-1
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 31 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6431,7 +6431,7 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
64316431
</entry>
64326432
<entry><type>interval</type></entry>
64336433
<entry>Subtract arguments, producing a <quote>symbolic</> result that
6434-
uses years and months</entry>
6434+
uses years and months</entry>, rather than just days
64356435
<entry><literal>age(timestamp '2001-04-10', timestamp '1957-06-13')</literal></entry>
64366436
<entry><literal>43 years 9 mons 27 days</literal></entry>
64376437
</row>
@@ -6794,6 +6794,36 @@ SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
67946794
days</> because May has 31 days, while April has only 30.
67956795
</para>
67966796

6797+
<para>
6798+
Subtraction of dates and timestamps can also be complex. The most
6799+
accurate way to perform subtraction is to convert each value to a number
6800+
of seconds using <literal>EXTRACT(EPOCH FROM ...)</> and compute the
6801+
number of <emphasis>seconds</> between the two values. This will adjust
6802+
for the number of days in each month, timezone changes, and daylight
6803+
saving time adjustments. Operator subtraction of date or timestamp
6804+
values returns the number of days (24-hours) and hours/minutes/seconds
6805+
between the values, making the same adjustments. The <function>age</>
6806+
function returns years, months, days, and hours/minutes/seconds,
6807+
performing field-by-field subtraction and then adjusting for negative
6808+
field values. The following queries, produced with <literal>timezone
6809+
= 'US/Eastern'</> and including a daylight saving time change,
6810+
illustrates these issues:
6811+
</para>
6812+
6813+
<screen>
6814+
SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
6815+
EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
6816+
<lineannotation>Result: </lineannotation><computeroutput>10537200</computeroutput>
6817+
SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
6818+
EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
6819+
/ 60 / 60 / 24;
6820+
<lineannotation>Result: </lineannotation><computeroutput>121.958333333333</computeroutput>
6821+
SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
6822+
<lineannotation>Result: </lineannotation><computeroutput>121 days 23:00:00</computeroutput>
6823+
SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');
6824+
<lineannotation>Result: </lineannotation><computeroutput>4 mons</computeroutput>
6825+
</screen>
6826+
67976827
<sect2 id="functions-datetime-extract">
67986828
<title><function>EXTRACT</function>, <function>date_part</function></title>
67996829

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp