时间周期问题，在SQL描述查询。 [英] Time Cycle issue in SQL Quires.

问题描述

在我的Rails应用程序目前已经在申请每家餐馆开幕式和闭幕式的时间表。像

In my rails application currently have opening and closing schedules in the application for every restaurant. Like

Monday 5:00 AM to 11:45 PM
Tuesday 6:00 AM to 11:00 PM
Wednesday 9:00 AM to 8:00 PM
Thursday 11:00 AM to 11:45 PM
Friday 5:00 AM to 11:45 PM
Saturday 5:00 AM to 11:45 PM

我有救了上述时间到，并24小时数据库的时间格式的数据库。这是工作完全正常。因为当我试图获取有关开放式餐厅，由于时间至24小时的时间信息，我能够从数据库中查询数据。但现在我的客户想要的截止时间是上午2:00，上午2:30，餐厅中期以后晚上肯定会关闭。 它是如何可能来管理SQL描述查询？我怎么能检查一时间餐厅规定的期限是开放与否？

I have saved the above time into the database with 24hour database time format. It is working perfectly fine. as when i try to get information about the open restaurant time due to 24 hours time i am able to query the data from the database. But now my client wants that the closing time can be 2:00 AM, 2:30 AM as restaurant closes definitely after the mid night. How it is possible to manage in the SQL Quires? How can i check for a specified period of a time restaurant is open or not?

目前我有以下的查询被执行：

Currently I have the following Query being executed:

SELECT 
COUNT(*)
FROM
`sechedules`
WHERE
`sechedules`.`sechedulable_id` = 16
    AND `sechedules`.`sechedulable_type` = 'Operation'
    AND (day = 'Wednesday')
    AND (opening_time <= '21:28:23'
    and closing_time >= '02:28:23'
    and is_closed = 0);

我可以改变天的周期。它可以开始从上午5:00到上午04时59分。但如何在数据库中管理的比较，我没有得到最佳的解决方案。

I can change the cycle of the day. It can start from the 5:00 AM to 4:59 AM. But how to manage in the database comparisons I'm not getting the best possible solution.

推荐答案

我建议你制定各地的开始时间和持续时间的查询。你可以从你的present数据集执行此操作。此查询获取开始时间和持续时间从表中。

I suggest you formulate your queries around start-time and duration. You could do this from your present dataset. This query gets start time and duration from your table.

select weekday, 
       start, 
       stop,
       timediff(
           if (TIMESTAMPDIFF(MINUTE,start,stop)<0,stop+INTERVAL 24 HOUR, stop), 
           start) duration 
  from sechedules

正如你所看到的，但它确实有些猴子业务为24小时添加到截止时间，如果它显示为早于开盘时间。

As you can see, it does some monkey business to add 24 hours to the closing time if it shows up as earlier than the opening time.

然后，您可以检查多少记录一天的这样一个特定的时间相匹配。

Then, you can check how many records match a particular time of day like this.

select count(*) cnt
 where sechedulable_id = 16
   and day = 'Wednesday'
   and start <= '21:28:23'
   and '21:28:23' <= if(TIMESTAMPDIFF(MINUTE,start,stop)<0,stop+INTERVAL 24 HOUR, stop)

这篇关于时间周期问题，在SQL描述查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！