MYSQL where with date time field

Question

Can anyone tell me what is the wrong with the query? It's not following the date condition. It's returning all the data and I want data less than the mentioned date.

SELECT `messages`.*, `user`.`firstname`, `user`.`lastname`, `user`.`organisation_name`, `user`.`type`, `user_info`.`profile_picture`, `user_info`.`logo`, `user`.`email`
FROM (`messages`) JOIN
     `user`
     ON `user`.`user_id` = `messages`.`from_id` JOIN
     `user_info`
     ON `user_info`.`uid` = `messages`.`from_id`
WHERE `messages`.`from_id` = '4' AND
      `messages`.`to_id` = '24572' AND 
      `messages`.`time_sent` <= '2016-06-23 12:41:47' OR
      `messages`.`from_id` = '24572' AND
      `messages`.`to_id` = '4' AND 
     `messages`.`time_sent` <= '2016-06-23 12:41:47'
ORDER BY `msg_id` DESC

its returning all the data and i want data less than the mentioned date — Ashok Jutlawar, Jun 27 '16 at 11:27

score 2 · Answer 1 · answered Jun 27 '16 at 11:32

I guess you can get all messages by user as a sender or reciever. Can you try below, please?

SELECT
    `messages`.*, `user`.`firstname`,
    `user`.`lastname`,
    `user`.`organisation_name`,
    `user`.`type`,
    `user_info`.`profile_picture`,
    `user_info`.`logo`,
    `user`.`email`
FROM
    `messages`
JOIN `user` ON `user`.`user_id` = `messages`.`from_id`
JOIN `user_info` ON `user_info`.`uid` = `messages`.`from_id`
WHERE
    (
        `messages`.`from_id` = '4'
        AND `messages`.`to_id` = '24572'
        AND `messages`.`time_sent` <= '2016-06-23 12:41:47'
    )
OR (
    `messages`.`from_id` = '24572'
    AND `messages`.`to_id` = '4'
    AND `messages`.`time_sent` <= '2016-06-23 12:41:47'
)
ORDER BY
    `messages`.`id` DESC

no its not giving me the error.it is returning the wrong data.its not following the date less than condition in where clause — Ashok Jutlawar, Jun 27 '16 at 11:43

score 0 · Answer 2 · answered Jun 27 '16 at 11:24

Try by removing brackets "(" and ")":

SELECT `messages`.*, `user`.`firstname`, `user`.`lastname`, `user`.`organisation_name`, `user`.`type`, `user_info`.`profile_picture`, `user_info`.`logo`, `user`.`email`
FROM `messages` JOIN
     `user`
     ON `user`.`user_id` = `messages`.`from_id` JOIN
     `user_info`
     ON `user_info`.`uid` = `messages`.`from_id`
WHERE `messages`.`from_id` = '4' AND
      `messages`.`to_id` = '24572' AND 
      `messages`.`time_sent` <= '2016-06-23 12:41:47' OR
      `messages`.`from_id` = '24572' AND
      `messages`.`to_id` = '4' AND 
     `messages`.`time_sent` <= '2016-06-23 12:41:47'
ORDER BY `msg_id` DES

And it will be helpful if you share error message and also take care about your 'AND' and 'OR' condition, as they don't have brackets so there priority will be different then what you might expect.

score 0 · Answer 3 · edited Jun 27 '16 at 12:04

Please try this..

SELECT `messages`.*, `user`.`firstname`, `user`.`lastname`, `user`.`organisation_name`, `user`.`type`, `user_info`.`profile_picture`, `user_info`.`logo`, `user`.`email`
FROM (`messages`) JOIN
     `user`
     ON `user`.`user_id` = `messages`.`from_id` JOIN
     `user_info`
     ON `user_info`.`uid` = `messages`.`from_id`
WHERE `messages`.`from_id` = '4' AND
      `messages`.`to_id` = '24572' AND 
      `messages`.`time_sent` <= '2016-06-23 12:41:47' OR
      `messages`.`from_id` = '24572' AND
      `messages`.`to_id` = '4' AND 
     `messages`.`time_sent` <= '2016-06-23 12:41:47'
ORDER BY `messages`.`msg_id` DESC

score 0 · Answer 4 · edited May 23 '17 at 10:29

0

Categorize the AND and OR with the () brackets AND make sure table field type

SELECT messages.*, user.firstname, user.lastname, user.organisation_name, user.type, user_info.profile_picture, user_info.logo, user.email 
FROM messages 
JOIN user ON user.user_id = messages.from_id 
JOIN user_info ON user_info.uid = messages.from_id 
WHERE (messages.from_id = '4' AND messages.to_id = '24572' AND messages.time_sent <= '2016-06-23 12:41:47')
    OR (messages.from_id = '24572' AND messages.to_id = '4' AND messages.time_sent <= '2016-06-23 12:41:47')
ORDER BY msg_id DESC

Read MySQL: Combining the AND and OR Conditions and Should I use field 'datetime' or 'timestamp'?

Example

edited May 23 '17 at 10:29

Community

1
1

answered Jun 27 '16 at 11:31

Abdulla Nilam

36,589
17
64
85

i did the above but its not following the date condition – Ashok Jutlawar Jun 27 '16 at 11:35
@AshokJutlawar try with `2016-06-23 00:00:00` – Abdulla Nilam Jun 27 '16 at 11:35
no i cannot change time its stored in the database and i have to use that value only – Ashok Jutlawar Jun 27 '16 at 11:37
@AshokJutlawar try like this `messages.time_sent >=2016-06-23 12:41:47` – Abdulla Nilam Jun 27 '16 at 11:40
@AshokJutlawar If this help please **[Mark it as ACCEPT**](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work). Hence [**VOTE UP** will lead my TAG count](http://meta.stackexchange.com/questions/173399/how-to-upvote-on-stack-overflow) – Abdulla Nilam Mar 02 '17 at 12:57

MYSQL where with date time field

4 Answers4