SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

------------------------------------------------------------
-- Table definitions
------------------------------------------------------------

CREATE TABLE Timezone (
    TimezoneId INT IDENTITY(1,1),
    Name NVARCHAR(100),
    Offset VARCHAR(10)
)
CREATE TABLE DayOfTheWeek (
    DayOfTheWeekId INT IDENTITY(1,1),
    DayName NVARCHAR(100)
)
CREATE TABLE Place (
    PlaceId INT IDENTITY(1,1),
    Name NVARCHAR(100),
    TimeZoneId INT
)
CREATE TABLE StoreHour (
	StoreHourId INT IDENTITY(1,1),
	PlaceId INT,
	DayOfTheWeekId INT,
	OpenHour TIME,
	CloseHour TIME
)

------------------------------------------------------------
-- Populate lookup tables
------------------------------------------------------------

-- All timezones
INSERT Timezone
SELECT TZI.name, TZI.current_utc_offset	
FROM sys.time_zone_info TZI

-- 7 days of the week
INSERT DayOfTheWeek
SELECT DATENAME(weekday, DATEADD(day, N.N, '1900-01-01'))
FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) N(N)

------------------------------------------------------------
-- Generate test data for places and hours
------------------------------------------------------------

-- Breakfast and dinner daily in each timezone
INSERT Place
SELECT
    'Breakfast and dinner everyday in ' + TZ.Name + ' (' + TZ.Offset + ')' AS Name,
	TZ.TimezoneId
FROM Timezone TZ

-- With limited hours (06:00 - 08:00 and 17:00 - 19:00) everyday
INSERT StoreHour
SELECT P.PlaceId, DOW.DayOfTheWeekId, H.OpenHour, H.CloseHour
FROM Place P
CROSS JOIN DayOfTheWeek DOW
CROSS JOIN (VALUES ('06:00', '08:00'),  ('17:00', '19:00')) H(OpenHour, CloseHour)
WHERE P.Name LIKE 'Breakfast and dinner everyday%'

-- Lunch on a specific day in each timezone
INSERT Place
SELECT
    'Lunch ' + DOW.DayName + 's in ' + TZ.Name + ' (' + TZ.Offset + ')' AS Name,
	TZ.TimezoneId
FROM Timezone TZ
CROSS JOIN DayOfTheWeek DOW

-- With limited hours (12:00 - 14:00) for specific days
INSERT StoreHour
SELECT P.PlaceId, DOW.DayOfTheWeekId, H.OpenHour, H.CloseHour
FROM Place P
CROSS JOIN DayOfTheWeek DOW
CROSS JOIN (VALUES ('12:00', '14:00')) H(OpenHour, CloseHour)
WHERE P.Name LIKE 'Lunch ' + DOW.DayName + '%'

-- Late night and dinner daily in each timezone
INSERT Place
SELECT
    'Late night daily in ' + TZ.Name + ' (' + TZ.Offset + ')' AS Name,
	TZ.TimezoneId
FROM Timezone TZ

-- With limited hours (22:00 - 24:00) for specific days
-- Special case where 24:00 is represented as 00:00.
INSERT StoreHour
SELECT P.PlaceId, DOW.DayOfTheWeekId, H.OpenHour, H.CloseHour
FROM Place P
CROSS JOIN DayOfTheWeek DOW
CROSS JOIN (VALUES ('22:00', '00:00')) H(OpenHour, CloseHour)
WHERE P.Name LIKE 'Late night daily%'

------------------------------------------------------------
-- Main select
------------------------------------------------------------

DECLARE @IncludeAll BIT = 0
DECLARE @UtcNow DATETIME = GETUTCDATE()

SELECT
    P.Name,
    CAST(DT.LocalTime AS CHAR(5)) AS LocalTime,
    DT.LocalWeekdayName,
    CASE WHEN O.IsOpen = 1 THEN 'Open' ELSE 'Closed' END AS OpenOrClosed
