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
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
