Skip site navigation (1) Skip section navigation (2)

Time span conversion function

From: Brendan Jurd <blakjak(at)blakjak(dot)sytes(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Time span conversion function
Date: 2005-01-14 14:26:24
Message-ID: 41E7D690.7030008@blakjak.sytes.net (view raw or flat)
Thread:
Lists: pgsql-hackers
I've got an idea for a new internal function.  I'm more than willing to 
code it myself, but I'd like to run it past the list and thrash out any 
conceptual issues before I get too excited about writing a patch.

When dealing with time values, it's not unknown to want to see a 
particular time span (not the same as a postgres "interval", see below) 
expressed in one particular time unit.  You might want to know how many 
months have passed since a certain date, or how many minutes between two 
events.  Postgres doesn't really have any functions to help out with 
this.  You can always develop workarounds to get the result, but IMO 
this would be more elegantly dealt with internally.

My proposed function, let's call it time_span for now, would take a unit 
of time as text (like date_part does), and two delimiting timestamps (or 
a timestamp and an interval).  The result would be the time span 
converted into the specified unit.  So for example:

 > SELECT time_span( 'month', '2004-10-01', '2005-02-22' );
4
 > SELECT time_span( 'minute', now(), interval '10:43:55' );
643

It may seem strange at first to be using two timestamps instead of a 
single interval value, but there's a reason for it.  A postgres interval 
is just a "delta".  It does not refer to any point in time, only 
separate quantities of the various units.  Therefore it is impossible to 
convert between units that don't have consistent relationships (being 
day <=> month and day <=> year) without approximating.  By using two 
real timestamps, you can accurately convert the span into any of the 
available units.

You could make the function even more powerful by adding an optional 
fourth "precision" argument, which when given allows the function to 
return a fractional part.  Like so:

 > SELECT time_span( 'month', '2004-10-01', '2005-02-22', 2 );
4.79
 > SELECT time_span( 'minute', now(), interval '10:43:55', 4 );
643.9167

That about does it for my initial proposal.  Fire at will.

BJ


Responses

pgsql-hackers by date

Next:From: Andreas Joseph KroghDate: 2005-01-14 15:24:51
Subject: Re: pgdump
Previous:From: EnricoDate: 2005-01-14 14:20:41
Subject: Re: pgdump

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group