On 16 February 2011 15:57, Jan-Benedict Glaw <jbglaw@lug-owl.de> wrote:
> On Wed, 2011-02-16 10:52:13 -0500, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Wed, Feb 16, 2011 at 10:47 AM, Thom Brown <thom@linux.com> wrote:
>> > I'm wondering what people think of introducing some kind of function
>> > to extract the number of units between 2 dates? At the moment there's
>> > no way to do this. Take the following example:
>> >
>> > Event 1 is '1985-10-26 01:22:00'
>> > Event 2 is now.
>> >
>> > How many minutes between these 2 events? What I don't want is how
>> > many years, months, days and hours there are between them.
>> >
>> > This could potentially involve implementing age(timestamp, timestamp,
>> > interval), like:
>> >
>> > postgres=# SELECT age(current_date, '1985-10-26 01:22:00'::timestamp,
>> > '1 second') as age_in_seconds;
>> > age_in_seconds
>> > ----------------
>> > 798733367
>> > (1 row)
>> >
>> > Is this easily done?
>>
>> How about something like this:
>>
>> rhaas=# select (extract('epoch' from now()) - extract('epoch' from
>> timestamptz '1985-10-26 01:22:00')) / 60;
>> ?column?
>> ------------------
>> 13311989.7435394
>> (1 row)
>
> Even shorter, an interval can be used directly:
>
> emails=# select extract(epoch from now() - '2010-01-01 11:45:13'::timestamp)/60;
> ?column?
> ----------------
> 592150.7494153
> (1 row)
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.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935