Calculating Differences Between Dates - Problem
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
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.
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:
Show quoted text
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)
Try:
Select int4( extract( epoch from
age(timestamptz(sample_request_line_item.date_shipped),
sample_request.date_of_request))/86400);
Hunter Hillegas wrote:
Show quoted text
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_differenceThis 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
OKdate_shipped: 2003-01-10
date_of_request: 2003-01-09
date_difference: -1
OKdate_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