MySQL Bugs: #47140: curdate() -1 doesn't work on first day of the month

Bug #47140	curdate() -1 doesn't work on first day of the month
Submitted:	4 Sep 2009 13:58	Modified:	10 Sep 2009 6:44
Reporter:	Frank van de Pasch	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.37	OS:	Windows (2003)
Assigned to:		CPU Architecture:	Any

Description:
Hello MySQL support,

We are using the free community edition of MySQL. The version is 5.1.37.

We are using the 2 below queries in MySQL to select records. Each query exports a subset of records. The 2 queries together cover 1 whole day.

The first query exports all records of the current date between 1:00:00 AM and 12:29:29 PM. The first query is started every day at 12:45:00 PM.

The second query exports all records of yesterday between 12:30:00 PM and 11:59:59 PM or current date between 12:00:00 AM and 12:59:59 AM.
The second query is started every day at 1:15:00 AM.

Field oj.Datum is of type TIMESTAMP.

These queries work perfect for every day and every time, except when the timestamp of the record is the last day of the month and the query execution date is first day of the month. 
For example: record with the timestamp of  5/31/2009  8:10:33 PM was not exported when the second query was started on 6/1/2009 at 1:15:00 AM.

Is this a bug in my MySQL version?

First query:
select oj.L_FI_Bedr_groep, oj.L_FI_Bedr_nr,
oj.Titel, oj.Geslacht, oj.Naam,
oj.Straat, oj.Postcode, oj.Plaats,
oj.Land, oj.Telefoon_1, oj.Telefoon_2,
oj.Email, oj.Opmerking, oj.Klachttekst,
oj.Voornaam_contactpersoon,
oj.Achternaam_contactpersoon,
oj.Telefoon_contactpersoon,
oj.Email_contactpersoon, oj.Servicenr,
oj.Foto_gekoppeld, oj.Ref_dealer,
op.L_UP_Pos_stnr, op.L_UP_Relatienr,
op.L_AU_Stationnr, op.L_AU_Ordernr,
op.Klacht_aantal, op.Klachttekst
from   oj, op
where  oj.Stationnr = op.Object_stationnr
and    oj.Servicenr = op.Objectnr
and    oj.Klachtbevestigen = 'J'
and    date(oj.Datum) = date(CURDATE())
and    time_to_sec(oj.Datum) >= 3600
and    time_to_sec(oj.Datum) < 45000
;

Second query:
select oj.L_FI_Bedr_groep, oj.L_FI_Bedr_nr,
oj.Titel, oj.Geslacht, oj.Naam,
oj.Straat, oj.Postcode, oj.Plaats,
oj.Land, oj.Telefoon_1, oj.Telefoon_2,
oj.Email, oj.Opmerking, oj.Klachttekst,
oj.Voornaam_contactpersoon,
oj.Achternaam_contactpersoon,
oj.Telefoon_contactpersoon,
oj.Email_contactpersoon, oj.Servicenr,
oj.Foto_gekoppeld, oj.Ref_dealer,
op.L_UP_Pos_stnr, op.L_UP_Relatienr,
op.L_AU_Stationnr, op.L_AU_Ordernr,
op.Klacht_aantal, op.Klachttekst
from   oj, op
where  oj.Stationnr = op.Object_stationnr
and    oj.Servicenr = op.Objectnr
and    oj.Klachtbevestigen = 'J'
and    ((date(oj.Datum) = date(CURDATE()-1)
and    time_to_sec(oj.Datum) >= 45000)
or     (date(oj.Datum) = date(CURDATE())
and    time_to_sec(oj.Datum) < 3600))
;

How to repeat:
Add a record with a timestamp of the last day in the month. On the first day of the month execute a query with curdate() - 1 in the where clause of a SQL query.

In the section "How to repeat" I wrote: "On the first day of the month
execute a query with curdate() - 1 in the where clause of a SQL query."

I meant "On the first day of the next month execute a query with curdate() - 1 in the where clause of a SQL query."

Thank you for a bug report. Could you please provide a repeatable test case with a dump file (create tables, insert command), the output of your queries, the expected result and the my.ini. Thanks in advance.

You do date arithmetics incorrectly, see

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

for details.

Hello Sergei,

sorry I don't see what's wrong with the date function in my query compared with the function in the manual. Could you be more specific?

Thanks,

Frank

Hello support,

attached the my.ini file. Also attached a file with the DDL statements you asked for.

When you execute the query on the first of oktober at 1:15 AM you can reproduce the problem.

Kind regards,

Frank

you cannot write date + 1, it won't work as you expect.

See the manual for DATE_ADD and DATE_SUB functions

Hello support,

So you say that the below statement is correct to select current day - 1?

date_add(curdate(), interval -1, day)

In the manual I can also see that there are arithmetics with the curdate() function so I don't see what the problem is.

Also strange is that the function curdate() - 1 perfectly works for every day in the month except when the last day of the month is selected.

Kind regards,

Frank

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.