Re: [HACKERS] Time span conversion function
Tom Lane wrote: Brendan Jurd <[EMAIL PROTECTED]> writes: Of course, it would be possible to create a shorthand version of the function which expects (text, interval), and passes directly to time_span($1, now(), $2). This bothers me a bit. That essentially says that (text, interval) has a hidden instability: the results depend on when you execute it. If we allow this form, it should be restricted to only those units (values of the text parameter) for which the result would *not* depend on now(). mm, I see your point. I suppose the only real reason to have the shorthand version is for people who want to test an interval value and don't particularly care what the startpoint is -- so they just use now() because it's a convenient way of getting a timestamp that satisfies the function. To be honest, I'd be quite comfortable with dropping the shorthand version from the proposal. If the caller has to type another 7 characters, so be it. It could fall under the "if you want it, define your own function for it" category. BJ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Time span conversion function
Brendan Jurd <[EMAIL PROTECTED]> writes: > Of course, it would be possible to create a shorthand version of the > function which expects (text, interval), and passes directly to > time_span($1, now(), $2). This bothers me a bit. That essentially says that (text, interval) has a hidden instability: the results depend on when you execute it. If we allow this form, it should be restricted to only those units (values of the text parameter) for which the result would *not* depend on now(). regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Time span conversion function
Kris Jurka wrote: On Sat, 15 Jan 2005, Brendan Jurd wrote: > SELECT time_span( 'minute', now(), interval '10:43:55' ); 643 The timestamp argument to this version of the function seems completely irrelevent. Kris Jurka I don't think so. As I pointed out in the OP, to make this function work properly you need to define a startpoint and an endpoint. The version of the function which accepts (text, timestamp, interval) arguments is really just using another notation to achieve the same thing - startpoint and endpoint. The timestamp argument is only irrelevant if you're willing to assume now() is going to be the startpoint, which is not a fair assumption IMO. I would rather give the caller the freedom of defining the startpoint himself, in either notation. Of course, it would be possible to create a shorthand version of the function which expects (text, interval), and passes directly to time_span($1, now(), $2). Does that resolve your concern? If not please explain it more fully. 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
Re: [HACKERS] Time span conversion function
On Sat, 15 Jan 2005, Brendan Jurd wrote: > > SELECT time_span( 'minute', now(), interval '10:43:55' ); > 643 > The timestamp argument to this version of the function seems completely irrelevent. Kris Jurka ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Time span conversion function
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 ---(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
