I have a table like this:

table view

I can not make a selection by date and time. I do this:

SELECT * FROM HistoryReaders WHERE (DateReaders BETWEEN "2018-08-30" AND "2018-08-31") OR (DateReaders = "2018-08-30" AND TimeReaders >= "04:00:00") AND (DateReaders = "2018-08-31" AND TimeReaders <= "13:00:00")

But values ​​are given only for the latest date.

Tell me how to make a request

  • so the request correctly fulfills - in the second part of the condition (DateReaders = "2018-08-31" AND TimeReaders >= "04:00:00") AND (DateReaders = "2018-08-31" AND TimeReaders <= "13:00:00") you select only one date - 20180831. For what period of time do you need to select data? - Novitskiy Denis
  • Well, for example, the date range 2018-08-30 and 2018-08-31. - Ivan Zhilnikov
  • then remove this condition from the request (DateReaders = "2018-08-31" AND TimeReaders >= "04:00:00") AND (DateReaders = "2018-08-31" AND TimeReaders <= "13:00:00") - Novitskiy Denis
  • or add parentheses if you want to use or . That's it - SELECT * FROM HistoryReaders WHERE (DateReaders BETWEEN "2018-08-30" AND "2018-08-31") OR ((DateReaders = "2018-08-30" AND TimeReaders >= "04:00:00") AND (DateReaders = "2018-08-31" AND TimeReaders <= "13:00:00")) - Novitskiy Denis
  • although, honestly, I do not understand the point in the second part of the condition, since in any case, it is included in the first ... - Novitskiy Denis

1 answer 1

You need to choose by the amount of date and time

 SELECT * FROM HistoryReaders WHERE (DateReaders BETWEEN "2018-08-30" AND "2018-08-31") AND (DateReaders + TimeReaders BETWEEN "2018-08-30 04:00:00"AND "2018-08-31 13:00:00")

Condition

 (DateReaders BETWEEN "2018-08-30" AND "2018-08-31")

necessary to initially substantially limit the sample. If your DateReaders field DateReaders indexed, we first do a quick sample of the indexed field, and then we finish it.

If there is no index on the DateReaders field, then the condition is not necessary. Anyway there will be a full search of records.

In general, the separation of the date and time fields is 90% bad architecture. If you do not need samples for a certain time for each day, then these fields should be combined into one field of type TIMESTAMP

  • thank! I will redo it under TIMESTAMP - Ivan Zhilnikov
  • If my answer solved your problem, mark it as correct (ticked by the number of votes) - Anton Shchyrov
  • Selection by sum of table fields == performance degradation. Indices go to the forest ... - Akina
  • @Akina Yes. Therefore, I suggested first selecting only by date, or combining the fields - Anton Shchyrov