FROM Place P
JOIN Timezone TZ ON TZ.TimezoneId = P.TimezoneId
CROSS APPLY (
    -- Intermediate local time calculations
    SELECT
        @UtcNow AT TIME ZONE 'UTC' AT TIME ZONE TZ.Name AS LocalDateTime
) LT
CROSS APPLY (
    -- More intermediate local time calculations
    SELECT
        DATENAME(weekday, LT.LocalDateTime) AS LocalWeekdayName,
        CAST(LT.LocalDateTime AS TIME) AS LocalTime
) DT
JOIN DayOfTheWeek DOW ON DOW.DayName = DT.LocalWeekdayName
CROSS APPLY (
    -- This is the big test
    SELECT CASE WHEN EXISTS (
        SELECT *
        FROM StoreHour H
        WHERE H.PlaceId = P.PlaceId
        AND H.DayOfTheWeekId = DOW.DayOfTheWeekId
        AND H.OpenHour <= DT.LocalTime
        AND (H.CloseHour > DT.LocalTime OR H.CloseHour = '00:00')
    ) THEN 1 ELSE 0 END AS IsOpen
) O
WHERE (O.IsOpen = 1 OR @IncludeAll = 1)


Name
LocalTime
LocalWeekdayName
OpenOrClosed


Breakfast and dinner everyday in Pacific Standard Time (Mexico) (-08:00)
06:16
Friday
Open

Breakfast and dinner everyday in UTC-08 (-08:00)
06:16
Friday
Open

Breakfast and dinner everyday in Pacific Standard Time (-08:00)
06:16
Friday
Open

Breakfast and dinner everyday in US Mountain Standard Time (-07:00)
07:16
Friday
Open

Breakfast and dinner everyday in Mountain Standard Time (Mexico) (-07:00)
07:16
Friday
Open

Breakfast and dinner everyday in Mountain Standard Time (-07:00)
07:16
Friday
Open

Breakfast and dinner everyday in Yukon Standard Time (-07:00)
07:16
Friday
Open

Breakfast and dinner everyday in Arabic Standard Time (+03:00)
17:16
Friday
Open

Breakfast and dinner everyday in Turkey Standard Time (+03:00)
17:16
Friday
Open

Breakfast and dinner everyday in Arab Standard Time (+03:00)
17:16
Friday
Open

Breakfast and dinner everyday in Belarus Standard Time (+03:00)
17:16
Friday
Open

Breakfast and dinner everyday in Russian Standard Time (+03:00)
17:16
Friday
Open

Breakfast and dinner everyday in E. Africa Standard Time (+03:00)
17:16
Friday
Open

Breakfast and dinner everyday in Volgograd Standard Time (+03:00)
17:16
Friday
Open

Breakfast and dinner everyday in Iran Standard Time (+03:30)
17:46
Friday
Open

Breakfast and dinner everyday in Arabian Standard Time (+04:00)
18:16
Friday
Open

Breakfast and dinner everyday in Astrakhan Standard Time (+04:00)
18:16
Friday
Open

Breakfast and dinner everyday in Azerbaijan Standard Time (+04:00)
18:16
Friday
Open

Breakfast and dinner everyday in Russia Time Zone 3 (+04:00)
18:16
Friday
Open

Breakfast and dinner everyday in Mauritius Standard Time (+04:00)
18:16
Friday
Open

Breakfast and dinner everyday in Saratov Standard Time (+04:00)
18:16
Friday
Open

Breakfast and dinner everyday in Georgian Standard Time (+04:00)
18:16
Friday
Open

Breakfast and dinner everyday in Caucasus Standard Time (+04:00)
18:16
Friday
Open

Breakfast and dinner everyday in Afghanistan Standard Time (+04:30)
18:46
Friday
Open

Lunch Fridays in UTC-02 (-02:00)
12:16
Friday
Open

Lunch Fridays in Mid-Atlantic Standard Time (-02:00)
12:16
Friday
Open

Lunch Fridays in Azores Standard Time (-01:00)
13:16
Friday
Open

Lunch Fridays in Cape Verde Standard Time (-01:00)
13:16
Friday
Open

Late night daily in China Standard Time (+08:00)
22:16
Friday
Open

Late night daily in North Asia East Standard Time (+08:00)
22:16
Friday
Open

Late night daily in Singapore Standard Time (+08:00)
22:16
Friday
Open

Late night daily in W. Australia Standard Time (+08:00)
22:16
Friday
Open

Late night daily in Taipei Standard Time (+08:00)
22:16
Friday
Open

Late night daily in Ulaanbaatar Standard Time (+08:00)
22:16
Friday
Open

Late night daily in Aus Central W. Standard Time (+08:45)
23:01
Friday
Open

Late night daily in Transbaikal Standard Time (+09:00)
23:16
Friday
Open

Late night daily in Tokyo Standard Time (+09:00)
23:16
Friday
Open

Late night daily in North Korea Standard Time (+09:00)
23:16
Friday
Open

