Postgres Pro
Downloads
Home   >   mailing lists

Thread: Calculating Differences Between Dates - Problem

Calculating Differences Between Dates - Problem

From
Hunter Hillegas
Date:
I am trying to calculate the number of days between two dates.

Right now I have this query fragment:

 date_part('day'::text,
age(timestamptz(sample_request_line_item.date_shipped),
sample_request.date_of_request)) AS date_difference

This works as long as the date doesn't span the month. In those cases I get
bad values. i.e.:

date_shipped: 2003-01-10
date_of_request: 2003-01-09
date_difference: -1
OK

date_shipped: 2003-01-10
date_of_request: 2003-01-09
date_difference: -1
OK

date_shipped: 2002-12-16
date_of_request: 2002-10-29
date_difference: 18
NOT OK - it is more days than this.

Looks like I am just not printing out the month as well as the day
difference... But I need it all translated into days, not month(s)+days(s).
Can I do this?

Thanks,
Hunter

Re: Calculating Differences Between Dates - Problem

From
Bruno Wolff III
Date:
On Fri, Jan 10, 2003 at 12:35:37 -0800,
  Hunter Hillegas <lists@lastonepicked.com> wrote:
> I am trying to calculate the number of days between two dates.

Just subtract the dates from each other. The result will be an integer type
with the number of days between the two dates.

> date_shipped: 2002-12-16
> date_of_request: 2002-10-29
> date_difference: 18
> NOT OK - it is more days than this.

You are just comparing the days of the month, not the actual days.

Re: Calculating Differences Between Dates - Problem

From
Simon Mitchell
Date:
Just subtract the dates from each other works ok for me.


psql=# select
to_date('2002-12-16','YYYY-MM-DD')-to_date('2002-10-29','YYYY-MM-DD');
?column?
----------
      48
(1 row)

or
psql=# select
to_date('2002-12-16','YYYY-MM-DD')-to_date('1999-10-29','YYYY-MM-DD') as
days ;
days
------
1144
(1 row)

Simon
Bruno Wolff III wrote:

>On Fri, Jan 10, 2003 at 12:35:37 -0800,
>  Hunter Hillegas <lists@lastonepicked.com> wrote:
>
>
>>I am trying to calculate the number of days between two dates.
>>
>>
>
>Just subtract the dates from each other. The result will be an integer type
>with the number of days between the two dates.
>
>
>
>>date_shipped: 2002-12-16
>>date_of_request: 2002-10-29
>>date_difference: 18
>>NOT OK - it is more days than this.
>>
>>
>
>You are just comparing the days of the month, not the actual days.
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>

Re: Calculating Differences Between Dates - Problem

From
Jean-Luc Lachance
Date:
Try:

Select int4( extract( epoch from
age(timestamptz(sample_request_line_item.date_shipped),
sample_request.date_of_request))/86400);



Hunter Hillegas wrote:
>
> I am trying to calculate the number of days between two dates.
>
> Right now I have this query fragment:
>
>  date_part('day'::text,
> age(timestamptz(sample_request_line_item.date_shipped),
>  AS date_difference
>
> This works as long as the date doesn't span the month. In those cases I get
> bad values. i.e.:
>
> date_shipped: 2003-01-10
> date_of_request: 2003-01-09
> date_difference: -1
> OK
>
> date_shipped: 2003-01-10
> date_of_request: 2003-01-09
> date_difference: -1
> OK
>
> date_shipped: 2002-12-16
> date_of_request: 2002-10-29
> date_difference: 18
> NOT OK - it is more days than this.
>
> Looks like I am just not printing out the month as well as the day
> difference... But I need it all translated into days, not month(s)+days(s).
> Can I do this?
>
> Thanks,
> Hunter
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster