Обсуждение: Converting interval to numeric?
Hello, I'm trying to calculate the difference between two timestamps and have the return value in minutes. So, for instance: Time 1 = 1/1/2005 12:00 Time 2 = 1/2/2005 12:00 If you subtract Time 1 from Time 2, it returns an interval value of '1 day'. Extracting hours or minutes from this obviously gives 0. What's the trick here? Thanks! _________________________________________________________________ Don�t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/
On Fri, Feb 11, 2005 at 01:48:06 +0000, Sal Dkj <saldkj@hotmail.com> wrote: > Hello, I'm trying to calculate the difference between two timestamps and > have the return value in minutes. > > So, for instance: > Time 1 = 1/1/2005 12:00 > Time 2 = 1/2/2005 12:00 > > If you subtract Time 1 from Time 2, it returns an interval value of '1 > day'. Extracting hours or minutes from this obviously gives 0. What's the > trick here? EXTRACT the EPOCH from the difference of the timestamps. This will give you the elapsed times in seconds (as a float8 I think).
On Thu, 2005-02-10 at 22:58 -0600, Bruno Wolff III wrote: > EXTRACT the EPOCH from the difference of the timestamps. This will give > you the elapsed times in seconds (as a float8 I think). > This is unnecessarily complicated, don't you think? Doesn't it make sense that there should be a way to get an interval "cast" to only hours or only minutes or only days? Instead of an interval *always* being shown as "3 days 4 hours 5 minutes", why can't we get "76 hours" or "4565 minutes"? Isn't there a function to extract, or cast, or something? And if not, should there be? Cheers, -- Rodolfo J. Paiz <rpaiz@simpaticus.com>
On Mon, Feb 14, 2005 at 12:45:01 -0600, "Rodolfo J. Paiz" <rpaiz@simpaticus.com> wrote: > On Thu, 2005-02-10 at 22:58 -0600, Bruno Wolff III wrote: > > EXTRACT the EPOCH from the difference of the timestamps. This will give > > you the elapsed times in seconds (as a float8 I think). > > > > This is unnecessarily complicated, don't you think? Doesn't it make > sense that there should be a way to get an interval "cast" to only hours > or only minutes or only days? There already is a way to do this. You get the time in seconds and divide by 60, 3600 or 86400 as appropiate. It might make sense to make some functions to do this for some common units people might convert to. However, you can do this yourself without much trouble. > > Instead of an interval *always* being shown as "3 days 4 hours 5 > minutes", why can't we get "76 hours" or "4565 minutes"? Isn't there a > function to extract, or cast, or something? And if not, should there be? This is really about display format. The logical place to do this would be to_char. But to_char for intervals has had enough problems that it has been depreciated. If someone wanted to work on it and came up with a specification for how it was to behave, I bet that it could get undepreciated.
Bruno, thanks. >From: Bruno Wolff III <bruno@wolff.to> >To: Sal Dkj <saldkj@hotmail.com> >CC: pgsql-novice@postgresql.org >Subject: Re: Converting interval to numeric? >Date: Thu, 10 Feb 2005 22:58:25 -0600 > >On Fri, Feb 11, 2005 at 01:48:06 +0000, > Sal Dkj <saldkj@hotmail.com> wrote: > > Hello, I'm trying to calculate the difference between two timestamps and > > have the return value in minutes. > > > > So, for instance: > > Time 1 = 1/1/2005 12:00 > > Time 2 = 1/2/2005 12:00 > > > > If you subtract Time 1 from Time 2, it returns an interval value of '1 > > day'. Extracting hours or minutes from this obviously gives 0. What's >the > > trick here? > >EXTRACT the EPOCH from the difference of the timestamps. This will give >you the elapsed times in seconds (as a float8 I think). _________________________________________________________________ Don�t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/