CODEWITHSUNDEEP
mysqlsqlselect
stefanosn
stefanosn

Reputation: 3324

Strange behaviour mysql query

I have a table with a timestamp field including these values:

2012-03-12 12:53:34
2012-03-12 12:54:11 
2012-03-12 01:02:37
2012-03-12 01:03:04
2012-03-12 01:03:46

My query is the following

SELECT * FROM 
(Select MESSAGE_OF_USER,MESSAGE_TIME as time,CONVERSATION_ID 
 from poem_authors_messages 
 where (CONVERSATION_ID='2' or CONVERSATION_ID='1') and 
       MESSAGE_TIME>'2012-03-12 12:53:34' 
 ORDER BY time DESC) poem_authors_messages 
ORDER BY time ASC

Notice the time. It should give me these results

2012-03-12 12:54:11 
2012-03-12 01:02:37
2012-03-12 01:03:04
2012-03-12 01:03:46

but instead i get

2012-03-12 12:54:11

why is this happening?

Any help appreciated.

Upvotes: 1

Views: 66

Answers (3)

Gabriel Santos
Gabriel Santos

Reputation: 4974

You have said to mysql: select only if time greater then '2012-03-12 12:53:34'

and MESSAGE_TIME>'2012-03-12 12:53:34'

Change and MESSAGE_TIME>'2012-03-12 12:53:34' to and MESSAGE_TIME <= '2012-03-12 12:53:34'

To get

2012-03-12 12:53:34
2012-03-12 01:02:37
2012-03-12 01:03:04
2012-03-12 01:03:46

Upvotes: 1

MatBailie
MatBailie

Reputation: 86706

3 of your times are AM and not PM. This means that they're not greater than your filter time...

                     |  MESSAGE_TIME>'2012-03-12 12:53:34'
---------------------+--------------------------------------
2012-03-12 01:02:37  |  No
2012-03-12 01:03:04  |  No
2012-03-12 01:03:46  |  No
2012-03-12 12:54:11  |  Yes
2012-03-12 13:02:37  |  Yes
2012-03-12 13:03:04  |  Yes
2012-03-12 13:03:46  |  Yes

Upvotes: 3

Sauer
Sauer

Reputation: 1469

Have you considered the AM/PM stuff? There is no information on that in your Mail. Maybe your assumption is just wrong and the sql is right...

However: Please provide your DB Version... Oracle?

Upvotes: 0

Related Questions