Determining period between 2 dates

On Wed, Feb 16, 2011 at 10:47 AM, Thom Brown <thom@linux.com> wrote:

Hi all,

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)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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 :

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

Thom Brown <thom@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.

The SQL standard has syntax to support getting that in YEAR, MONTH,
DAY, HOUR, MINUTE, or SECOND (with the ability to specify decimal
positions for SECOND). Nothing in there about fortnights, however.

<left paren> <datetime value expression> <minus sign>
<datetime term> <right paren> <interval qualifier>

I seem to remember previous discussions where people have resisted
implementing this part of the standard, although I can't remember
the reason. I'll probably be reminded soon... :-)

-Kevin

On Wed, Feb 16, 2011 at 18:03, Thom Brown <thom@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

On 02/16/2011 09:07 AM, Marti Raudsepp wrote:

On Wed, Feb 16, 2011 at 18:03, Thom Brown<thom@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