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]

Re: current_date()-1 misbehaving on first day of month

>I have the following select statement whch gives me a count for[color=blue]
>downloads for previous day.
>
>SELECT
> COUNT(swid)
>FROM
> downloads
>WHERE
> date = current_date()-1[/color]
^^^^^^^^^^^^^^^ ^^^^^^^^
This isn't a reasonable way to do date math.
[color=blue]
>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.[/color]

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);
[color=blue]
>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?[/color]

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]

Re: current_date()-1 misbehaving on first day of month

toedipper wrote:
[color=blue]
> 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[/color]

Hi toedipper,

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

SELECT CURRENT_DATE() - INTERVAL 1 DAY;

HTH.
Peter.
--

http://www.phpforums.nl