current_date()-1 misbehaving on first day of month

[toedipper]

Hello,

PHP and mysql

I have the following select statement whch gives me a count for
downloads for previous day.

SELECT
COUNT(swid)
FROM
downloads
WHERE
date = current_date()-1

This works on every day of the month except the 1st day....so on 1st
November my values for 31 Oct showed 0 even though elsewhere I could see
that there was activity. It was the same on 1 Oct for 30th Sept and
will be no doubt the same on 1st Dec for 30 Nov.

Any ideas on why it shows zero? Does current_date()-1 have problems
when it's the first day of month?

Thanks,

td.
Great news!
Cure found for Bird Flu!
http://www.pocketpcheaven.com/birdflue.php

[Gordon Burditt]

>I have the following select statement whch gives me a count for

downloads for previous day.

SELECT
COUNT(swid)
FROM
downloads
WHERE
date = current_date()-1 ^^^^^^^^^^^^^^^^^^^^^^^
This isn't a reasonable way to do date math.
This works on every day of the month except the 1st day....so on 1st
November my values for 31 Oct showed 0 even though elsewhere I could see
that there was activity.
Try running:

select current_date()-40;

and observe that it makes sense on *NO* days of the month.
It's doing INTEGER, not DATE, math.

Then try:
select subdate(current_date(), INTERVAL 40 DAY);
It was the same on 1 Oct for 30th Sept and
will be no doubt the same on 1st Dec for 30 Nov.

Any ideas on why it shows zero? Does current_date()-1 have problems
when it's the first day of month?

current_date()-1 makes about as much sense as current_date()/2.583
(none at all). It's a coding error regardless of what day of the
month it is. Even a stopped clock is sometimes right.

Learn about adddate() and subdate().

Gordon L. Burditt

[Peter van Schie]

toedipper wrote:

I have the following select statement whch gives me a count for
downloads for previous day.

SELECT
COUNT(swid)
FROM
downloads
WHERE
date = current_date()-1

Hi toedipper,

I think you're looking for something like this instead:

SELECT CURRENT_DATE() - INTERVAL 1 DAY;

HTH.
Peter.
--
http://www.phpforums.nl