Re: Determining period between 2 dates

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)

--      Jan-Benedict Glaw      jbglaw@lug-owl.de              +49-172-7608481
Signature of:                 Friends are relatives you make for yourself.
the second  :