Good afternoon, please tell me how to write a request for a sample of the time interval but ignoring the year. Those. for example, there is an interval of '31 .07 22:00:00 '- '31 .08 22:00:00' and you need to run over the table ignoring the year and select all occurrences in this interval.
(option to create a separate request for each year separately - not comme il faut)

for Between need a year, please tell me how to be in this case?

I use mysql 5.6

  • The easiest way to put the substr before between, another option is to see the date functions for mysql - nick_n_a

1 answer 1

select * from table where DATE_FORMAT(date,'%m%d%h%i') between '07312200' and '08312200'

Please note, the format of the date is Month-Day-Hour-Minute, so that the comparison for the interval would work correctly.

  • It works, thanks !!! - Igor Kalamurda
  • one
    Is there any solution that would use the index (if we consider that the date field is indexed)? - MaxU
  • @MaxU This is not possible by the definition of a binary search tree used for indexing in databases. The tree itself is built on more-less, and the entire value as a whole. To use the index, we need to look at certain intervals (for each year separately). then these intervals must first be generated, and secondly, the optimizer should be explained that the index should be used (it usually does not use the index even with a normal OR). And if the first is at least doable, then the second is unlikely - Mike
  • @Mike, what about using generated (virtual) columns ? - MaxU 4:02 pm
  • @MaxU If you have MySQL version 5.7 then why not. In other databases, this is called a functional index and works automatically. those. We build the index directly using the expression date_format(...) and the optimizer for such a query starts using it. Well, here a little crooked path with virtual columns was invented ... And the TC even in question indicated that it had 5.6 - Mike