Thread: Determining period between 2 dates
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? Thanks -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
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