Thread: diference in dates in minutes
Sorry for being redundant (I asked this a while back). I was looking for in days before and used date math Date1::date - date2::date returned the days which was fine for my original question. I did get a reply mentioning the age function and he mentioned I could get minutes etc. I looked up age in the docs and did not see it. Anyone have a example of using it to get the minutes between two date functions, or another method? Joel Fradkin
On Thu, Feb 24, 2005 at 17:34:08 -0500, Joel Fradkin <jfradkin@wazagua.com> wrote: > Sorry for being redundant (I asked this a while back). > I was looking for in days before and used date math > Date1::date - date2::date returned the days which was fine for my original > question. > I did get a reply mentioning the age function and he mentioned I could get > minutes etc. > I looked up age in the docs and did not see it. > Anyone have a example of using it to get the minutes between two date > functions, or another method? 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.
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.
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
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?
If so what is a better way?
Joel
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
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.
>
On Mon, Feb 28, 2005 at 09:09:09 -0500, Joel Fradkin <jfradkin@wazagua.com> wrote:
> Yea I probably forgot respond to all. I agree (specialy for this topic).
>
> In any case, I have dates not time (dates with times).
It really helps if you use precise language when discussing problems.
date, time with time zone, time without time zone, timestamp with time zone,
and timestamp without time zone are all different types.
> I did not use datevar::date - date2::date, I did datevar - datevar2 and it
> appeared to work.
That can not give you a result that is an interval if datevar and datevar2
are actually dates. They must be some other type, probably a timestamp
of some sort.
> Since the dates I was comparing were over a year apart the number in secs
> was hard to verify. Soon as I get to debuggin the actual app where the time
> dif will be a few minutes I will let you know if it worked to do the
> date_part('epoch',date-date) returns in secs so /60.
If the date variables are of type timestamp with time zone you should
be OK. You probably want to test comparing dates in different time zones
(if you have different time offsets from GMT at different times of the year
at your locale, e.g. daylight savings vs standard time) to make sure you get
the expected result.
Yea I probably forgot respond to all. I agree (specialy for this topic).
In any case, I have dates not time (dates with times).
I did not use datevar::date - date2::date, I did datevar - datevar2 and it
appeared to work.
Since the dates I was comparing were over a year apart the number in secs
was hard to verify. Soon as I get to debuggin the actual app where the time
dif will be a few minutes I will let you know if it worked to do the
date_part('epoch',date-date) returns in secs so /60.
I appreciate your help and concern, it will be very important to us to
ensure we can do the proper calculations.
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, IncThis email message is for the use of the intended recipient(s)
andmay
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: Sunday, February 27, 2005 12:54 AM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] 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@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.
>
Sorry you are correct again it is TimeStamp not date.
So maybe that is why it appeared to work ok.
I will do as you suggest and play around with it before I accept it is a
perfect solution, but it appeared to do what I was looking for (figure the
difference in minutes).
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, IncThis email message is for the use of the intended recipient(s)
andmay
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: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Bruno Wolff III
Sent: Monday, February 28, 2005 10:25 AM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] diference in dates in minutes
On Mon, Feb 28, 2005 at 09:09:09 -0500, Joel Fradkin <jfradkin@wazagua.com> wrote:
> Yea I probably forgot respond to all. I agree (specialy for this topic).
>
> In any case, I have dates not time (dates with times).
It really helps if you use precise language when discussing problems.
date, time with time zone, time without time zone, timestamp with time zone,
and timestamp without time zone are all different types.
> I did not use datevar::date - date2::date, I did datevar - datevar2 and it
> appeared to work.
That can not give you a result that is an interval if datevar and datevar2
are actually dates. They must be some other type, probably a timestamp
of some sort.
> Since the dates I was comparing were over a year apart the number in secs
> was hard to verify. Soon as I get to debuggin the actual app where the
time
> dif will be a few minutes I will let you know if it worked to do the
> date_part('epoch',date-date) returns in secs so /60.
If the date variables are of type timestamp with time zone you should
be OK. You probably want to test comparing dates in different time zones
(if you have different time offsets from GMT at different times of the year
at your locale, e.g. daylight savings vs standard time) to make sure you get
the expected result.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings