CODEWITHSUNDEEP
mysqlrangehour
FireFoxII
FireFoxII

Reputation: 828

Mysql: get hour between selected time

I have a table like this:

id | date | time from | time to
1 | 2015-09-19 | 20:00 | 04:00
2 | 2015-09-19 | 10:00 | 23:00
3 | 2015-09-19 | 22:00 | 10:00
4 | 2015-09-20 | 10:00 | 16:00

For each row, I need hour between 22:00 and 06:00

Then first row has 6, second row has 1, third row has 8, fourth row has nothing.

Upvotes: 0

Views: 69

Answers (1)

Aguardientico
Aguardientico

Reputation: 7779

Yes, it is possible. Just use some logic:

  • If time to is lower than time from then just use the amount of hours between midnight and time from but maximum 2 hours, and add the minimum between 6 hours (the max limit) and time to.

  • In time from is lower than time to then

-- if time from is greater than 6 add 0, else add time from

-- if time to is lower than 22 add 0, else add difference between 24 and time to.

SELECT
  *,
  CASE
    WHEN time_to < time_from
    THEN
      TIME_TO_SEC (
        LEAST(TIMEDIFF(TIME('24:00'), time_from), TIME('02:00')) +
        LEAST(time_to, TIME('06:00'))
      ) / 60 / 60
    ELSE
      TIME_TO_SEC (
        CASE WHEN time_from > TIME('06:00') THEN TIME('00:00') ELSE time_from END +
        CASE WHEN time_to < TIME('22:00') THEN TIME('00:00') ELSE TIMEDIFF(TIME('24:00'), time_to) END
      ) / 60 / 60
  END new_time_to
FROM demo;

Here is the example: http://sqlfiddle.com/#!9/78571/36/0

Upvotes: 2

Related Questions