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

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   

Date substraction problemsJonas B. Nielsen15 Mar