There is a date at the entrance. And there is another date as a starting point. The time is divided into intervals of 10 15 30 minutes and so on. It is necessary to find out which interval from the starting date of 10 15 30 minutes is the date at the entrance, and the time of the beginning and end of this period.

Request for mysql.

  • Subtract from the input date the beginning of the countdown, express in minutes and divide by 15. - VladD
  • And how to take the beginning and end of the period? Help with a request, I kind of understand the idea, but I have to express it :) - vkovalchuk88

2 answers 2

SELECT CASE WHEN your_date BETWEEN someDate AND someDate + 15min THEN 1 WHEN your_date BETWEEN someDate + 15 min 01 sec AND someDate + 30 min THEN 2 WHEN your_date > someDate + 30 min 01 sec THEN 3 END AS num_interval FROM some_table

For date functions, see the MySQL documentation.

  • And how many cases are you going to register manually? - VladD
  • A specific answer to a specific question. It is necessary to find out in which account from the initial date a period of 10 15 30 minutes has hit. A question the answer. - ЫЫЫ
  • Not suitable, I need not only three intervals :) - vkovalchuk88

For a 15-minute interval, the request might look like this:

 SELECT FLOOR( (UNIX_TIMESTAMP() - UNIX_TIMESTAMP('2015-10-10 15:00:00')) / (15 * 60) ) AS begin_15, CEILING( (UNIX_TIMESTAMP() - UNIX_TIMESTAMP('2015-10-10 15:00:00')) / (15 * 60) ) AS finish_15, FROM_UNIXTIME( UNIX_TIMESTAMP('2015-10-10 15:00:00') + FLOOR((UNIX_TIMESTAMP() - UNIX_TIMESTAMP('2015-10-10 15:00:00')) / (15 * 60)) * (15 * 60) ) AS date_begin_15, FROM_UNIXTIME( UNIX_TIMESTAMP('2015-10-10 15:00:00') + CEILING((UNIX_TIMESTAMP() - UNIX_TIMESTAMP('2015-10-10 15:00:00')) / (15 * 60)) * (15 * 60) ) AS date_finish_15; +----------+-----------+---------------------+---------------------+ | begin_15 | finish_15 | date_begin_15 | date_finish_15 | +----------+-----------+---------------------+---------------------+ | 20579 | 20580 | 2016-05-11 23:45:00 | 2016-05-12 00:00:00 | +----------+-----------+---------------------+---------------------+

The date of reference is chosen '2015-10-10 15:00:00', the current date is taken as the checked date. For other intervals, the digit in the request should replace the digit 15 with a digit corresponding to the number of minutes in the interval.