On 02/16/2011 09:07 AM, Marti Raudsepp wrote:
On Wed, Feb 16, 2011 at 18:03, Thom Brown<t...@linux.com> wrote:
For the number of fortnights, that becomes:
select extract(epoch from now() - '2010-01-01 11:45:13'::timestamp)/60/60/24/14;
You'd think with PostgreSQL having such a rich type system, it
wouldn't need to come to that. It's just asking for the number of
intervals between 2 timestamps rather than the number of seconds and
dividing it to the point you get your answer.
I think a good generic solution would be an interval/interval operator
that returns numeric. Then the above becomes:
SELECT (now() - timestamp '2010-01-01 11:45:13') / interval '2 weeks';
However, looking at the code, it's not so obvious what to do if the
intervals contain months.
Regards,
Marti
Actually, what I would really like is an option in the to_char format
that would display an interval using an arbitrary combination of units.
For instance, right now I can display parts of an interval:
steve=# select to_char('10d 11h 21m 3s'::interval, 'DD');
to_char
---------
10
steve=# select to_char('10d 11h 21m 3s'::interval, 'SS');
to_char
---------
03
steve=# select to_char('10d 11h 21m 3s'::interval, 'MI');
to_char
---------
21
But those formats extract portions of the interval. I would like to be
able to display the *entire* interval filling the largest portions first
and continuing to smaller units, say:
select to_char('10d 11h 21m 3s'::interval, 'XM SS');
to_char
--------
904863
or
select to_char('10d 11h 21m 3s'::interval, 'XM MI:SS');
to_char
--------
15081:03
And as long as I'm on the subject, decimal time display would be handy
as well (especially decimal hours and minutes).
The use case is anything that accumulates time - especially for billing
purposes: 2.4 hours for the attorney, 11434.8 minutes of long-distance
this month, etc.
I can write these myself, of course, but built-in would be nice.
-Steve
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