Late night daily in Korea Standard Time (+09:00)
23:16
Friday
Open

Late night daily in Yakutsk Standard Time (+09:00)
23:16
Friday
Open

Late night daily in AUS Central Standard Time (+09:30)
23:46
Friday
Open


----------------------------------------------------------------------
-- Stored procedure to filter and retrieve currently open places
----------------------------------------------------------------------
  
CREATE PROCEDURE GetOpenPlaces
AS
    SELECT P.*
    FROM Place P
    JOIN Timezone TZ ON TZ.TimezoneId = P.TimezoneId
    CROSS APPLY (
        -- Intermediate local time calculations
        SELECT
            GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE TZ.Name AS LocalDateTime
    ) LT
    CROSS APPLY (
        -- More intermediate local time calculations
        SELECT
            DATENAME(weekday, LT.LocalDateTime) AS LocalWeekdayName,
            CAST(LT.LocalDateTime AS TIME) AS LocalTime
    ) DT
    JOIN DayOfTheWeek DOW ON DOW.DayName = DT.LocalWeekdayName
    WHERE EXISTS (
        SELECT *
        FROM StoreHour H
        WHERE H.PlaceId = P.PlaceId
        AND H.DayOfTheWeekId = DOW.DayOfTheWeekId
        AND H.OpenHour <= DT.LocalTime
        AND (H.CloseHour > DT.LocalTime OR H.CloseHour = '00:00')
    )
----------------------------------------------------------------------
-- Test the above stored procedure
----------------------------------------------------------------------
  
EXEC GetOpenPlaces


PlaceId
Name
TimeZoneId


8
Breakfast and dinner everyday in Pacific Standard Time (Mexico) (-08:00)
8

9
Breakfast and dinner everyday in UTC-08 (-08:00)
9

10
Breakfast and dinner everyday in Pacific Standard Time (-08:00)
10

11
Breakfast and dinner everyday in US Mountain Standard Time (-07:00)
11

12
Breakfast and dinner everyday in Mountain Standard Time (Mexico) (-07:00)
12

13
Breakfast and dinner everyday in Mountain Standard Time (-07:00)
13

14
Breakfast and dinner everyday in Yukon Standard Time (-07:00)
14

72
Breakfast and dinner everyday in Arabic Standard Time (+03:00)
72

73
Breakfast and dinner everyday in Turkey Standard Time (+03:00)
73

74
Breakfast and dinner everyday in Arab Standard Time (+03:00)
74

75
Breakfast and dinner everyday in Belarus Standard Time (+03:00)
75

76
Breakfast and dinner everyday in Russian Standard Time (+03:00)
76

77
Breakfast and dinner everyday in E. Africa Standard Time (+03:00)
77

78
Breakfast and dinner everyday in Volgograd Standard Time (+03:00)
78

79
Breakfast and dinner everyday in Iran Standard Time (+03:30)
79

80
Breakfast and dinner everyday in Arabian Standard Time (+04:00)
80

81
Breakfast and dinner everyday in Astrakhan Standard Time (+04:00)
81

82
Breakfast and dinner everyday in Azerbaijan Standard Time (+04:00)
82

83
Breakfast and dinner everyday in Russia Time Zone 3 (+04:00)
83

84
Breakfast and dinner everyday in Mauritius Standard Time (+04:00)
84

85
Breakfast and dinner everyday in Saratov Standard Time (+04:00)
85

86
Breakfast and dinner everyday in Georgian Standard Time (+04:00)
86

87
Breakfast and dinner everyday in Caucasus Standard Time (+04:00)
87

88
Breakfast and dinner everyday in Afghanistan Standard Time (+04:30)
88

748
Lunch Fridays in UTC-02 (-02:00)
43

749
Lunch Fridays in Mid-Atlantic Standard Time (-02:00)
44

750
Lunch Fridays in Azores Standard Time (-01:00)
45

751
Lunch Fridays in Cape Verde Standard Time (-01:00)
46

1234
Late night daily in China Standard Time (+08:00)
106

1235
Late night daily in North Asia East Standard Time (+08:00)
107

1236
Late night daily in Singapore Standard Time (+08:00)
108

1237
Late night daily in W. Australia Standard Time (+08:00)
109

1238
Late night daily in Taipei Standard Time (+08:00)
110

1239
Late night daily in Ulaanbaatar Standard Time (+08:00)
111

1240
Late night daily in Aus Central W. Standard Time (+08:45)
112

