Re: diference in dates in minutes

Discussions along this line should stay on the list so that other people
can learn from and add comments to the discussion.

On Sat, Feb 26, 2005 at 16:57:15 -0500,
Joel Fradkin <jfradkin(at)wazagua(dot)com> wrote:
> You sure?
> I thought date1::date - date2::date returns an integer of day's diff, but
> date -date returns an interval (least I can do a to_char on it and see day's
> hours etc that were correct.

Then your "date" column is most likely a timestamp, not a date. That is
what you want anyway if you are trying to get a time difference in minutes.
That wouldn't make much sense for dates.

> Why are they depreciating the ability to look at an interval as a string
> anyhow? Is there an approved method of looking at an interval as a string
> replacing it?

I think because the current version does some odd things and no one has put
together a spec to replace it. You can ge formatted output using EXTRACT
and suitable further manipulation.

>
> Joel Fradkin
>
> Wazagua, Inc.
> 2520 Trailmate Dr
> Sarasota, Florida 34243
> Tel. 941-753-7111 ext 305
>
> jfradkin(at)wazagua(dot)com
> www.wazagua.com
> Powered by Wazagua
> Providing you with the latest Web-based technology & advanced tools.
> C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
> This email message is for the use of the intended recipient(s) and may
> contain confidential and privileged information. Any unauthorized review,
> use, disclosure or distribution is prohibited. If you are not the intended
> recipient, please contact the sender by reply email and delete and destroy
> all copies of the original message, including attachments.
>
>
>
>
> -----Original Message-----
> From: Bruno Wolff III [mailto:bruno(at)wolff(dot)to]
> Sent: Saturday, February 26, 2005 4:16 PM
> To: Joel Fradkin
> Cc: pgsql-sql(at)postgresql(dot)org
> Subject: Re: [SQL] diference in dates in minutes
>
> On Sat, Feb 26, 2005 at 15:14:02 -0500,
> Joel Fradkin <jfradkin(at)wazagua(dot)com> wrote:
> > You probably want to convert the dates to timestamps, subtract them to
> > get an interval, extract the epoch to get timme in seconds and then divide
> > by 60 to get time in minutes.
> >
> > The converting date to timestamp part isn't trivial. You need to decide
> > on what you mean when you do this. If you really have timestamps in the
> > first place, then you can skip the covernsion step.
> >
> > They are dates and I did find I could do date - date to give me an
> interval
> > date_part('epoch',date-date) returns in secs so /60
>
> date - date won't give you an interval, it will give you an integer of some
> sort.
>
> > This appeared to work ok without converting to time stamps, but maybe I am
> > missing it if it is not correct as the example I looked at was a large
> > difference. The app is analyzing Tlogs and the difference should never be
> > too large, so I will further analyze it with real data.
> > As always I appreciate the help.
> > My real question is this an interval then and will it be depreciated soon?
>
> The Interval type won't be depreciated. Using to_char to convert intervals
> to strings is being depreciated. This won;t cause a problem for extract
> or similar functions.
>