datetime comparison problem

[dapperdanman1400]

I have a query in a php page that goes something like this:

SELECT * FROM posts WHERE post_time < '$php_var_date' LIMIT 0,1

I'm using this in a blog where I need the previous post's post date. php_var_date is an ISO 8601 formatted date. The post_time is of the data type datetime. While I don't get an error on execution of the query, it keeps returning the date EQUAL to php_var_date instead of the one previous to it. My question is, is this a MySQL glitch or is it something incorrectly set on the server?

 

[feherke]

Hi

I think you want this :

[b]select[/b] * [b]from[/b] posts [b]where[/b] post_time < [i]'$php_var_date'[/i] [red][b]order by[/b] post_time [b]desc[/b][/red] [b]limit[/b] 0,1

But this does not solve the equality problem. Are you sure both post_time and $php_var_date has both date and time part ?

Feherke.

http://rootshell.be/~feherke/

 

[dapperdanman1400]

Thanks for the tip, I've changed it now. But even after the change, it's still goofy. Both date and time are indeed there, and they are correct for the time that they were posted.

 

[dapperdanman1400]

OK, issue has been resolved. It was a case of the actual solution being too simple, and I was ignorant enough to think the problem was more complex than it was. I just need to format my date SPECIFICALLY to what was in the database in the column. That's what I get for being used to SQL Server where you could be lazy with date formatting.

 

[r937]

your original post said it was ISO format, turns out it wasn't, eh?

ISO format is yyyy-mm-dd, and that will always work

r937.com | rudy.ca

 

[dapperdanman1400]

No, it's still ISO, but php ISO 8601 adds a 'T' between the date and time part with no space, and MySQL has a space instead, so that was throwing it all wonky

 

[r937]

um, yeah, i should have remembered that difference :blush:

thanks



r937.com | rudy.ca