1241
Late night daily in Transbaikal Standard Time (+09:00)
113

1242
Late night daily in Tokyo Standard Time (+09:00)
114

1243
Late night daily in North Korea Standard Time (+09:00)
115

1244
Late night daily in Korea Standard Time (+09:00)
116

1245
Late night daily in Yakutsk Standard Time (+09:00)
117

1247
Late night daily in AUS Central Standard Time (+09:30)
119

Name	LocalTime	LocalWeekdayName	OpenOrClosed
Breakfast and dinner everyday in Pacific Standard Time (Mexico) (-08:00)	06:16	Friday	Open
Breakfast and dinner everyday in UTC-08 (-08:00)	06:16	Friday	Open
Breakfast and dinner everyday in Pacific Standard Time (-08:00)	06:16	Friday	Open
Breakfast and dinner everyday in US Mountain Standard Time (-07:00)	07:16	Friday	Open
Breakfast and dinner everyday in Mountain Standard Time (Mexico) (-07:00)	07:16	Friday	Open
Breakfast and dinner everyday in Mountain Standard Time (-07:00)	07:16	Friday	Open
Breakfast and dinner everyday in Yukon Standard Time (-07:00)	07:16	Friday	Open
Breakfast and dinner everyday in Arabic Standard Time (+03:00)	17:16	Friday	Open
Breakfast and dinner everyday in Turkey Standard Time (+03:00)	17:16	Friday	Open
Breakfast and dinner everyday in Arab Standard Time (+03:00)	17:16	Friday	Open
Breakfast and dinner everyday in Belarus Standard Time (+03:00)	17:16	Friday	Open
Breakfast and dinner everyday in Russian Standard Time (+03:00)	17:16	Friday	Open
Breakfast and dinner everyday in E. Africa Standard Time (+03:00)	17:16	Friday	Open
Breakfast and dinner everyday in Volgograd Standard Time (+03:00)	17:16	Friday	Open
Breakfast and dinner everyday in Iran Standard Time (+03:30)	17:46	Friday	Open
Breakfast and dinner everyday in Arabian Standard Time (+04:00)	18:16	Friday	Open
Breakfast and dinner everyday in Astrakhan Standard Time (+04:00)	18:16	Friday	Open
Breakfast and dinner everyday in Azerbaijan Standard Time (+04:00)	18:16	Friday	Open
Breakfast and dinner everyday in Russia Time Zone 3 (+04:00)	18:16	Friday	Open
Breakfast and dinner everyday in Mauritius Standard Time (+04:00)	18:16	Friday	Open
Breakfast and dinner everyday in Saratov Standard Time (+04:00)	18:16	Friday	Open
Breakfast and dinner everyday in Georgian Standard Time (+04:00)	18:16	Friday	Open
Breakfast and dinner everyday in Caucasus Standard Time (+04:00)	18:16	Friday	Open
Breakfast and dinner everyday in Afghanistan Standard Time (+04:30)	18:46	Friday	Open
Lunch Fridays in UTC-02 (-02:00)	12:16	Friday	Open
Lunch Fridays in Mid-Atlantic Standard Time (-02:00)	12:16	Friday	Open
Lunch Fridays in Azores Standard Time (-01:00)	13:16	Friday	Open
Lunch Fridays in Cape Verde Standard Time (-01:00)	13:16	Friday	Open
Late night daily in China Standard Time (+08:00)	22:16	Friday	Open
Late night daily in North Asia East Standard Time (+08:00)	22:16	Friday	Open
Late night daily in Singapore Standard Time (+08:00)	22:16	Friday	Open
Late night daily in W. Australia Standard Time (+08:00)	22:16	Friday	Open
Late night daily in Taipei Standard Time (+08:00)	22:16	Friday	Open
Late night daily in Ulaanbaatar Standard Time (+08:00)	22:16	Friday	Open
Late night daily in Aus Central W. Standard Time (+08:45)	23:01	Friday	Open
Late night daily in Transbaikal Standard Time (+09:00)	23:16	Friday	Open
Late night daily in Tokyo Standard Time (+09:00)	23:16	Friday	Open
Late night daily in North Korea Standard Time (+09:00)	23:16	Friday	Open
Late night daily in Korea Standard Time (+09:00)	23:16	Friday	Open
Late night daily in Yakutsk Standard Time (+09:00)	23:16	Friday	Open
Late night daily in AUS Central Standard Time (+09:30)	23:46	Friday	Open