SQL for Opening Hours
build another table, call it schedules, add a foreign key to the shops table primary key, a Day of week field, time_open, time_closed.
The data should look something like this:
shop_id day_of_week time_open time_closed
1 1 09:00 12:00
1 1 16:00 19:00
1 2 09:00 12:00
1 2 16:00 19:00
1 3 09:00 12:00
1 3 16:00 19:00
1 6 10:00 14:00
2 1 09:00 12:00
2 1 13:00 18:00
This will give you the opportunity to build any kind of schedules, with as many windows as you want, with how many exceptions you need. It's universal, limited only to the fact that it expects all weeks to be identical. No holidays considered, nor odd/even-week schedules that someone might use.
Edit:
With Julien's question, about working hours of a night business, it has come to my attention that the previous solution is not the best bu far. You can't have a bar open at 20:00, close at 06:00, and compare if current time (02:45) is inside this interval, because it won't be. That's why, it would be most convenient to register not the closing time, but the total working time, in the convenient unit of measure (minutes for example).
shop_id day_of_week time_open working_time
1 1 09:00 180
1 1 16:00 180
1 2 09:00 180
1 2 16:00 180
1 3 09:00 180
1 3 16:00 180
1 6 10:00 240
2 1 09:00 180
2 1 13:00 300
How to store a store opening hours in an SQL database?
A very flexible and well normalized way would be to store each opening period as one row in a table. An opening period can be encoded as the weekday it begins, the time of the day it begins and the duration it lasts. Each opening period is linked to a restaurant via a foreign key.
CREATE TABLE opening_period
(restaurant integer,
weekday integer,
time time,
duration interval,
PRIMARY KEY (restaurant,
weekday,
time,
duration),
FOREIGN KEY (restaurant)
REFERENCES restaurant
(id)
ON DELETE CASCADE,
CHECK (weekday >= 0
AND weekday < 7),
-- prevent overlapping opening periods
EXCLUDE USING gist (restaurant WITH =,
tsrange('epoch'::timestamp + time + weekday * INTERVAL '1 days',
'epoch'::timestamp + time + weekday * INTERVAL '1 days' + duration,
'[)') WITH &&));
Best way to store working hours and query it efficiently
To store normal operation hours, you would need to store a number of records containing:
- Shop - INTEGER
- DayOfWeek - INTEGER (0-6)
- OpenTime - TIME
- CloseTime - TIME
I assume for example that each shop has reduced hours during national holidays, or has plant shutdowns, so you would also need to store some override records:
- Shop - INTEGER
- OverrideStartDate - DATE
- OverrideEndDate - DATE
- DayOfWeek - INTEGER (0-6)
- AltOpenTime - TIME
- AltCloseTime - TIME
- Closed - INTEGER (0, 1)
To find open shops is trivial, but you also need to check if there are override hours:
SELECT Shop
FROM OverrideHours
WHERE OverrideStartDate <= NOW()
AND OverrideEndDate >= NOW()
AND DayOfWeek = WEEKDAY(NOW())
If there are any record returned, those shops have alternate hours or are closed.
There may be some nice SQL-fu you can do here, but this gives you the basics.
EDIT
I haven't tested this, but this should get you close:
SELECT Normal.Shop
FROM Normal
LEFT JOIN Override
ON Normal.Shop = Override.Shop
AND Normal.DayOfWeek = Override.DayOfWeek
AND NOW() BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate
WHERE Normal.DayOfWeek = WEEKDAY(NOW())
AND ((Override.Shop IS NULL AND TIME(NOW()) BETWEEN Normal.OpenTime AND Normal.CloseTime)
OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(NOW()) BETWEEN Override.AltOpenTime AND Override.AltCloseTime))
EDIT
As for efficiency, it is efficient in the sense that you only have to make one call to MySQL which is often a bottleneck if it is across a network. You'll have to test and see whether this performs to your specifications. If not, you may be to play with some indices.
EDIT
Testing. Not complete testing, but some.
mysql> select * from Normal;
+------+-----------+----------+-----------+
| Shop | DayOfWeek | OpenTime | CloseTime |
+------+-----------+----------+-----------+
| 1 | 1 | 09:00:00 | 17:00:00 |
| 1 | 5 | 09:00:00 | 16:00:00 |
| 2 | 1 | 09:00:00 | 17:00:00 |
| 2 | 5 | 09:00:00 | 17:00:00 |
+------+-----------+----------+-----------+
4 rows in set (0.01 sec)
mysql> select * from Override;
+------+-------------------+-----------------+-----------+-------------+--------------+--------+
| Shop | OverrideStartDate | OverrideEndDate | DayOfWeek | AltOpenTime | AltCloseTime | Closed |
+------+-------------------+-----------------+-----------+-------------+--------------+--------+
| 2 | 2010-12-01 | 2010-12-31 | 1 | 09:00:00 | 18:00:00 | 0 |
| 2 | 2010-12-01 | 2010-12-31 | 5 | 09:00:00 | 18:00:00 | 0 |
| 1 | 2010-12-01 | 2010-12-31 | 1 | 09:00:00 | 17:00:00 | 1 |
+------+-------------------+-----------------+-----------+-------------+--------------+--------+
3 rows in set (0.00 sec)
mysql> SET @whenever = TIMESTAMP('2010-11-23 16:05');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT WEEKDAY(@whenever);
+--------------------+
| WEEKDAY(@whenever) |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime));
+------+
| Shop |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql> SET @whenever = TIMESTAMP('2010-11-23 17:05');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime));
Empty set (0.01 sec)
mysql> SET @whenever = TIMESTAMP('2010-12-25 16:05');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime));
+------+
| Shop |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
mysql> SET @whenever = TIMESTAMP('2010-11-23 17:05');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT WEEKDAY(@whenever);
+--------------------+
| WEEKDAY(@whenever) |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime));
Empty set (0.00 sec)
Opening Hours Database Design
Presuming a robust trigger framework
On insert/update you would check if the new start or end date falls inside of any existing range. If it does then you would roll back the change.
CREATE TRIGGER [dbo].[mytable_iutrig] on [mytable] FOR INSERT, UPDATE AS
IF (SELECT COUNT(*)
FROM inserted, mytable
WHERE (inserted.startdate < mytable.enddate
AND inserted.startdate > mytable.startdate)
OR (inserted.enddate < mytable.enddate
AND inserted.enddate > mytable.startdate)) > 0
BEGIN
RAISERROR --error number
ROLLBACK TRANSACTION
END
sql calculate business hours for weekdays by opening and closing hours range
Here on Stackoverflow, it is generally frowned upon to just ask a question, expecting others to do your work for you. You should at least show some minimum effort, for example some SQL that you have tried out so far.
That said, here's the query that will return what you want. This query works even if you don't have a Date (Calendar) table, as it generates a sequence of numbers from a system table. These numbers are then, in turn, added to the startdate, to return one record for each day in the interval:
SELECT * INTO #WorkingHours
FROM (VALUES (1, 'Monday', '08:00', '16:00')
,(2, 'Tuesday', '08:00', '16:00')
,(3, 'Wednesday', '08:00', '16:00')
,(4, 'Thursday', '08:00', '16:00')
,(5, 'Friday', '08:00', '16:00'))
E(DayId, DayName, OpeningHour, ClosingHour)
DECLARE @StartDate DATETIME = '2014-04-01 09:00:00'
DECLARE @EndDate DATETIME = '2014-04-03 14:00:00'
SELECT [GivenDate], [DayID], [DayName],
DATEDIFF(HOUR, CASE WHEN @StartDate > [OpeningDateTime] THEN @StartDate ELSE [OpeningDateTime] END,
CASE WHEN @EndDate < [ClosingDateTime] THEN @EndDate ELSE [ClosingDateTime] END) AS [DateDiff]
FROM (
SELECT CAST(@StartDate + n - 1 AS DATE) AS [GivenDate]
, n AS [DayID]
, DATENAME(dw, @StartDate + n - 1) AS [DayName]
, CAST(CAST(CAST(@StartDate + n - 1 AS DATE) AS VARCHAR) + ' ' + OpeningHour AS DateTime) AS OpeningDateTime
, CAST(CAST(CAST(@StartDate + n - 1 AS DATE) AS VARCHAR) + ' ' + ClosingHour AS DateTime) AS ClosingDateTime
FROM
-- Numbers, for expanding the date range:
(SELECT ROW_NUMBER() OVER (ORDER BY object_id) n FROM sys.all_objects) Numbers
INNER JOIN #WorkingHours ON DayName = DATENAME(dw, @StartDate + n - 1)
WHERE Numbers.n <= DATEDIFF(d, @StartDate, @EndDate) + 1
) SubQuery
And here is the resulting output:
Way to store various shop opening times in a database
Normalise your data
store it as
shop_ID, Weekday, Start_hour, end_hour
weekday can have values between 1 and 7, as an output of
SELECT DAYOFWEEK('2007-02-03')
start hour and end hour can be stored in time http://dev.mysql.com/doc/refman/5.0/en/time.html
with this you would have everything covered
To find hours on a date for a shop you would do
select start_hour, end_hour from table where weekday=dayofweek(curdate()) and shop_id=1
Need 2 time intervals for a day for a shop? no problem,
`shop ID, weekday, start_hour, end_hour`
1; 1; 08:00:00 ; 09:00:00
1; 1; 10:00:00 ; 11:00:00
For exceptions, you can add an exceptions table with the date and the shop. You can query that, and if it's null(no exception), return opening hours. Alternatively you can store every date for every shop, but that would bloat your data.
What data type to use for opening hours in a database
I think it makes total sense to have two Column One for Open DateTime and One for Close Datetime. Since Once a shop is open it will have to be closed someday/sometime.
My Suggestion
I would Create a separate table for shop Opening/Closing Times. Since everytime A shop is opened it will have a close time value as well so you wont have any unwanted nulls in you second column. to me it makes total sense to have a separate table altogether for shop opening closing times.
MySQL sql query LIKE to get current opening hours
So honestly the best thing to do is to normalize your database so you can do better queries. BUT I love to see if I can solve impossible situations so here is what you can do!
This will check all the business that are open on Tuesday at 11am
SELECT * FROM `businessdetails` WHERE `date` REGEXP 'Tuesday:(0|1|2|3|4|5|6|7|8|9|10|11):(11|12|13|14|15|16|17|18|19|20|21|22|23)[^0-9]'
(Funny thing I've found I can't seem to escape the [ in the column so I had to make sure the Regex doesn't have any extra digits at the end or it may erroneously match 2 and 20 or something.)
Here's how you can generate that REGEXP string via PHP:
<?php
$regexp = date('l') . ':(' . join('|', range(0, date('G'))) . '):(' . join('|', range(date('G'), 23)) . ')[^0-9]';
DISCLAIMER I don't actually recommend doing this but I thought it was clever and wanted to share since it directly answers your question.
Calculate business hours between two dates
Baran's answer fixed and modified for SQL 2005
SQL 2008 and above:
-- =============================================
-- Author: Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime]
(
@StartDate DATETIME,
@FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @Temp BIGINT
SET @Temp=0
DECLARE @FirstDay DATE
SET @FirstDay = CONVERT(DATE, @StartDate, 112)
DECLARE @LastDay DATE
SET @LastDay = CONVERT(DATE, @FinishDate, 112)
DECLARE @StartTime TIME
SET @StartTime = CONVERT(TIME, @StartDate)
DECLARE @FinishTime TIME
SET @FinishTime = CONVERT(TIME, @FinishDate)
DECLARE @WorkStart TIME
SET @WorkStart = '09:00'
DECLARE @WorkFinish TIME
SET @WorkFinish = '17:00'
DECLARE @DailyWorkTime BIGINT
SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)
IF (@StartTime<@WorkStart)
BEGIN
SET @StartTime = @WorkStart
END
IF (@FinishTime>@WorkFinish)
BEGIN
SET @FinishTime=@WorkFinish
END
IF (@FinishTime<@WorkStart)
BEGIN
SET @FinishTime=@WorkStart
END
IF (@StartTime>@WorkFinish)
BEGIN
SET @StartTime = @WorkFinish
END
DECLARE @CurrentDate DATE
SET @CurrentDate = @FirstDay
DECLARE @LastDate DATE
SET @LastDate = @LastDay
WHILE(@CurrentDate<=@LastDate)
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + @DailyWorkTime
END
--IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
END
END
SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
END
-- Return the result of the function
IF @Temp<0
BEGIN
SET @Temp=0
END
RETURN @Temp
END
SQL 2005 and below:
-- =============================================
-- Author: Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime]
(
@StartDate DATETIME,
@FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @Temp BIGINT
SET @Temp=0
DECLARE @FirstDay DATETIME
SET @FirstDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate))
DECLARE @LastDay DATETIME
SET @LastDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @FinishDate))
DECLARE @StartTime DATETIME
SET @StartTime = @StartDate - DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)
DECLARE @FinishTime DATETIME
SET @FinishTime = @FinishDate - DATEADD(dd, DATEDIFF(dd, 0, @FinishDate), 0)
DECLARE @WorkStart DATETIME
SET @WorkStart = CONVERT(DATETIME, '09:00', 8)
DECLARE @WorkFinish DATETIME
SET @WorkFinish = CONVERT(DATETIME, '17:00', 8)
DECLARE @DailyWorkTime BIGINT
SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)
IF (@StartTime<@WorkStart)
BEGIN
SET @StartTime = @WorkStart
END
IF (@FinishTime>@WorkFinish)
BEGIN
SET @FinishTime=@WorkFinish
END
IF (@FinishTime<@WorkStart)
BEGIN
SET @FinishTime=@WorkStart
END
IF (@StartTime>@WorkFinish)
BEGIN
SET @StartTime = @WorkFinish
END
DECLARE @CurrentDate DATETIME
SET @CurrentDate = @FirstDay
DECLARE @LastDate DATETIME
SET @LastDate = @LastDay
WHILE(@CurrentDate<=@LastDate)
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + @DailyWorkTime
END
--IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
END
END
SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
END
-- Return the result of the function
IF @Temp<0
BEGIN
SET @Temp=0
END
RETURN @Temp
END
How to create SQL query for working hours
you can use a query like below.
Also here's a link to working demo.
Explanation:
initialsetfordate part defines a set of records which are from the desired date. It also additionally prunes down the dropoff_timestamp column to limit it's value to end of the day. We also get the next record's value using lead function.
Next continuous set uses the lead value found in previous set for each row and compares it with current drop off timestamp to use the minimum of the timestamps as higher time end range.
Finally we group by drivers' and sum the time difference for each record's dateranges.
; with initialsetfordate as
(
select
driver_id,
pickup_timestamp,
dropoff_timestamp =
case
when cast(dropoff_timestamp as date)<> cast(pickup_timestamp as date)
then cast(cast(dropoff_timestamp as date)as datetime)
else dropoff_timestamp
end,
new_dropoff_timestamp =
ISNULL(lead(pickup_timestamp) over(partition by driver_id order by pickup_timestamp asc),dropoff_timestamp)
from trips
where cast(pickup_timestamp as date)='2020-01-01'
),
continuousset as (
select
driver_id,
pickup_timestamp,
dropoff_timestamp=
case
when dropoff_timestamp>= new_dropoff_timestamp
then new_dropoff_timestamp
else dropoff_timestamp
end
from initialsetfordate
)
select driver_id,
CONVERT(varchar(12),
DATEADD(minute,sum(datediff(mi,pickup_timestamp,dropoff_timestamp)),0), 114) time_worked from continuousset
group by driver_id
Related Topics
When to Use an Auto-Incremented Primary Key and When Not To
Upper Limit for Autoincrement Primary Key in SQL Server
Mysql: Which to Use When: Drop Table, Truncate Table, Delete from Table
What's the Easiest Way to Preview Data from an Image Column
SQL Server Index - Any Improvement for Like Queries
SQL Server, Converting Ntext to Nvarchar(Max)
Sqlite Binding Within String Literal
Bigquery SQL for Sliding Window Aggregate
Inserting Text String with Hex into Postgresql as a Bytea
Select Query by Pair of Fields Using an in Clause
Need a Tool to Automatically Indent and Format SQL Server Stored Procedures
Good Resources for Relational Database Design
Postgresql Generate_Series of Months