From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | aanisimov(at)inbox(dot)ru |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #7722: extract(epoch from age(...)) appears to be broken |
Date: | 2012-12-03 06:05:57 |
Message-ID: | 20121203060556.GA4601@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-docs |
aanisimov(at)inbox(dot)ru wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7722
> Logged by: Artem Anisimov
> Email address: aanisimov(at)inbox(dot)ru
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | xjl | 2012-12-03 07:17:28 | BUG #7726: Can't paste sqls size>4096 to psql console. |
Previous Message | Tom Lane | 2012-12-02 21:02:49 | Re: PITR potentially broken in 9.2 |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-12-03 16:10:09 | Re: [PATCH] Fix return type documentation for path(polygon) function |
Previous Message | aanisimov | 2012-12-02 18:28:19 | BUG #7722: extract(epoch from age(...)) appears to be broken |