CODEWITHSUNDEEP
mysqlmysql-workbench
Sameer
Sameer

Reputation: 21

MySql: can this query be more efficient?

I have a table called TIMINGS

----------------create----------------

CREATE TABLE TIMINGS(
    SNO TINYINT UNIQUE NOT NULL AUTO_INCREMENT,
    TIMINGTYPE VARCHAR(20) PRIMARY KEY,
    STARTTIME TIME NOT NULL,
    ENDTIME TIME NOT NULL
)

------------------ insert ---------------------

INSERT INTO TIMINGS(TIMINGTYPE, STARTTIME, ENDTIME) VALUES
('BREAKFAST','08:34:00','10:00:00'),
('LUNCH','11:30:00','13:30:00'),
('DINNER','19:00:00','20:30:00');

so i want to know TIMINGTYPE according to current time and if the next TIMINGTYPE

ex. : if the current time between breakfast start and breakfast end then it should return 'BREAKFAST' and the next TIMINGTYPE

Expected output ex.:

----------------------------------------------------------
     CURRENTTIME   |     NEXT
-----------------------------------------------------------
1.  BREAKFAST        |      LUNCH
-----------------------------------------------------------

so i made a query which is working according to my requirement:

---------------------- select ----------------------------

SELECT (
 CASE
    WHEN DATE_FORMAT(CONVERT_TZ(NOW(),'+00:00','+05:30') ,'%H:%i:%s' ) BETWEEN (select STARTTIME from TIMINGS where TIMINGTYPE = 'BREAKFAST') AND (select ENDTIME from TIMINGS where TIMINGTYPE = 'BREAKFAST') THEN 'BREAKFAST'    
    WHEN DATE_FORMAT(CONVERT_TZ(NOW(),'+00:00','+05:30') ,'%H:%i:%s' ) BETWEEN (select STARTTIME from TIMINGS where TIMINGTYPE = 'LUNCH') AND (select ENDTIME from TIMINGS where TIMINGTYPE = 'LUNCH') THEN 'LUNCH'
    WHEN DATE_FORMAT(CONVERT_TZ(NOW(),'+00:00','+05:30') ,'%H:%i:%s' ) BETWEEN (select STARTTIME from TIMINGS where TIMINGTYPE = 'DINNER') AND (select ENDTIME from TIMINGS where TIMINGTYPE = 'DINNER') THEN 'DINNER'
    ELSE 'NOT AVAILABLE'
 END
) as CURRENTTIME, 
(
 CASE
    WHEN DATE_FORMAT(CONVERT_TZ(NOW(),'+00:00','+05:30') ,'%H:%i:%s' ) BETWEEN (select ENDTIME from TIMINGS where TIMINGTYPE = 'BREAKFAST') AND (select STARTTIME from TIMINGS where TIMINGTYPE = 'LUNCH') THEN 'LUNCH'

    WHEN DATE_FORMAT(CONVERT_TZ(NOW(),'+00:00','+05:30') ,'%H:%i:%s' ) BETWEEN (select ENDTIME from TIMINGS where TIMINGTYPE = 'LUNCH') AND (select STARTTIME from TIMINGS where TIMINGTYPE = 'DINNER') THEN 'DINNER'

    ELSE 'BREAKFAST'
 END) as NEXT

So my question is can i make it more efficient? If yes then how!

NOTE: I'm using MySql

Any help would be greatly appreciated. Thanks!

Upvotes: 0

Views: 52

Answers (1)

GMB
GMB

Reputation: 222612

You could just do:

select t.*
from timings t
where 
    (current_time between starttime endtime)    -- on-going
    or (current_time >= endttime)               -- next
order by starttime
limit 1

The logic is to search for records that are on-going or future, and then to sort them by date and keep the earliest.

Note that the use of current_time greatly simplifies the time logic.

Edit

After the question was updated: you can use union all:

-- on-going
(select 'current', timingtype from timings where current_time between starttime and endtime)
union all
-- upcoming
(select 'upcomong', timingtype from timings where current_time > endtime order by starttime limit 1)

If you want the results on the same row:

select 
    (select timingtype from timings where current_time between starttime and endtime) current,
    (select timingtype from timings where current_time > endtime order by starttime limit 1) upcoming

Upvotes: 1

Related Questions