public inbox for [email protected]
help / color / mirror / Atom feedBUG #7722: extract(epoch from age(...)) appears to be broken
9+ messages / 6 participants
[nested] [flat]
* BUG #7722: extract(epoch from age(...)) appears to be broken
@ 2012-12-02 18:28 [email protected]
0 siblings, 1 reply; 9+ messages in thread
From: [email protected] @ 2012-12-02 18:28 UTC (permalink / raw)
To: [email protected]
The following bug has been logged on the website:
Bug reference: 7722
Logged by: Artem Anisimov
Email address: [email protected]
PostgreSQL version: 9.2.1
Operating system: Slackware Linux 14.0/amd64
Description:
The following to queries give the same result (first arguments to age()
differ in the day number only, second arguments are identical):
select extract(epoch from age('2012-11-23 16:41:31', '2012-10-23
15:56:10'));
and
select extract(epoch from age('2012-11-22 16:41:31', '2012-10-23
15:56:10'));
The problem can also be reproduced in pgsql 9.1.4 of Fedora 17.
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #7722: extract(epoch from age(...)) appears to be broken
@ 2012-12-03 06:05 Alvaro Herrera <[email protected]>
parent: [email protected]
0 siblings, 1 reply; 9+ messages in thread
From: Alvaro Herrera @ 2012-12-03 06:05 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
[email protected] wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7722
> Logged by: Artem Anisimov
> Email address: [email protected]
> PostgreSQL version: 9.2.1
> Operating system: Slackware Linux 14.0/amd64
> Description:
>
> The following to queries give the same result (first arguments to age()
> differ in the day number only, second arguments are identical):
>
> select extract(epoch from age('2012-11-23 16:41:31', '2012-10-23
> 15:56:10'));
>
> and
>
> select extract(epoch from age('2012-11-22 16:41:31', '2012-10-23
> 15:56:10'));
alvherre=# select age('2012-11-22 16:41:31', '2012-10-23 15:56:10');
age
------------------
30 days 00:45:21
(1 fila)
alvherre=# select age('2012-11-23 16:41:31', '2012-10-23 15:56:10');
age
----------------
1 mon 00:45:21
(1 fila)
The problem is that age() returns 30 days in one case, and "one month" in the
other; extract() then considers the month as equivalent to 30 days. This is
documented as such, see [1].
[1] http://www.postgresql.org/docs/current/static/functions-datetime.html
I think if you want a precise computation you should just subtract the two
dates and then extract epoch from the result.
alvherre=# select extract(epoch from timestamp '2012-11-22 16:41:31' - '2012-10-23 15:56:10');
date_part
-----------
2594721
(1 fila)
alvherre=# select extract(epoch from timestamp '2012-11-23 16:41:31' - '2012-10-23 15:56:10');
date_part
-----------
2681121
(1 fila)
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #7722: extract(epoch from age(...)) appears to be broken
@ 2012-12-04 02:17 Bruce Momjian <[email protected]>
parent: Alvaro Herrera <[email protected]>
0 siblings, 2 replies; 9+ messages in thread
From: Bruce Momjian @ 2012-12-04 02:17 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: [email protected]; [email protected]
On Mon, Dec 3, 2012 at 03:05:57AM -0300, Alvaro Herrera wrote:
> [email protected] wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 7722
> > Logged by: Artem Anisimov
> > Email address: [email protected]
> > PostgreSQL version: 9.2.1
> > Operating system: Slackware Linux 14.0/amd64
> > Description:
> >
> > The following to queries give the same result (first arguments to age()
> > differ in the day number only, second arguments are identical):
> >
> > select extract(epoch from age('2012-11-23 16:41:31', '2012-10-23
> > 15:56:10'));
> >
> > and
> >
> > select extract(epoch from age('2012-11-22 16:41:31', '2012-10-23
> > 15:56:10'));
>
> alvherre=# select age('2012-11-22 16:41:31', '2012-10-23 15:56:10');
> age
> ------------------
> 30 days 00:45:21
> (1 fila)
>
> alvherre=# select age('2012-11-23 16:41:31', '2012-10-23 15:56:10');
> age
> ----------------
> 1 mon 00:45:21
> (1 fila)
>
> The problem is that age() returns 30 days in one case, and "one month" in the
> other; extract() then considers the month as equivalent to 30 days. This is
> documented as such, see [1].
>
> [1] http://www.postgresql.org/docs/current/static/functions-datetime.html
Wow, that is a weird case. In the first test, we count the number of
days because it is less than a full month. In the second case, we call
it a full month, but then forget how long it is. Not sure how we could
improve this.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #7722: extract(epoch from age(...)) appears to be broken
@ 2012-12-04 05:16 Артем Анисимов <[email protected]>
parent: Bruce Momjian <[email protected]>
1 sibling, 0 replies; 9+ messages in thread
From: Артем Анисимов @ 2012-12-04 05:16 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; Alvaro Herrera <[email protected]>; +Cc: [email protected]
Dear Mr. Herrera and Mr. Momjian,
thank you for your feedback and for explaining that age() better not be used.
On Monday 03 December 2012 03:05:57 Alvaro Herrera wrote:
> The problem is that age() returns 30 days in one case, and "one month" in
> the other; extract() then considers the month as equivalent to 30 days.
> This is documented as such, see [1].
On Monday 03 December 2012 21:17:00 Bruce Momjian wrote:
> Wow, that is a weird case. In the first test, we count the number of
> days because it is less than a full month. In the second case, we call
> it a full month, but then forget how long it is. Not sure how we could
> improve this.
Best regargs,
Artem Anisimov.
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #7722: extract(epoch from age(...)) appears to be broken
@ 2012-12-04 19:18 Francisco Olarte (M) <[email protected]>
parent: Bruce Momjian <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: Francisco Olarte (M) @ 2012-12-04 19:18 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: Alvaro Herrera <[email protected]>; [email protected]; [email protected]
> Wow, that is a weird case. In the first test, we count the number of
> days because it is less than a full month. In the second case, we call
> it a full month, but then forget how long it is. Not sure how we could
> improve this.
I do not think this needs to be improved, the problem is given two
dates you can substract them in three different ways,
1.- (year months)+(days)+(hours minutes seconds), which is what age
does and is documented as such.
folarte=# select age('2013-11-24 16:41:31','2012-10-23 15:56:10');
age
-----------------------------
1 year 1 mon 1 day 00:45:21
(1 row)
Which is apropiate for things like 'I'm xxx old'
2.- (days)+(hours-minutes-seconds), which is what substractint dates
do ( or seems to do for me, as I've done:
select timestamp '2013-11-23 16:41:31' - '2012-10-23 15:56:10';
?column?
-------------------
396 days 00:45:21
Which I can not find a use for, but there sure are and I'm doomed to
find one soon.
3.- Exact duration ( I do this a lot at work as I need to calculate
call durations ):
folarte=# select extract(epoch from timestamp '2013-11-23 16:41:31') -
extract(epoch from timestamp '2012-10-23 15:56:10');
?column?
----------
34217121
(1 row)
folarte=# select (extract(epoch from timestamp '2013-11-23 16:41:31')
- extract(epoch from timestamp '2012-10-23 15:56:10')) * interval '1
second';
?column?
------------
9504:45:21
(1 row)
The problem I see is intervals are really complicated and difficult to
undestand, so it is at most a documentation problem ( people usually
understimate the difficulty of working with them, I see this a lot at
work ).
Francisco Olarte.
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [BUGS] BUG #7722: extract(epoch from age(...)) appears to be broken
@ 2013-08-24 01:36 Bruce Momjian <[email protected]>
parent: Francisco Olarte (M) <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2013-08-24 01:36 UTC (permalink / raw)
To: Francisco Olarte (M) <[email protected]>; +Cc: Alvaro Herrera <[email protected]>; [email protected]; pgsql-docs
I have developed the attached doc patch to address your very clear
illustration that our documentation is lacking in this area.
---------------------------------------------------------------------------
On Tue, Dec 4, 2012 at 08:18:31PM +0100, Francisco Olarte (M) wrote:
> > Wow, that is a weird case. In the first test, we count the number of
> > days because it is less than a full month. In the second case, we call
> > it a full month, but then forget how long it is. Not sure how we could
> > improve this.
>
> I do not think this needs to be improved, the problem is given two
> dates you can substract them in three different ways,
>
> 1.- (year months)+(days)+(hours minutes seconds), which is what age
> does and is documented as such.
>
> folarte=# select age('2013-11-24 16:41:31','2012-10-23 15:56:10');
> age
> -----------------------------
> 1 year 1 mon 1 day 00:45:21
> (1 row)
>
> Which is apropiate for things like 'I'm xxx old'
>
> 2.- (days)+(hours-minutes-seconds), which is what substractint dates
> do ( or seems to do for me, as I've done:
>
> select timestamp '2013-11-23 16:41:31' - '2012-10-23 15:56:10';
> ?column?
> -------------------
> 396 days 00:45:21
>
> Which I can not find a use for, but there sure are and I'm doomed to
> find one soon.
>
> 3.- Exact duration ( I do this a lot at work as I need to calculate
> call durations ):
>
> folarte=# select extract(epoch from timestamp '2013-11-23 16:41:31') -
> extract(epoch from timestamp '2012-10-23 15:56:10');
> ?column?
> ----------
> 34217121
> (1 row)
>
> folarte=# select (extract(epoch from timestamp '2013-11-23 16:41:31')
> - extract(epoch from timestamp '2012-10-23 15:56:10')) * interval '1
> second';
> ?column?
> ------------
> 9504:45:21
> (1 row)
>
> The problem I see is intervals are really complicated and difficult to
> undestand, so it is at most a documentation problem ( people usually
> understimate the difficulty of working with them, I see this a lot at
> work ).
>
> Francisco Olarte.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-docs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Attachments:
[text/x-diff] dates.diff (3.0K, 2-dates.diff)
download | inline diff:
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 425544a..a411f86
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1
*** 6431,6437 ****
</entry>
<entry><type>interval</type></entry>
<entry>Subtract arguments, producing a <quote>symbolic</> result that
! uses years and months</entry>
<entry><literal>age(timestamp '2001-04-10', timestamp '1957-06-13')</literal></entry>
<entry><literal>43 years 9 mons 27 days</literal></entry>
</row>
--- 6431,6437 ----
</entry>
<entry><type>interval</type></entry>
<entry>Subtract arguments, producing a <quote>symbolic</> result that
! uses years and months</entry>, rather than just days
<entry><literal>age(timestamp '2001-04-10', timestamp '1957-06-13')</literal></entry>
<entry><literal>43 years 9 mons 27 days</literal></entry>
</row>
*************** SELECT (DATE '2001-10-30', DATE '2001-10
*** 6794,6799 ****
--- 6794,6829 ----
days</> because May has 31 days, while April has only 30.
</para>
+ <para>
+ Subtraction of dates and timestamps can also be complex. The most
+ accurate way to perform subtraction is to convert each value to a number
+ of seconds using <literal>EXTRACT(EPOCH FROM ...)</> and compute the
+ number of <emphasis>seconds</> between the two values. This will adjust
+ for the number of days in each month, timezone changes, and daylight
+ saving time adjustments. Operator subtraction of date or timestamp
+ values returns the number of days (24-hours) and hours/minutes/seconds
+ between the values, making the same adjustments. The <function>age</>
+ function returns years, months, days, and hours/minutes/seconds,
+ performing field-by-field subtraction and then adjusting for negative
+ field values. The following queries, produced with <literal>timezone
+ = 'US/Eastern'</> and including a daylight saving time change,
+ illustrates these issues:
+ </para>
+
+ <screen>
+ SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
+ EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
+ <lineannotation>Result: </lineannotation><computeroutput>10537200</computeroutput>
+ SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
+ EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
+ / 60 / 60 / 24;
+ <lineannotation>Result: </lineannotation><computeroutput>121.958333333333</computeroutput>
+ SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
+ <lineannotation>Result: </lineannotation><computeroutput>121 days 23:00:00</computeroutput>
+ SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');
+ <lineannotation>Result: </lineannotation><computeroutput>4 mons</computeroutput>
+ </screen>
+
<sect2 id="functions-datetime-extract">
<title><function>EXTRACT</function>, <function>date_part</function></title>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Re: [BUGS] BUG #7722: extract(epoch from age(...)) appears to be broken
@ 2013-09-03 17:25 Bruce Momjian <[email protected]>
parent: Bruce Momjian <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2013-09-03 17:25 UTC (permalink / raw)
To: Francisco Olarte (M) <[email protected]>; +Cc: Alvaro Herrera <[email protected]>; [email protected]; pgsql-docs
On Fri, Aug 23, 2013 at 09:36:58PM -0400, Bruce Momjian wrote:
>
> I have developed the attached doc patch to address your very clear
> illustration that our documentation is lacking in this area.
Patch applied. It will appear in PG 9.4.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-docs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Re: [BUGS] BUG #7722: extract(epoch from age(...)) appears to be broken
@ 2013-09-03 19:59 Peter Eisentraut <[email protected]>
parent: Bruce Momjian <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Peter Eisentraut @ 2013-09-03 19:59 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: Francisco Olarte (M) <[email protected]>; Alvaro Herrera <[email protected]>; [email protected]; pgsql-docs
On 9/3/13 1:25 PM, Bruce Momjian wrote:
> On Fri, Aug 23, 2013 at 09:36:58PM -0400, Bruce Momjian wrote:
>>
>> I have developed the attached doc patch to address your very clear
>> illustration that our documentation is lacking in this area.
>
> Patch applied. It will appear in PG 9.4.
This broke the documentation build:
openjade:func.sgml:6434:37:E: character data is not allowed here
--
Sent via pgsql-docs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Re: [BUGS] BUG #7722: extract(epoch from age(...)) appears to be broken
@ 2013-09-03 20:06 Bruce Momjian <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Bruce Momjian @ 2013-09-03 20:06 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Francisco Olarte (M) <[email protected]>; Alvaro Herrera <[email protected]>; [email protected]; pgsql-docs
On Tue, Sep 3, 2013 at 03:59:57PM -0400, Peter Eisentraut wrote:
> On 9/3/13 1:25 PM, Bruce Momjian wrote:
> > On Fri, Aug 23, 2013 at 09:36:58PM -0400, Bruce Momjian wrote:
> >>
> >> I have developed the attached doc patch to address your very clear
> >> illustration that our documentation is lacking in this area.
> >
> > Patch applied. It will appear in PG 9.4.
>
> This broke the documentation build:
>
> openjade:func.sgml:6434:37:E: character data is not allowed here
Oops, sorry. Fixed. My apologies.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-docs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2013-09-03 20:06 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-12-02 18:28 BUG #7722: extract(epoch from age(...)) appears to be broken [email protected]
2012-12-03 06:05 ` Alvaro Herrera <[email protected]>
2012-12-04 02:17 ` Bruce Momjian <[email protected]>
2012-12-04 05:16 ` Артем Анисимов <[email protected]>
2012-12-04 19:18 ` Francisco Olarte (M) <[email protected]>
2013-08-24 01:36 ` Bruce Momjian <[email protected]>
2013-09-03 17:25 ` Bruce Momjian <[email protected]>
2013-09-03 19:59 ` Peter Eisentraut <[email protected]>
2013-09-03 20:06 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox