List:General Discussion« Previous MessageNext Message »
From:Jonas B. Nielsen Date:March 15 1999 11:02am
Subject:Date substraction problems
View as plain text  
I've a database containing some records with a datefield and I need
to do some operations on these dates in order to generate some reports.

I have a select statement which looks something like this:

select filename, count(filename) from entries where
filename like '%.pdf' and date > (curdate() - 7) group by filename

Which should return a report over the last week, I then vary the number to
generate reports over a week, a month and two months.

The dataset ranges from 1998-06-25 to 1999-03-10

When running the select statement with -30
the recordset ranges from: 1998-06-25 to 1999-03-10.

when running it with: - 60 
the resulting recordset is the same

-7 generates a recordset ranging from 1999-03-06 to 1999-03-10
	(4 days which is ok since the db hasn't been updated)
-10 generates a recordset ranging from 1999-03-03 to 1999-03-10
	(7 days see above)
-11 generates a recordset ranging from 1999-03-02 to 1999-03-10
	(8 days see above)
-20 generates a recordset ranging from 1998-06-25 to 1999-03-10
 	(somewhere in the vicinity of 8-9 months, which also is the
resulting set for -30 and -60)

MySQL doesn't like when you write days after the number, I've difficulty
seeing where it goes wrong except for the fact that all my reports are
wrong.

My MySQL installation is a rpm based 3.21.* on a RH5.2 system.		

I would prefer to use SQL to generate these reports, but I can always work
around it in Perl...

Does anybody have experience with similar problems?

Jonas B. Nielsen

Eml: jbn@stripped
Tel: 45257449   

Thread
Date substraction problemsJonas B. Nielsen15 Mar