MAX(DATETIME) bug

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Xpistiva
Forum Newbie
Posts: 5
Joined: Wed Sep 09, 2009 6:20 am

MAX(DATETIME) bug

Post by Xpistiva »

I got a table with a DATETIME column. When I manually get the latest datetime it says:
2009-09-17 08:45:29
2009-09-17 08:45:34
2009-09-17 08:45:55
2009-09-17 08:48:23
2009-09-17 08:51:06

BUT when i use MAX(date) it somehow gets these dates:
2009-09-17 08:45:30
2009-09-17 08:45:34
2009-09-17 08:45:55
2009-09-17 08:48:23
2009-09-17 08:51:07

(2009-09-17 08:45:29 is now 2009-09-17 08:45:30
2009-09-17 08:51:06 is now 2009-09-17 08:51:07)

Is there a way to fix this problem?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MAX(DATETIME) bug

Post by VladSun »

Post your full queries.
MAX will return a single result, so it's not a simple query (e.g. GROUP BY is involved)
There are 10 types of people in this world, those who understand binary and those who don't
Xpistiva
Forum Newbie
Posts: 5
Joined: Wed Sep 09, 2009 6:20 am

Re: MAX(DATETIME) bug

Post by Xpistiva »

SELECT COUNT( DISTINCT pupil) FROM answer WHERE question='1' && answer='yes' && date IN ( SELECT MAX(date) FROM answer WHERE subject='English' && class=2 GROUP BY pupil);
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MAX(DATETIME) bug

Post by VladSun »

An your query for " I manually get the latest datetime "
There are 10 types of people in this world, those who understand binary and those who don't
Xpistiva
Forum Newbie
Posts: 5
Joined: Wed Sep 09, 2009 6:20 am

Re: MAX(DATETIME) bug

Post by Xpistiva »

SELECT * FROM answer WHERE subject='English' && class=2 && question=1 && answer='yes'

This only gives me these 5 rows so it’s easy to see.

while
SELECT MAX(date) FROM answer WHERE subject='English' && class=2 GROUP BY pupil;

gives me the last 5 rows
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MAX(DATETIME) bug

Post by VladSun »

These queries are too different.
What about:
[sql]SELECT MAX(`date`) FROM `answer` WHERE `subject`='English' AND `class`=2 AND `question`=1 AND `answer`='yes' GROUP BY `pupil`[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
Xpistiva
Forum Newbie
Posts: 5
Joined: Wed Sep 09, 2009 6:20 am

Re: MAX(DATETIME) bug

Post by Xpistiva »

Thanks for the help!

I found the problem. I submitted many questions at the same time, but they didn't get the same timestamp.

When i moved "question='1'" it worked

SELECT COUNT( DISTINCT pupil) FROM answer WHERE && answer='yes' && date IN ( SELECT MAX(date) FROM answer WHERE subject='English' && class=2 && question='1' GROUP BY pupil);
Post Reply