Re: Determining period between 2 dates

On 16 February 2011 15:57, Jan-Benedict Glaw <jbglaw(at)lug-owl(dot)de> wrote:
> On Wed, 2011-02-16 10:52:13 -0500, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Wed, Feb 16, 2011 at 10:47 AM, Thom Brown <thom(at)linux(dot)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