Re: diference in dates in minutes - Mailing list pgsql-sql

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@wazagua.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@wazagua.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@wolff.to] 
> Sent: Saturday, February 26, 2005 4:16 PM
> To: Joel Fradkin
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] diference in dates in minutes
> 
> On Sat, Feb 26, 2005 at 15:14:02 -0500,
>   Joel Fradkin <jfradkin@wazagua.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.
>