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
|• Date substraction problems||Jonas B. Nielsen||15 Mar|