SQL Server 2022

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

CREATE TABLE Timezone (
	TimezoneId INT IDENTITY(1,1) NOT NULL
	  CONSTRAINT PK_Timezone PRIMARY KEY,
	Name       NVARCHAR(100)     NOT NULL,
	CreatedDT  DATETIME          NULL
	  CONSTRAINT DFT_Timezone_CreatedDT DEFAULT(GETUTCDATE())
);

CREATE TABLE Place (
	PlaceId       INT IDENTITY(1,1) NOT NULL
	  CONSTRAINT PK_Place PRIMARY KEY,
	[Name]        NVARCHAR(100)     NOT NULL,
	Latitude      DOUBLE PRECISION  NOT NULL,
	Longitude     DOUBLE PRECISION  NOT NULL,
	IsOpen        BIT               NOT NULL,
    CanUpdateIsOpen BIT             NOT NULL,
    TimezoneId    INT               NOT NULL
	  CONSTRAINT FK_Place_TimezoneId FOREIGN KEY REFERENCES Timezone(TimezoneId)
);

CREATE TABLE PlaceAudit (
	PlaceId         INT               NOT NULL,
	[Name]          NVARCHAR(100)     NOT NULL,
	Latitude        DOUBLE PRECISION  NOT NULL,
	Longitude       DOUBLE PRECISION  NOT NULL,
	IsOpen          BIT               NOT NULL,
    CanUpdateIsOpen BIT               NULL
);

CREATE TABLE DayOfTheWeek (
	DayOfTheWeekId INT IDENTITY(1,1) NOT NULL
	  CONSTRAINT PK_DayOfTheWeek PRIMARY KEY,
	Name           NVARCHAR(100)     NOT NULL
);

CREATE TABLE StoreHour (
	StoreHourId    INT IDENTITY(1,1) NOT NULL
	  CONSTRAINT PK_StoreHour PRIMARY KEY,
	OpenHour       TIME              NOT NULL,
	CloseHour      TIME              NOT NULL,
	CreatedDT      DATETIME          NULL
	  CONSTRAINT DFT_StoreHour_CreatedDT DEFAULT(GETUTCDATE()),
	DayOfTheWeekId INT               NOT NULL
	  CONSTRAINT FK_StoreHour_DayOfTheWeekId FOREIGN KEY REFERENCES DayOfTheWeek(DayOfTheWeekId),
	PlaceId        INT               NOT NULL
	  CONSTRAINT FK_StoreHour_PlaceId FOREIGN KEY REFERENCES Place(PlaceId)
);

CREATE TABLE [Address] (
	AddressId INT IDENTITY(1,1) NOT NULL
	  CONSTRAINT PK_Address PRIMARY KEY,
	Name      NVARCHAR(100)     NOT NULL,
	City      NVARCHAR(100)     NOT NULL,
	CreatedDT DATETIME          NULL
	  CONSTRAINT DFT_Address_CreatedDT DEFAULT(GETUTCDATE()),
	PlaceId   INT               NOT NULL
	  CONSTRAINT FK_Address_PlaceId FOREIGN KEY REFERENCES Place(PlaceId)
);

-- Table to store Olson timezones and their equivalent Windows timezones
CREATE TABLE OlsonToWindows (
	OlsonTz varchar(300)  NOT NULL
	  CONSTRAINT PK_OlsonToWindows PRIMARY KEY,
	WindowsTz varchar(50) NOT NULL
);
------------------------------------------------------------
-- Seed data
------------------------------------------------------------

INSERT INTO DayOfTheWeek
SELECT DATENAME(WEEKDAY, DATEADD(DAY, N.N, '1900-01-01'))
FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) N(N);

-- This is the mapping between Olson timezones and their equivalent Windows timezones
DECLARE @xml xml = N'<?xml version="1.0" encoding="UTF-16" ?>
<!--
Copyright © 1991-2013 Unicode, Inc.
CLDR data files are interpreted according to the LDML specification (http://unicode.org/reports/tr35/)
For terms of use, see http://www.unicode.org/copyright.html
-->

<supplementalData>
	<version number="$Revision$"/>
	<windowsZones>
		<mapTimezones otherVersion="7e11800" typeVersion="2021a">

			<!-- (UTC-12:00) International Date Line West -->
			<mapZone other="Dateline Standard Time" territory="001" type="Etc/GMT+12"/>
			<mapZone other="Dateline Standard Time" territory="ZZ" type="Etc/GMT+12"/>

			<!-- (UTC-11:00) Coordinated Universal Time-11 -->
			<mapZone other="UTC-11" territory="001" type="Etc/GMT+11"/>
			<mapZone other="UTC-11" territory="AS" type="Pacific/Pago_Pago"/>
			<mapZone other="UTC-11" territory="NU" type="Pacific/Niue"/>
			<mapZone other="UTC-11" territory="UM" type="Pacific/Midway"/>
			<mapZone other="UTC-11" territory="ZZ" type="Etc/GMT+11"/>

			<!-- (UTC-10:00) Aleutian Islands -->
			<mapZone other="Aleutian Standard Time" territory="001" type="America/Adak"/>
			<mapZone other="Aleutian Standard Time" territory="US" type="America/Adak"/>

			<!-- (UTC-10:00) Hawaii -->
			<mapZone other="Hawaiian Standard Time" territory="001" type="Pacific/Honolulu"/>
			<mapZone other="Hawaiian Standard Time" territory="CK" type="Pacific/Rarotonga"/>
			<mapZone other="Hawaiian Standard Time" territory="PF" type="Pacific/Tahiti"/>
			<mapZone other="Hawaiian Standard Time" territory="UM" type="Pacific/Johnston"/>
			<mapZone other="Hawaiian Standard Time" territory="US" type="Pacific/Honolulu"/>
			<mapZone other="Hawaiian Standard Time" territory="ZZ" type="Etc/GMT+10"/>

			<!-- (UTC-09:30) Marquesas Islands -->
			<mapZone other="Marquesas Standard Time" territory="001" type="Pacific/Marquesas"/>
			<mapZone other="Marquesas Standard Time" territory="PF" type="Pacific/Marquesas"/>

			<!-- (UTC-09:00) Alaska -->
			<mapZone other="Alaskan Standard Time" territory="001" type="America/Anchorage"/>
			<mapZone other="Alaskan Standard Time" territory="US" type="America/Anchorage"/>
			<mapZone other="Alaskan Standard Time" territory="US" type="America/Juneau"/>
			<mapZone other="Alaskan Standard Time" territory="US" type="America/Metlakatla"/>
			<mapZone other="Alaskan Standard Time" territory="US" type="America/Nome"/>
			<mapZone other="Alaskan Standard Time" territory="US" type="America/Sitka"/>
			<mapZone other="Alaskan Standard Time" territory="US" type="America/Yakutat"/>

			<!-- (UTC-09:00) Coordinated Universal Time-09 -->
			<mapZone other="UTC-09" territory="001" type="Etc/GMT+9"/>
			<mapZone other="UTC-09" territory="PF" type="Pacific/Gambier"/>
			<mapZone other="UTC-09" territory="ZZ" type="Etc/GMT+9"/>

			<!-- (UTC-08:00) Baja California -->
			<mapZone other="Pacific Standard Time (Mexico)" territory="001" type="America/Tijuana"/>
			<mapZone other="Pacific Standard Time (Mexico)" territory="MX" type="America/Tijuana"/>
			<mapZone other="Pacific Standard Time (Mexico)" territory="MX" type="America/Santa_Isabel"/>

			<!-- (UTC-08:00) Coordinated Universal Time-08 -->
			<mapZone other="UTC-08" territory="001" type="Etc/GMT+8"/>
			<mapZone other="UTC-08" territory="PN" type="Pacific/Pitcairn"/>
			<mapZone other="UTC-08" territory="ZZ" type="Etc/GMT+8"/>

			<!-- (UTC-08:00) Pacific Time (US & Canada) -->
			<mapZone other="Pacific Standard Time" territory="001" type="America/Los_Angeles"/>
			<mapZone other="Pacific Standard Time" territory="CA" type="America/Vancouver"/>
			<mapZone other="Pacific Standard Time" territory="US" type="America/Los_Angeles"/>
			<mapZone other="Pacific Standard Time" territory="ZZ" type="PST8PDT"/>

			<!-- (UTC-07:00) Arizona -->
			<mapZone other="US Mountain Standard Time" territory="001" type="America/Phoenix"/>
			<mapZone other="US Mountain Standard Time" territory="CA" type="America/Creston"/>
			<mapZone other="US Mountain Standard Time" territory="CA" type="America/Dawson_Creek"/>
			<mapZone other="US Mountain Standard Time" territory="CA" type="America/Fort_Nelson"/>
			<mapZone other="US Mountain Standard Time" territory="MX" type="America/Hermosillo"/>
			<mapZone other="US Mountain Standard Time" territory="US" type="America/Phoenix"/>
			<mapZone other="US Mountain Standard Time" territory="ZZ" type="Etc/GMT+7"/>

			<!-- (UTC-07:00) Chihuahua, La Paz, Mazatlan -->
			<mapZone other="Mountain Standard Time (Mexico)" territory="001" type="America/Mazatlan"/>
			<mapZone other="Mountain Standard Time (Mexico)" territory="MX" type="America/Mazatlan"/>

			<!-- (UTC-07:00) Mountain Time (US & Canada) -->
			<mapZone other="Mountain Standard Time" territory="001" type="America/Denver"/>
			<mapZone other="Mountain Standard Time" territory="CA" type="America/Edmonton"/>
			<mapZone other="Mountain Standard Time" territory="CA" type="America/Cambridge_Bay"/>
			<mapZone other="Mountain Standard Time" territory="CA" type="America/Inuvik"/>
			<mapZone other="Mountain Standard Time" territory="CA" type="America/Yellowknife"/>
			<mapZone other="Mountain Standard Time" territory="MX" type="America/Ciudad_Juarez"/>
			<mapZone other="Mountain Standard Time" territory="US" type="America/Denver"/>
			<mapZone other="Mountain Standard Time" territory="US" type="America/Boise"/>
			<mapZone other="Mountain Standard Time" territory="ZZ" type="MST7MDT"/>

			<!-- (UTC-07:00) Yukon -->
			<mapZone other="Yukon Standard Time" territory="001" type="America/Whitehorse"/>
			<mapZone other="Yukon Standard Time" territory="CA" type="America/Whitehorse"/>
			<mapZone other="Yukon Standard Time" territory="CA" type="America/Dawson"/>

			<!-- (UTC-06:00) Central America -->
			<mapZone other="Central America Standard Time" territory="001" type="America/Guatemala"/>
			<mapZone other="Central America Standard Time" territory="BZ" type="America/Belize"/>
			<mapZone other="Central America Standard Time" territory="CR" type="America/Costa_Rica"/>
			<mapZone other="Central America Standard Time" territory="EC" type="Pacific/Galapagos"/>
			<mapZone other="Central America Standard Time" territory="GT" type="America/Guatemala"/>
			<mapZone other="Central America Standard Time" territory="HN" type="America/Tegucigalpa"/>
			<mapZone other="Central America Standard Time" territory="NI" type="America/Managua"/>
			<mapZone other="Central America Standard Time" territory="SV" type="America/El_Salvador"/>
			<mapZone other="Central America Standard Time" territory="ZZ" type="Etc/GMT+6"/>

			<!-- (UTC-06:00) Central Time (US & Canada) -->
			<mapZone other="Central Standard Time" territory="001" type="America/Chicago"/>
			<mapZone other="Central Standard Time" territory="CA" type="America/Winnipeg"/>
			<mapZone other="Central Standard Time" territory="CA" type="America/Rainy_River"/>
			<mapZone other="Central Standard Time" territory="CA" type="America/Rankin_Inlet"/>
			<mapZone other="Central Standard Time" territory="CA" type="America/Resolute"/>
			<mapZone other="Central Standard Time" territory="MX" type="America/Matamoros"/>
			<mapZone other="Central Standard Time" territory="MX" type="America/Ojinaga"/>
			<mapZone other="Central Standard Time" territory="US" type="America/Chicago"/>
			<mapZone other="Central Standard Time" territory="US" type="America/Indiana/Knox"/>
			<mapZone other="Central Standard Time" territory="US" type="America/Indiana/Tell_City"/>
			<mapZone other="Central Standard Time" territory="US" type="America/Menominee"/>
			<mapZone other="Central Standard Time" territory="US" type="America/North_Dakota/Beulah"/>
			<mapZone other="Central Standard Time" territory="US" type="America/North_Dakota/Center"/>
			<mapZone other="Central Standard Time" territory="US" type="America/North_Dakota/New_Salem"/>
			<mapZone other="Central Standard Time" territory="ZZ" type="CST6CDT"/>

			<!-- (UTC-06:00) Easter Island -->
			<mapZone other="Easter Island Standard Time" territory="001" type="Pacific/Easter"/>
			<mapZone other="Easter Island Standard Time" territory="CL" type="Pacific/Easter"/>

			<!-- (UTC-06:00) Guadalajara, Mexico City, Monterrey -->
			<mapZone other="Central Standard Time (Mexico)" territory="001" type="America/Mexico_City"/>
			<mapZone other="Central Standard Time (Mexico)" territory="MX" type="America/Mexico_City"/>
			<mapZone other="Central Standard Time (Mexico)" territory="MX" type="America/Bahia_Banderas"/>
			<mapZone other="Central Standard Time (Mexico)" territory="MX" type="America/Merida"/>
			<mapZone other="Central Standard Time (Mexico)" territory="MX" type="America/Monterrey"/>
			<mapZone other="Central Standard Time (Mexico)" territory="MX" type="America/Chihuahua"/>

			<!-- (UTC-06:00) Saskatchewan -->
			<mapZone other="Canada Central Standard Time" territory="001" type="America/Regina"/>
			<mapZone other="Canada Central Standard Time" territory="CA" type="America/Regina"/>
			<mapZone other="Canada Central Standard Time" territory="CA" type="America/Swift_Current"/>

			<!-- (UTC-05:00) Bogota, Lima, Quito, Rio Branco -->
			<mapZone other="SA Pacific Standard Time" territory="001" type="America/Bogota"/>
			<mapZone other="SA Pacific Standard Time" territory="BR" type="America/Rio_Branco"/>
			<mapZone other="SA Pacific Standard Time" territory="BR" type="America/Eirunepe"/>
			<mapZone other="SA Pacific Standard Time" territory="CA" type="America/Coral_Harbour"/>
			<mapZone other="SA Pacific Standard Time" territory="CO" type="America/Bogota"/>
			<mapZone other="SA Pacific Standard Time" territory="EC" type="America/Guayaquil"/>
			<mapZone other="SA Pacific Standard Time" territory="JM" type="America/Jamaica"/>
			<mapZone other="SA Pacific Standard Time" territory="KY" type="America/Cayman"/>
			<mapZone other="SA Pacific Standard Time" territory="PA" type="America/Panama"/>
			<mapZone other="SA Pacific Standard Time" territory="PE" type="America/Lima"/>
			<mapZone other="SA Pacific Standard Time" territory="ZZ" type="Etc/GMT+5"/>

			<!-- (UTC-05:00) Chetumal -->
			<mapZone other="Eastern Standard Time (Mexico)" territory="001" type="America/Cancun"/>
			<mapZone other="Eastern Standard Time (Mexico)" territory="MX" type="America/Cancun"/>

			<!-- (UTC-05:00) Eastern Time (US & Canada) -->
			<mapZone other="Eastern Standard Time" territory="001" type="America/New_York"/>
			<mapZone other="Eastern Standard Time" territory="BS" type="America/Nassau"/>
			<mapZone other="Eastern Standard Time" territory="CA" type="America/Toronto"/>
			<mapZone other="Eastern Standard Time" territory="CA" type="America/Iqaluit"/>
			<mapZone other="Eastern Standard Time" territory="CA" type="America/Montreal"/>
			<mapZone other="Eastern Standard Time" territory="CA" type="America/Nipigon"/>
			<mapZone other="Eastern Standard Time" territory="CA" type="America/Pangnirtung"/>
			<mapZone other="Eastern Standard Time" territory="CA" type="America/Thunder_Bay"/>
			<mapZone other="Eastern Standard Time" territory="US" type="America/New_York"/>
			<mapZone other="Eastern Standard Time" territory="US" type="America/Detroit"/>
			<mapZone other="Eastern Standard Time" territory="US" type="America/Indiana/Petersburg"/>
			<mapZone other="Eastern Standard Time" territory="US" type="America/Indiana/Vincennes"/>
			<mapZone other="Eastern Standard Time" territory="US" type="America/Indiana/Winamac"/>
			<mapZone other="Eastern Standard Time" territory="US" type="America/Kentucky/Monticello"/>
			<mapZone other="Eastern Standard Time" territory="US" type="America/Louisville"/>
			<mapZone other="Eastern Standard Time" territory="ZZ" type="EST5EDT"/>

			<!-- (UTC-05:00) Haiti -->
			<mapZone other="Haiti Standard Time" territory="001" type="America/Port-au-Prince"/>
			<mapZone other="Haiti Standard Time" territory="HT" type="America/Port-au-Prince"/>

			<!-- (UTC-05:00) Havana -->
			<mapZone other="Cuba Standard Time" territory="001" type="America/Havana"/>
			<mapZone other="Cuba Standard Time" territory="CU" type="America/Havana"/>

			<!-- (UTC-05:00) Indiana (East) -->
			<mapZone other="US Eastern Standard Time" territory="001" type="America/Indianapolis"/>
			<mapZone other="US Eastern Standard Time" territory="US" type="America/Indianapolis"/>
			<mapZone other="US Eastern Standard Time" territory="US" type="America/Indiana/Marengo"/>
			<mapZone other="US Eastern Standard Time" territory="US" type="America/Indiana/Vevay"/>

			<!-- (UTC-05:00) Turks and Caicos -->
			<mapZone other="Turks And Caicos Standard Time" territory="001" type="America/Grand_Turk"/>
			<mapZone other="Turks And Caicos Standard Time" territory="TC" type="America/Grand_Turk"/>

			<!-- (UTC-04:00) Asuncion -->
			<mapZone other="Paraguay Standard Time" territory="001" type="America/Asuncion"/>
			<mapZone other="Paraguay Standard Time" territory="PY" type="America/Asuncion"/>

			<!-- (UTC-04:00) Atlantic Time (Canada) -->
			<mapZone other="Atlantic Standard Time" territory="001" type="America/Halifax"/>
			<mapZone other="Atlantic Standard Time" territory="BM" type="Atlantic/Bermuda"/>
			<mapZone other="Atlantic Standard Time" territory="CA" type="America/Halifax"/>
			<mapZone other="Atlantic Standard Time" territory="CA" type="America/Glace_Bay"/>
			<mapZone other="Atlantic Standard Time" territory="CA" type="America/Goose_Bay"/>
			<mapZone other="Atlantic Standard Time" territory="CA" type="America/Moncton"/>
			<mapZone other="Atlantic Standard Time" territory="GL" type="America/Thule"/>

			<!-- (UTC-04:00) Caracas -->
			<mapZone other="Venezuela Standard Time" territory="001" type="America/Caracas"/>
			<mapZone other="Venezuela Standard Time" territory="VE" type="America/Caracas"/>

			<!-- (UTC-04:00) Cuiaba -->
			<mapZone other="Central Brazilian Standard Time" territory="001" type="America/Cuiaba"/>
			<mapZone other="Central Brazilian Standard Time" territory="BR" type="America/Cuiaba"/>
			<mapZone other="Central Brazilian Standard Time" territory="BR" type="America/Campo_Grande"/>

			<!-- (UTC-04:00) Georgetown, La Paz, Manaus, San Juan -->
			<mapZone other="SA Western Standard Time" territory="001" type="America/La_Paz"/>
			<mapZone other="SA Western Standard Time" territory="AG" type="America/Antigua"/>
			<mapZone other="SA Western Standard Time" territory="AI" type="America/Anguilla"/>
			<mapZone other="SA Western Standard Time" territory="AW" type="America/Aruba"/>
			<mapZone other="SA Western Standard Time" territory="BB" type="America/Barbados"/>
			<mapZone other="SA Western Standard Time" territory="BL" type="America/St_Barthelemy"/>
			<mapZone other="SA Western Standard Time" territory="BO" type="America/La_Paz"/>
			<mapZone other="SA Western Standard Time" territory="BQ" type="America/Kralendijk"/>
			<mapZone other="SA Western Standard Time" territory="BR" type="America/Manaus"/>
			<mapZone other="SA Western Standard Time" territory="BR" type="America/Boa_Vista"/>
			<mapZone other="SA Western Standard Time" territory="BR" type="America/Porto_Velho"/>
			<mapZone other="SA Western Standard Time" territory="CA" type="America/Blanc-Sablon"/>
			<mapZone other="SA Western Standard Time" territory="CW" type="America/Curacao"/>
			<mapZone other="SA Western Standard Time" territory="DM" type="America/Dominica"/>
			<mapZone other="SA Western Standard Time" territory="DO" type="America/Santo_Domingo"/>
			<mapZone other="SA Western Standard Time" territory="GD" type="America/Grenada"/>
			<mapZone other="SA Western Standard Time" territory="GP" type="America/Guadeloupe"/>
			<mapZone other="SA Western Standard Time" territory="GY" type="America/Guyana"/>
			<mapZone other="SA Western Standard Time" territory="KN" type="America/St_Kitts"/>
			<mapZone other="SA Western Standard Time" territory="LC" type="America/St_Lucia"/>
			<mapZone other="SA Western Standard Time" territory="MF" type="America/Marigot"/>
			<mapZone other="SA Western Standard Time" territory="MQ" type="America/Martinique"/>
			<mapZone other="SA Western Standard Time" territory="MS" type="America/Montserrat"/>
			<mapZone other="SA Western Standard Time" territory="PR" type="America/Puerto_Rico"/>
			<mapZone other="SA Western Standard Time" territory="SX" type="America/Lower_Princes"/>
			<mapZone other="SA Western Standard Time" territory="TT" type="America/Port_of_Spain"/>
			<mapZone other="SA Western Standard Time" territory="VC" type="America/St_Vincent"/>
			<mapZone other="SA Western Standard Time" territory="VG" type="America/Tortola"/>
			<mapZone other="SA Western Standard Time" territory="VI" type="America/St_Thomas"/>
			<mapZone other="SA Western Standard Time" territory="ZZ" type="Etc/GMT+4"/>

			<!-- (UTC-04:00) Santiago -->
			<mapZone other="Pacific SA Standard Time" territory="001" type="America/Santiago"/>
			<mapZone other="Pacific SA Standard Time" territory="CL" type="America/Santiago"/>

			<!-- (UTC-03:30) Newfoundland -->
			<mapZone other="Newfoundland Standard Time" territory="001" type="America/St_Johns"/>
			<mapZone other="Newfoundland Standard Time" territory="CA" type="America/St_Johns"/>

			<!-- (UTC-03:00) Araguaina -->
			<mapZone other="Tocantins Standard Time" territory="001" type="America/Araguaina"/>
			<mapZone other="Tocantins Standard Time" territory="BR" type="America/Araguaina"/>

			<!-- (UTC-03:00) Brasilia -->
			<mapZone other="E. South America Standard Time" territory="001" type="America/Sao_Paulo"/>
			<mapZone other="E. South America Standard Time" territory="BR" type="America/Sao_Paulo"/>

			<!-- (UTC-03:00) Cayenne, Fortaleza -->
			<mapZone other="SA Eastern Standard Time" territory="001" type="America/Cayenne"/>
			<mapZone other="SA Eastern Standard Time" territory="AQ" type="Antarctica/Rothera"/>
			<mapZone other="SA Eastern Standard Time" territory="AQ" type="Antarctica/Palmer"/>
			<mapZone other="SA Eastern Standard Time" territory="BR" type="America/Fortaleza"/>
			<mapZone other="SA Eastern Standard Time" territory="BR" type="America/Belem"/>
			<mapZone other="SA Eastern Standard Time" territory="BR" type="America/Maceio"/>
			<mapZone other="SA Eastern Standard Time" territory="BR" type="America/Recife"/>
			<mapZone other="SA Eastern Standard Time" territory="BR" type="America/Santarem"/>
			<mapZone other="SA Eastern Standard Time" territory="FK" type="Atlantic/Stanley"/>
			<mapZone other="SA Eastern Standard Time" territory="GF" type="America/Cayenne"/>
			<mapZone other="SA Eastern Standard Time" territory="SR" type="America/Paramaribo"/>
			<mapZone other="SA Eastern Standard Time" territory="ZZ" type="Etc/GMT+3"/>

			<!-- (UTC-03:00) City of Buenos Aires -->
			<mapZone other="Argentina Standard Time" territory="001" type="America/Buenos_Aires"/>
			<mapZone other="Argentina Standard Time" territory="AR" type="America/Buenos_Aires"/>
			<mapZone other="Argentina Standard Time" territory="AR" type="America/Argentina/La_Rioja"/>
			<mapZone other="Argentina Standard Time" territory="AR" type="America/Argentina/Rio_Gallegos"/>
			<mapZone other="Argentina Standard Time" territory="AR" type="America/Argentina/Salta"/>
			<mapZone other="Argentina Standard Time" territory="AR" type="America/Argentina/San_Juan"/>
			<mapZone other="Argentina Standard Time" territory="AR" type="America/Argentina/San_Luis"/>
			<mapZone other="Argentina Standard Time" territory="AR" type="America/Argentina/Tucuman"/>
			<mapZone other="Argentina Standard Time" territory="AR" type="America/Argentina/Ushuaia"/>
			<mapZone other="Argentina Standard Time" territory="AR" type="America/Catamarca"/>
			<mapZone other="Argentina Standard Time" territory="AR" type="America/Cordoba"/>
			<mapZone other="Argentina Standard Time" territory="AR" type="America/Jujuy"/>
			<mapZone other="Argentina Standard Time" territory="AR" type="America/Mendoza"/>

			<!-- (UTC-03:00) Greenland -->
			<mapZone other="Greenland Standard Time" territory="001" type="America/Godthab"/>
			<mapZone other="Greenland Standard Time" territory="GL" type="America/Godthab"/>

			<!-- (UTC-03:00) Montevideo -->
			<mapZone other="Montevideo Standard Time" territory="001" type="America/Montevideo"/>
			<mapZone other="Montevideo Standard Time" territory="UY" type="America/Montevideo"/>

			<!-- (UTC-03:00) Punta Arenas -->
			<mapZone other="Magallanes Standard Time" territory="001" type="America/Punta_Arenas"/>
			<mapZone other="Magallanes Standard Time" territory="CL" type="America/Punta_Arenas"/>

			<!-- (UTC-03:00) Saint Pierre and Miquelon -->
			<mapZone other="Saint Pierre Standard Time" territory="001" type="America/Miquelon"/>
			<mapZone other="Saint Pierre Standard Time" territory="PM" type="America/Miquelon"/>

			<!-- (UTC-03:00) Salvador -->
			<mapZone other="Bahia Standard Time" territory="001" type="America/Bahia"/>
			<mapZone other="Bahia Standard Time" territory="BR" type="America/Bahia"/>

			<!-- (UTC-02:00) Coordinated Universal Time-02 -->
			<mapZone other="UTC-02" territory="001" type="Etc/GMT+2"/>
			<mapZone other="UTC-02" territory="BR" type="America/Noronha"/>
			<mapZone other="UTC-02" territory="GS" type="Atlantic/South_Georgia"/>
			<mapZone other="UTC-02" territory="ZZ" type="Etc/GMT+2"/>

			<!-- (UTC-01:00) Azores -->
			<mapZone other="Azores Standard Time" territory="001" type="Atlantic/Azores"/>
			<mapZone other="Azores Standard Time" territory="GL" type="America/Scoresbysund"/>
			<mapZone other="Azores Standard Time" territory="PT" type="Atlantic/Azores"/>

			<!-- (UTC-01:00) Cabo Verde Is. -->
			<mapZone other="Cape Verde Standard Time" territory="001" type="Atlantic/Cape_Verde"/>
			<mapZone other="Cape Verde Standard Time" territory="CV" type="Atlantic/Cape_Verde"/>
			<mapZone other="Cape Verde Standard Time" territory="ZZ" type="Etc/GMT+1"/>

			<!-- (UTC) Coordinated Universal Time -->
			<mapZone other="UTC" territory="001" type="Etc/UTC"/>
			<mapZone other="UTC" territory="ZZ" type="Etc/UTC Etc/GMT"/>

			<!-- (UTC+00:00) Dublin, Edinburgh, Lisbon, London -->
			<mapZone other="GMT Standard Time" territory="001" type="Europe/London"/>
			<mapZone other="GMT Standard Time" territory="ES" type="Atlantic/Canary"/>
			<mapZone other="GMT Standard Time" territory="FO" type="Atlantic/Faeroe"/>
			<mapZone other="GMT Standard Time" territory="GB" type="Europe/London"/>
			<mapZone other="GMT Standard Time" territory="GG" type="Europe/Guernsey"/>
			<mapZone other="GMT Standard Time" territory="IE" type="Europe/Dublin"/>
			<mapZone other="GMT Standard Time" territory="IM" type="Europe/Isle_of_Man"/>
			<mapZone other="GMT Standard Time" territory="JE" type="Europe/Jersey"/>
			<mapZone other="GMT Standard Time" territory="PT" type="Europe/Lisbon"/>
			<mapZone other="GMT Standard Time" territory="PT" type="Atlantic/Madeira"/>

			<!-- (UTC+00:00) Monrovia, Reykjavik -->
			<mapZone other="Greenwich Standard Time" territory="001" type="Atlantic/Reykjavik"/>
			<mapZone other="Greenwich Standard Time" territory="BF" type="Africa/Ouagadougou"/>
			<mapZone other="Greenwich Standard Time" territory="CI" type="Africa/Abidjan"/>
			<mapZone other="Greenwich Standard Time" territory="GH" type="Africa/Accra"/>
			<mapZone other="Greenwich Standard Time" territory="GL" type="America/Danmarkshavn"/>
			<mapZone other="Greenwich Standard Time" territory="GM" type="Africa/Banjul"/>
			<mapZone other="Greenwich Standard Time" territory="GN" type="Africa/Conakry"/>
			<mapZone other="Greenwich Standard Time" territory="GW" type="Africa/Bissau"/>
			<mapZone other="Greenwich Standard Time" territory="IS" type="Atlantic/Reykjavik"/>
			<mapZone other="Greenwich Standard Time" territory="LR" type="Africa/Monrovia"/>
			<mapZone other="Greenwich Standard Time" territory="ML" type="Africa/Bamako"/>
			<mapZone other="Greenwich Standard Time" territory="MR" type="Africa/Nouakchott"/>
			<mapZone other="Greenwich Standard Time" territory="SH" type="Atlantic/St_Helena"/>
			<mapZone other="Greenwich Standard Time" territory="SL" type="Africa/Freetown"/>
			<mapZone other="Greenwich Standard Time" territory="SN" type="Africa/Dakar"/>
			<mapZone other="Greenwich Standard Time" territory="TG" type="Africa/Lome"/>

			<!-- (UTC+00:00) Sao Tome -->
			<mapZone other="Sao Tome Standard Time" territory="001" type="Africa/Sao_Tome"/>
			<mapZone other="Sao Tome Standard Time" territory="ST" type="Africa/Sao_Tome"/>

			<!-- (UTC+01:00) Casablanca -->
			<mapZone other="Morocco Standard Time" territory="001" type="Africa/Casablanca"/>
			<mapZone other="Morocco Standard Time" territory="EH" type="Africa/El_Aaiun"/>
			<mapZone other="Morocco Standard Time" territory="MA" type="Africa/Casablanca"/>

			<!-- (UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna -->
			<mapZone other="W. Europe Standard Time" territory="001" type="Europe/Berlin"/>
			<mapZone other="W. Europe Standard Time" territory="AD" type="Europe/Andorra"/>
			<mapZone other="W. Europe Standard Time" territory="AT" type="Europe/Vienna"/>
			<mapZone other="W. Europe Standard Time" territory="CH" type="Europe/Zurich"/>
			<mapZone other="W. Europe Standard Time" territory="DE" type="Europe/Berlin"/>
			<mapZone other="W. Europe Standard Time" territory="DE" type="Europe/Busingen"/>
			<mapZone other="W. Europe Standard Time" territory="GI" type="Europe/Gibraltar"/>
			<mapZone other="W. Europe Standard Time" territory="IT" type="Europe/Rome"/>
			<mapZone other="W. Europe Standard Time" territory="LI" type="Europe/Vaduz"/>
			<mapZone other="W. Europe Standard Time" territory="LU" type="Europe/Luxembourg"/>
			<mapZone other="W. Europe Standard Time" territory="MC" type="Europe/Monaco"/>
			<mapZone other="W. Europe Standard Time" territory="MT" type="Europe/Malta"/>
			<mapZone other="W. Europe Standard Time" territory="NL" type="Europe/Amsterdam"/>
			<mapZone other="W. Europe Standard Time" territory="NO" type="Europe/Oslo"/>
			<mapZone other="W. Europe Standard Time" territory="SE" type="Europe/Stockholm"/>
			<mapZone other="W. Europe Standard Time" territory="SJ" type="Arctic/Longyearbyen"/>
			<mapZone other="W. Europe Standard Time" territory="SM" type="Europe/San_Marino"/>
			<mapZone other="W. Europe Standard Time" territory="VA" type="Europe/Vatican"/>

			<!-- (UTC+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague -->
			<mapZone other="Central Europe Standard Time" territory="001" type="Europe/Budapest"/>
			<mapZone other="Central Europe Standard Time" territory="AL" type="Europe/Tirane"/>
			<mapZone other="Central Europe Standard Time" territory="CZ" type="Europe/Prague"/>
			<mapZone other="Central Europe Standard Time" territory="HU" type="Europe/Budapest"/>
			<mapZone other="Central Europe Standard Time" territory="ME" type="Europe/Podgorica"/>
			<mapZone other="Central Europe Standard Time" territory="RS" type="Europe/Belgrade"/>
			<mapZone other="Central Europe Standard Time" territory="SI" type="Europe/Ljubljana"/>
			<mapZone other="Central Europe Standard Time" territory="SK" type="Europe/Bratislava"/>

			<!-- (UTC+01:00) Brussels, Copenhagen, Madrid, Paris -->
			<mapZone other="Romance Standard Time" territory="001" type="Europe/Paris"/>
			<mapZone other="Romance Standard Time" territory="BE" type="Europe/Brussels"/>
			<mapZone other="Romance Standard Time" territory="DK" type="Europe/Copenhagen"/>
			<mapZone other="Romance Standard Time" territory="ES" type="Europe/Madrid"/>
			<mapZone other="Romance Standard Time" territory="ES" type="Africa/Ceuta"/>
			<mapZone other="Romance Standard Time" territory="FR" type="Europe/Paris"/>

			<!-- (UTC+01:00) Sarajevo, Skopje, Warsaw, Zagreb -->
			<mapZone other="Central European Standard Time" territory="001" type="Europe/Warsaw"/>
			<mapZone other="Central European Standard Time" territory="BA" type="Europe/Sarajevo"/>
			<mapZone other="Central European Standard Time" territory="HR" type="Europe/Zagreb"/>
			<mapZone other="Central European Standard Time" territory="MK" type="Europe/Skopje"/>
			<mapZone other="Central European Standard Time" territory="PL" type="Europe/Warsaw"/>

			<!-- (UTC+01:00) West Central Africa -->
			<mapZone other="W. Central Africa Standard Time" territory="001" type="Africa/Lagos"/>
			<mapZone other="W. Central Africa Standard Time" territory="AO" type="Africa/Luanda"/>
			<mapZone other="W. Central Africa Standard Time" territory="BJ" type="Africa/Porto-Novo"/>
			<mapZone other="W. Central Africa Standard Time" territory="CD" type="Africa/Kinshasa"/>
			<mapZone other="W. Central Africa Standard Time" territory="CF" type="Africa/Bangui"/>
			<mapZone other="W. Central Africa Standard Time" territory="CG" type="Africa/Brazzaville"/>
			<mapZone other="W. Central Africa Standard Time" territory="CM" type="Africa/Douala"/>
			<mapZone other="W. Central Africa Standard Time" territory="DZ" type="Africa/Algiers"/>
			<mapZone other="W. Central Africa Standard Time" territory="GA" type="Africa/Libreville"/>
			<mapZone other="W. Central Africa Standard Time" territory="GQ" type="Africa/Malabo"/>
			<mapZone other="W. Central Africa Standard Time" territory="NE" type="Africa/Niamey"/>
			<mapZone other="W. Central Africa Standard Time" territory="NG" type="Africa/Lagos"/>
			<mapZone other="W. Central Africa Standard Time" territory="TD" type="Africa/Ndjamena"/>
			<mapZone other="W. Central Africa Standard Time" territory="TN" type="Africa/Tunis"/>
			<mapZone other="W. Central Africa Standard Time" territory="ZZ" type="Etc/GMT-1"/>

			<!-- (UTC+02:00) Amman -->
			<mapZone other="Jordan Standard Time" territory="001" type="Asia/Amman"/>
			<mapZone other="Jordan Standard Time" territory="JO" type="Asia/Amman"/>

			<!-- (UTC+02:00) Athens, Bucharest -->
			<mapZone other="GTB Standard Time" territory="001" type="Europe/Bucharest"/>
			<mapZone other="GTB Standard Time" territory="CY" type="Asia/Nicosia"/>
			<mapZone other="GTB Standard Time" territory="CY" type="Asia/Famagusta"/>
			<mapZone other="GTB Standard Time" territory="GR" type="Europe/Athens"/>
			<mapZone other="GTB Standard Time" territory="RO" type="Europe/Bucharest"/>

			<!-- (UTC+02:00) Beirut -->
			<mapZone other="Middle East Standard Time" territory="001" type="Asia/Beirut"/>
			<mapZone other="Middle East Standard Time" territory="LB" type="Asia/Beirut"/>

			<!-- (UTC+02:00) Cairo -->
			<mapZone other="Egypt Standard Time" territory="001" type="Africa/Cairo"/>
			<mapZone other="Egypt Standard Time" territory="EG" type="Africa/Cairo"/>

			<!-- (UTC+02:00) Chisinau -->
			<mapZone other="E. Europe Standard Time" territory="001" type="Europe/Chisinau"/>
			<mapZone other="E. Europe Standard Time" territory="MD" type="Europe/Chisinau"/>

			<!-- (UTC+02:00) Damascus -->
			<mapZone other="Syria Standard Time" territory="001" type="Asia/Damascus"/>
			<mapZone other="Syria Standard Time" territory="SY" type="Asia/Damascus"/>

			<!-- (UTC+02:00) Gaza, Hebron -->
			<mapZone other="West Bank Standard Time" territory="001" type="Asia/Hebron"/>
			<mapZone other="West Bank Standard Time" territory="PS" type="Asia/Hebron"/>
			<mapZone other="West Bank Standard Time" territory="PS" type="Asia/Gaza"/>

			<!-- (UTC+02:00) Harare, Pretoria -->
			<mapZone other="South Africa Standard Time" territory="001" type="Africa/Johannesburg"/>
			<mapZone other="South Africa Standard Time" territory="BI" type="Africa/Bujumbura"/>
			<mapZone other="South Africa Standard Time" territory="BW" type="Africa/Gaborone"/>
			<mapZone other="South Africa Standard Time" territory="CD" type="Africa/Lubumbashi"/>
			<mapZone other="South Africa Standard Time" territory="LS" type="Africa/Maseru"/>
			<mapZone other="South Africa Standard Time" territory="MW" type="Africa/Blantyre"/>
			<mapZone other="South Africa Standard Time" territory="MZ" type="Africa/Maputo"/>
			<mapZone other="South Africa Standard Time" territory="RW" type="Africa/Kigali"/>
			<mapZone other="South Africa Standard Time" territory="SZ" type="Africa/Mbabane"/>
			<mapZone other="South Africa Standard Time" territory="ZA" type="Africa/Johannesburg"/>
			<mapZone other="South Africa Standard Time" territory="ZM" type="Africa/Lusaka"/>
			<mapZone other="South Africa Standard Time" territory="ZW" type="Africa/Harare"/>
			<mapZone other="South Africa Standard Time" territory="ZZ" type="Etc/GMT-2"/>

			<!-- (UTC+02:00) Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius -->
			<mapZone other="FLE Standard Time" territory="001" type="Europe/Kiev"/>
			<mapZone other="FLE Standard Time" territory="AX" type="Europe/Mariehamn"/>
			<mapZone other="FLE Standard Time" territory="BG" type="Europe/Sofia"/>
			<mapZone other="FLE Standard Time" territory="EE" type="Europe/Tallinn"/>
			<mapZone other="FLE Standard Time" territory="FI" type="Europe/Helsinki"/>
			<mapZone other="FLE Standard Time" territory="LT" type="Europe/Vilnius"/>
			<mapZone other="FLE Standard Time" territory="LV" type="Europe/Riga"/>
			<mapZone other="FLE Standard Time" territory="UA" type="Europe/Kiev"/>
			<mapZone other="FLE Standard Time" territory="UA" type="Europe/Uzhgorod"/>
			<mapZone other="FLE Standard Time" territory="UA" type="Europe/Zaporozhye"/>

			<!-- (UTC+02:00) Jerusalem -->
			<mapZone other="Israel Standard Time" territory="001" type="Asia/Jerusalem"/>
			<mapZone other="Israel Standard Time" territory="IL" type="Asia/Jerusalem"/>

			<!-- (UTC+02:00) Juba -->
			<mapZone other="South Sudan Standard Time" territory="001" type="Africa/Juba"/>
			<mapZone other="South Sudan Standard Time" territory="SS" type="Africa/Juba"/>

			<!-- (UTC+02:00) Kaliningrad -->
			<mapZone other="Kaliningrad Standard Time" territory="001" type="Europe/Kaliningrad"/>
			<mapZone other="Kaliningrad Standard Time" territory="RU" type="Europe/Kaliningrad"/>

			<!-- (UTC+02:00) Khartoum -->
			<mapZone other="Sudan Standard Time" territory="001" type="Africa/Khartoum"/>
			<mapZone other="Sudan Standard Time" territory="SD" type="Africa/Khartoum"/>

			<!-- (UTC+02:00) Tripoli -->
			<mapZone other="Libya Standard Time" territory="001" type="Africa/Tripoli"/>
			<mapZone other="Libya Standard Time" territory="LY" type="Africa/Tripoli"/>

			<!-- (UTC+02:00) Windhoek -->
			<mapZone other="Namibia Standard Time" territory="001" type="Africa/Windhoek"/>
			<mapZone other="Namibia Standard Time" territory="NA" type="Africa/Windhoek"/>

			<!-- (UTC+03:00) Baghdad -->
			<mapZone other="Arabic Standard Time" territory="001" type="Asia/Baghdad"/>
			<mapZone other="Arabic Standard Time" territory="IQ" type="Asia/Baghdad"/>

			<!-- (UTC+03:00) Istanbul -->
			<mapZone other="Turkey Standard Time" territory="001" type="Europe/Istanbul"/>
			<mapZone other="Turkey Standard Time" territory="TR" type="Europe/Istanbul"/>

			<!-- (UTC+03:00) Kuwait, Riyadh -->
			<mapZone other="Arab Standard Time" territory="001" type="Asia/Riyadh"/>
			<mapZone other="Arab Standard Time" territory="BH" type="Asia/Bahrain"/>
			<mapZone other="Arab Standard Time" territory="KW" type="Asia/Kuwait"/>
			<mapZone other="Arab Standard Time" territory="QA" type="Asia/Qatar"/>
			<mapZone other="Arab Standard Time" territory="SA" type="Asia/Riyadh"/>
			<mapZone other="Arab Standard Time" territory="YE" type="Asia/Aden"/>

			<!-- (UTC+03:00) Minsk -->
			<mapZone other="Belarus Standard Time" territory="001" type="Europe/Minsk"/>
			<mapZone other="Belarus Standard Time" territory="BY" type="Europe/Minsk"/>

			<!-- (UTC+03:00) Moscow, St. Petersburg -->
			<mapZone other="Russian Standard Time" territory="001" type="Europe/Moscow"/>
			<mapZone other="Russian Standard Time" territory="RU" type="Europe/Moscow"/>
			<mapZone other="Russian Standard Time" territory="RU" type="Europe/Kirov"/>
			<mapZone other="Russian Standard Time" territory="UA" type="Europe/Simferopol"/>

			<!-- (UTC+03:00) Nairobi -->
			<mapZone other="E. Africa Standard Time" territory="001" type="Africa/Nairobi"/>
			<mapZone other="E. Africa Standard Time" territory="AQ" type="Antarctica/Syowa"/>
			<mapZone other="E. Africa Standard Time" territory="DJ" type="Africa/Djibouti"/>
			<mapZone other="E. Africa Standard Time" territory="ER" type="Africa/Asmera"/>
			<mapZone other="E. Africa Standard Time" territory="ET" type="Africa/Addis_Ababa"/>
			<mapZone other="E. Africa Standard Time" territory="KE" type="Africa/Nairobi"/>
			<mapZone other="E. Africa Standard Time" territory="KM" type="Indian/Comoro"/>
			<mapZone other="E. Africa Standard Time" territory="MG" type="Indian/Antananarivo"/>
			<mapZone other="E. Africa Standard Time" territory="SO" type="Africa/Mogadishu"/>
			<mapZone other="E. Africa Standard Time" territory="TZ" type="Africa/Dar_es_Salaam"/>
			<mapZone other="E. Africa Standard Time" territory="UG" type="Africa/Kampala"/>
			<mapZone other="E. Africa Standard Time" territory="YT" type="Indian/Mayotte"/>
			<mapZone other="E. Africa Standard Time" territory="ZZ" type="Etc/GMT-3"/>

			<!-- (UTC+03:30) Tehran -->
			<mapZone other="Iran Standard Time" territory="001" type="Asia/Tehran"/>
			<mapZone other="Iran Standard Time" territory="IR" type="Asia/Tehran"/>

			<!-- (UTC+04:00) Abu Dhabi, Muscat -->
			<mapZone other="Arabian Standard Time" territory="001" type="Asia/Dubai"/>
			<mapZone other="Arabian Standard Time" territory="AE" type="Asia/Dubai"/>
			<mapZone other="Arabian Standard Time" territory="OM" type="Asia/Muscat"/>
			<mapZone other="Arabian Standard Time" territory="ZZ" type="Etc/GMT-4"/>

			<!-- (UTC+04:00) Astrakhan, Ulyanovsk -->
			<mapZone other="Astrakhan Standard Time" territory="001" type="Europe/Astrakhan"/>
			<mapZone other="Astrakhan Standard Time" territory="RU" type="Europe/Astrakhan"/>
			<mapZone other="Astrakhan Standard Time" territory="RU" type="Europe/Ulyanovsk"/>

			<!-- (UTC+04:00) Baku -->
			<mapZone other="Azerbaijan Standard Time" territory="001" type="Asia/Baku"/>
			<mapZone other="Azerbaijan Standard Time" territory="AZ" type="Asia/Baku"/>

			<!-- (UTC+04:00) Izhevsk, Samara -->
			<mapZone other="Russia Time Zone 3" territory="001" type="Europe/Samara"/>
			<mapZone other="Russia Time Zone 3" territory="RU" type="Europe/Samara"/>

			<!-- (UTC+04:00) Port Louis -->
			<mapZone other="Mauritius Standard Time" territory="001" type="Indian/Mauritius"/>
			<mapZone other="Mauritius Standard Time" territory="MU" type="Indian/Mauritius"/>
			<mapZone other="Mauritius Standard Time" territory="RE" type="Indian/Reunion"/>
			<mapZone other="Mauritius Standard Time" territory="SC" type="Indian/Mahe"/>

			<!-- (UTC+04:00) Saratov -->
			<mapZone other="Saratov Standard Time" territory="001" type="Europe/Saratov"/>
			<mapZone other="Saratov Standard Time" territory="RU" type="Europe/Saratov"/>

			<!-- (UTC+04:00) Tbilisi -->
			<mapZone other="Georgian Standard Time" territory="001" type="Asia/Tbilisi"/>
			<mapZone other="Georgian Standard Time" territory="GE" type="Asia/Tbilisi"/>

			<!-- (UTC+04:00) Volgograd -->
			<mapZone other="Volgograd Standard Time" territory="001" type="Europe/Volgograd"/>
			<mapZone other="Volgograd Standard Time" territory="RU" type="Europe/Volgograd"/>

			<!-- (UTC+04:00) Yerevan -->
			<mapZone other="Caucasus Standard Time" territory="001" type="Asia/Yerevan"/>
			<mapZone other="Caucasus Standard Time" territory="AM" type="Asia/Yerevan"/>

			<!-- (UTC+04:30) Kabul -->
			<mapZone other="Afghanistan Standard Time" territory="001" type="Asia/Kabul"/>
			<mapZone other="Afghanistan Standard Time" territory="AF" type="Asia/Kabul"/>

			<!-- (UTC+05:00) Ashgabat, Tashkent -->
			<mapZone other="West Asia Standard Time" territory="001" type="Asia/Tashkent"/>
			<mapZone other="West Asia Standard Time" territory="AQ" type="Antarctica/Mawson"/>
			<mapZone other="West Asia Standard Time" territory="KZ" type="Asia/Oral"/>
			<mapZone other="West Asia Standard Time" territory="KZ" type="Asia/Aqtau"/>
			<mapZone other="West Asia Standard Time" territory="KZ" type="Asia/Aqtobe"/>
			<mapZone other="West Asia Standard Time" territory="KZ" type="Asia/Atyrau"/>
			<mapZone other="West Asia Standard Time" territory="MV" type="Indian/Maldives"/>
			<mapZone other="West Asia Standard Time" territory="TF" type="Indian/Kerguelen"/>
			<mapZone other="West Asia Standard Time" territory="TJ" type="Asia/Dushanbe"/>
			<mapZone other="West Asia Standard Time" territory="TM" type="Asia/Ashgabat"/>
			<mapZone other="West Asia Standard Time" territory="UZ" type="Asia/Tashkent"/>
			<mapZone other="West Asia Standard Time" territory="UZ" type="Asia/Samarkand"/>
			<mapZone other="West Asia Standard Time" territory="ZZ" type="Etc/GMT-5"/>

			<!-- (UTC+05:00) Ekaterinburg -->
			<mapZone other="Ekaterinburg Standard Time" territory="001" type="Asia/Yekaterinburg"/>
			<mapZone other="Ekaterinburg Standard Time" territory="RU" type="Asia/Yekaterinburg"/>

			<!-- (UTC+05:00) Islamabad, Karachi -->
			<mapZone other="Pakistan Standard Time" territory="001" type="Asia/Karachi"/>
			<mapZone other="Pakistan Standard Time" territory="PK" type="Asia/Karachi"/>

			<!-- (UTC+05:00) Qyzylorda -->
			<mapZone other="Qyzylorda Standard Time" territory="001" type="Asia/Qyzylorda"/>
			<mapZone other="Qyzylorda Standard Time" territory="KZ" type="Asia/Qyzylorda"/>

			<!-- (UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi -->
			<mapZone other="India Standard Time" territory="001" type="Asia/Calcutta"/>
			<mapZone other="India Standard Time" territory="IN" type="Asia/Calcutta"/>

			<!-- (UTC+05:30) Sri Jayawardenepura -->
			<mapZone other="Sri Lanka Standard Time" territory="001" type="Asia/Colombo"/>
			<mapZone other="Sri Lanka Standard Time" territory="LK" type="Asia/Colombo"/>

			<!-- (UTC+05:45) Kathmandu -->
			<mapZone other="Nepal Standard Time" territory="001" type="Asia/Katmandu"/>
			<mapZone other="Nepal Standard Time" territory="NP" type="Asia/Katmandu"/>

			<!-- (UTC+06:00) Astana -->
			<mapZone other="Central Asia Standard Time" territory="001" type="Asia/Almaty"/>
			<mapZone other="Central Asia Standard Time" territory="AQ" type="Antarctica/Vostok"/>
			<mapZone other="Central Asia Standard Time" territory="CN" type="Asia/Urumqi"/>
			<mapZone other="Central Asia Standard Time" territory="IO" type="Indian/Chagos"/>
			<mapZone other="Central Asia Standard Time" territory="KG" type="Asia/Bishkek"/>
			<mapZone other="Central Asia Standard Time" territory="KZ" type="Asia/Almaty"/>
			<mapZone other="Central Asia Standard Time" territory="KZ" type="Asia/Qostanay"/>
			<mapZone other="Central Asia Standard Time" territory="ZZ" type="Etc/GMT-6"/>

			<!-- (UTC+06:00) Dhaka -->
			<mapZone other="Bangladesh Standard Time" territory="001" type="Asia/Dhaka"/>
			<mapZone other="Bangladesh Standard Time" territory="BD" type="Asia/Dhaka"/>
			<mapZone other="Bangladesh Standard Time" territory="BT" type="Asia/Thimphu"/>

			<!-- (UTC+06:00) Omsk -->
			<mapZone other="Omsk Standard Time" territory="001" type="Asia/Omsk"/>
			<mapZone other="Omsk Standard Time" territory="RU" type="Asia/Omsk"/>

			<!-- (UTC+06:30) Yangon (Rangoon) -->
			<mapZone other="Myanmar Standard Time" territory="001" type="Asia/Rangoon"/>
			<mapZone other="Myanmar Standard Time" territory="CC" type="Indian/Cocos"/>
			<mapZone other="Myanmar Standard Time" territory="MM" type="Asia/Rangoon"/>

			<!-- (UTC+07:00) Bangkok, Hanoi, Jakarta -->
			<mapZone other="SE Asia Standard Time" territory="001" type="Asia/Bangkok"/>
			<mapZone other="SE Asia Standard Time" territory="AQ" type="Antarctica/Davis"/>
			<mapZone other="SE Asia Standard Time" territory="CX" type="Indian/Christmas"/>
			<mapZone other="SE Asia Standard Time" territory="ID" type="Asia/Jakarta"/>
			<mapZone other="SE Asia Standard Time" territory="ID" type="Asia/Pontianak"/>
			<mapZone other="SE Asia Standard Time" territory="KH" type="Asia/Phnom_Penh"/>
			<mapZone other="SE Asia Standard Time" territory="LA" type="Asia/Vientiane"/>
			<mapZone other="SE Asia Standard Time" territory="TH" type="Asia/Bangkok"/>
			<mapZone other="SE Asia Standard Time" territory="VN" type="Asia/Saigon"/>
			<mapZone other="SE Asia Standard Time" territory="ZZ" type="Etc/GMT-7"/>

			<!-- (UTC+07:00) Barnaul, Gorno-Altaysk -->
			<mapZone other="Altai Standard Time" territory="001" type="Asia/Barnaul"/>
			<mapZone other="Altai Standard Time" territory="RU" type="Asia/Barnaul"/>

			<!-- (UTC+07:00) Hovd -->
			<mapZone other="W. Mongolia Standard Time" territory="001" type="Asia/Hovd"/>
			<mapZone other="W. Mongolia Standard Time" territory="MN" type="Asia/Hovd"/>

			<!-- (UTC+07:00) Krasnoyarsk -->
			<mapZone other="North Asia Standard Time" territory="001" type="Asia/Krasnoyarsk"/>
			<mapZone other="North Asia Standard Time" territory="RU" type="Asia/Krasnoyarsk"/>
			<mapZone other="North Asia Standard Time" territory="RU" type="Asia/Novokuznetsk"/>

			<!-- (UTC+07:00) Novosibirsk -->
			<mapZone other="N. Central Asia Standard Time" territory="001" type="Asia/Novosibirsk"/>
			<mapZone other="N. Central Asia Standard Time" territory="RU" type="Asia/Novosibirsk"/>

			<!-- (UTC+07:00) Tomsk -->
			<mapZone other="Tomsk Standard Time" territory="001" type="Asia/Tomsk"/>
			<mapZone other="Tomsk Standard Time" territory="RU" type="Asia/Tomsk"/>

			<!-- (UTC+08:00) Beijing, Chongqing, Hong Kong, Urumqi -->
			<mapZone other="China Standard Time" territory="001" type="Asia/Shanghai"/>
			<mapZone other="China Standard Time" territory="CN" type="Asia/Shanghai"/>
			<mapZone other="China Standard Time" territory="HK" type="Asia/Hong_Kong"/>
			<mapZone other="China Standard Time" territory="MO" type="Asia/Macau"/>

			<!-- (UTC+08:00) Irkutsk -->
			<mapZone other="North Asia East Standard Time" territory="001" type="Asia/Irkutsk"/>
			<mapZone other="North Asia East Standard Time" territory="RU" type="Asia/Irkutsk"/>

			<!-- (UTC+08:00) Kuala Lumpur, Singapore -->
			<mapZone other="Singapore Standard Time" territory="001" type="Asia/Singapore"/>
			<mapZone other="Singapore Standard Time" territory="BN" type="Asia/Brunei"/>
			<mapZone other="Singapore Standard Time" territory="ID" type="Asia/Makassar"/>
			<mapZone other="Singapore Standard Time" territory="MY" type="Asia/Kuala_Lumpur"/>
			<mapZone other="Singapore Standard Time" territory="MY" type="Asia/Kuching"/>
			<mapZone other="Singapore Standard Time" territory="PH" type="Asia/Manila"/>
			<mapZone other="Singapore Standard Time" territory="SG" type="Asia/Singapore"/>
			<mapZone other="Singapore Standard Time" territory="ZZ" type="Etc/GMT-8"/>

			<!-- (UTC+08:00) Perth -->
			<mapZone other="W. Australia Standard Time" territory="001" type="Australia/Perth"/>
			<mapZone other="W. Australia Standard Time" territory="AU" type="Australia/Perth"/>

			<!-- (UTC+08:00) Taipei -->
			<mapZone other="Taipei Standard Time" territory="001" type="Asia/Taipei"/>
			<mapZone other="Taipei Standard Time" territory="TW" type="Asia/Taipei"/>

			<!-- (UTC+08:00) Ulaanbaatar -->
			<mapZone other="Ulaanbaatar Standard Time" territory="001" type="Asia/Ulaanbaatar"/>
			<mapZone other="Ulaanbaatar Standard Time" territory="MN" type="Asia/Ulaanbaatar"/>
			<mapZone other="Ulaanbaatar Standard Time" territory="MN" type="Asia/Choibalsan"/>

			<!-- (UTC+08:45) Eucla -->
			<mapZone other="Aus Central W. Standard Time" territory="001" type="Australia/Eucla"/>
			<mapZone other="Aus Central W. Standard Time" territory="AU" type="Australia/Eucla"/>

			<!-- (UTC+09:00) Chita -->
			<mapZone other="Transbaikal Standard Time" territory="001" type="Asia/Chita"/>
			<mapZone other="Transbaikal Standard Time" territory="RU" type="Asia/Chita"/>

			<!-- (UTC+09:00) Osaka, Sapporo, Tokyo -->
			<mapZone other="Tokyo Standard Time" territory="001" type="Asia/Tokyo"/>
			<mapZone other="Tokyo Standard Time" territory="ID" type="Asia/Jayapura"/>
			<mapZone other="Tokyo Standard Time" territory="JP" type="Asia/Tokyo"/>
			<mapZone other="Tokyo Standard Time" territory="PW" type="Pacific/Palau"/>
			<mapZone other="Tokyo Standard Time" territory="TL" type="Asia/Dili"/>
			<mapZone other="Tokyo Standard Time" territory="ZZ" type="Etc/GMT-9"/>

			<!-- (UTC+09:00) Pyongyang -->
			<mapZone other="North Korea Standard Time" territory="001" type="Asia/Pyongyang"/>
			<mapZone other="North Korea Standard Time" territory="KP" type="Asia/Pyongyang"/>

			<!-- (UTC+09:00) Seoul -->
			<mapZone other="Korea Standard Time" territory="001" type="Asia/Seoul"/>
			<mapZone other="Korea Standard Time" territory="KR" type="Asia/Seoul"/>

			<!-- (UTC+09:00) Yakutsk -->
			<mapZone other="Yakutsk Standard Time" territory="001" type="Asia/Yakutsk"/>
			<mapZone other="Yakutsk Standard Time" territory="RU" type="Asia/Yakutsk"/>
			<mapZone other="Yakutsk Standard Time" territory="RU" type="Asia/Khandyga"/>

			<!-- (UTC+09:30) Adelaide -->
			<mapZone other="Cen. Australia Standard Time" territory="001" type="Australia/Adelaide"/>
			<mapZone other="Cen. Australia Standard Time" territory="AU" type="Australia/Adelaide"/>
			<mapZone other="Cen. Australia Standard Time" territory="AU" type="Australia/Broken_Hill"/>

			<!-- (UTC+09:30) Darwin -->
			<mapZone other="AUS Central Standard Time" territory="001" type="Australia/Darwin"/>
			<mapZone other="AUS Central Standard Time" territory="AU" type="Australia/Darwin"/>

			<!-- (UTC+10:00) Brisbane -->
			<mapZone other="E. Australia Standard Time" territory="001" type="Australia/Brisbane"/>
			<mapZone other="E. Australia Standard Time" territory="AU" type="Australia/Brisbane"/>
			<mapZone other="E. Australia Standard Time" territory="AU" type="Australia/Lindeman"/>

			<!-- (UTC+10:00) Canberra, Melbourne, Sydney -->
			<mapZone other="AUS Eastern Standard Time" territory="001" type="Australia/Sydney"/>
			<mapZone other="AUS Eastern Standard Time" territory="AU" type="Australia/Sydney"/>
			<mapZone other="AUS Eastern Standard Time" territory="AU" type="Australia/Melbourne"/>

			<!-- (UTC+10:00) Guam, Port Moresby -->
			<mapZone other="West Pacific Standard Time" territory="001" type="Pacific/Port_Moresby"/>
			<mapZone other="West Pacific Standard Time" territory="AQ" type="Antarctica/DumontDUrville"/>
			<mapZone other="West Pacific Standard Time" territory="FM" type="Pacific/Truk"/>
			<mapZone other="West Pacific Standard Time" territory="GU" type="Pacific/Guam"/>
			<mapZone other="West Pacific Standard Time" territory="MP" type="Pacific/Saipan"/>
			<mapZone other="West Pacific Standard Time" territory="PG" type="Pacific/Port_Moresby"/>
			<mapZone other="West Pacific Standard Time" territory="ZZ" type="Etc/GMT-10"/>

			<!-- (UTC+10:00) Hobart -->
			<mapZone other="Tasmania Standard Time" territory="001" type="Australia/Hobart"/>
			<mapZone other="Tasmania Standard Time" territory="AU" type="Australia/Hobart"/>
			<mapZone other="Tasmania Standard Time" territory="AU" type="Australia/Currie"/>
			<mapZone other="Tasmania Standard Time" territory="AU" type="Antarctica/Macquarie"/>

			<!-- (UTC+10:00) Vladivostok -->
			<mapZone other="Vladivostok Standard Time" territory="001" type="Asia/Vladivostok"/>
			<mapZone other="Vladivostok Standard Time" territory="RU" type="Asia/Vladivostok"/>
			<mapZone other="Vladivostok Standard Time" territory="RU" type="Asia/Ust-Nera"/>

			<!-- (UTC+10:30) Lord Howe Island -->
			<mapZone other="Lord Howe Standard Time" territory="001" type="Australia/Lord_Howe"/>
			<mapZone other="Lord Howe Standard Time" territory="AU" type="Australia/Lord_Howe"/>

			<!-- (UTC+11:00) Bougainville Island -->
			<mapZone other="Bougainville Standard Time" territory="001" type="Pacific/Bougainville"/>
			<mapZone other="Bougainville Standard Time" territory="PG" type="Pacific/Bougainville"/>

			<!-- (UTC+11:00) Chokurdakh -->
			<mapZone other="Russia Time Zone 10" territory="001" type="Asia/Srednekolymsk"/>
			<mapZone other="Russia Time Zone 10" territory="RU" type="Asia/Srednekolymsk"/>

			<!-- (UTC+11:00) Magadan -->
			<mapZone other="Magadan Standard Time" territory="001" type="Asia/Magadan"/>
			<mapZone other="Magadan Standard Time" territory="RU" type="Asia/Magadan"/>

			<!-- (UTC+11:00) Norfolk Island -->
			<mapZone other="Norfolk Standard Time" territory="001" type="Pacific/Norfolk"/>
			<mapZone other="Norfolk Standard Time" territory="NF" type="Pacific/Norfolk"/>

			<!-- (UTC+11:00) Sakhalin -->
			<mapZone other="Sakhalin Standard Time" territory="001" type="Asia/Sakhalin"/>
			<mapZone other="Sakhalin Standard Time" territory="RU" type="Asia/Sakhalin"/>

			<!-- (UTC+11:00) Solomon Is., New Caledonia -->
			<mapZone other="Central Pacific Standard Time" territory="001" type="Pacific/Guadalcanal"/>
			<mapZone other="Central Pacific Standard Time" territory="AQ" type="Antarctica/Casey"/>
			<mapZone other="Central Pacific Standard Time" territory="FM" type="Pacific/Ponape"/>
			<mapZone other="Central Pacific Standard Time" territory="FM" type="Pacific/Kosrae"/>
			<mapZone other="Central Pacific Standard Time" territory="NC" type="Pacific/Noumea"/>
			<mapZone other="Central Pacific Standard Time" territory="SB" type="Pacific/Guadalcanal"/>
			<mapZone other="Central Pacific Standard Time" territory="VU" type="Pacific/Efate"/>
			<mapZone other="Central Pacific Standard Time" territory="ZZ" type="Etc/GMT-11"/>

			<!-- (UTC+12:00) Anadyr, Petropavlovsk-Kamchatsky -->
			<mapZone other="Russia Time Zone 11" territory="001" type="Asia/Kamchatka"/>
			<mapZone other="Russia Time Zone 11" territory="RU" type="Asia/Kamchatka"/>
			<mapZone other="Russia Time Zone 11" territory="RU" type="Asia/Anadyr"/>

			<!-- (UTC+12:00) Auckland, Wellington -->
			<mapZone other="New Zealand Standard Time" territory="001" type="Pacific/Auckland"/>
			<mapZone other="New Zealand Standard Time" territory="AQ" type="Antarctica/McMurdo"/>
			<mapZone other="New Zealand Standard Time" territory="NZ" type="Pacific/Auckland"/>

			<!-- (UTC+12:00) Coordinated Universal Time+12 -->
			<mapZone other="UTC+12" territory="001" type="Etc/GMT-12"/>
			<mapZone other="UTC+12" territory="KI" type="Pacific/Tarawa"/>
			<mapZone other="UTC+12" territory="MH" type="Pacific/Majuro"/>
			<mapZone other="UTC+12" territory="MH" type="Pacific/Kwajalein"/>
			<mapZone other="UTC+12" territory="NR" type="Pacific/Nauru"/>
			<mapZone other="UTC+12" territory="TV" type="Pacific/Funafuti"/>
			<mapZone other="UTC+12" territory="UM" type="Pacific/Wake"/>
			<mapZone other="UTC+12" territory="WF" type="Pacific/Wallis"/>
			<mapZone other="UTC+12" territory="ZZ" type="Etc/GMT-12"/>

			<!-- (UTC+12:00) Fiji -->
			<mapZone other="Fiji Standard Time" territory="001" type="Pacific/Fiji"/>
			<mapZone other="Fiji Standard Time" territory="FJ" type="Pacific/Fiji"/>

			<!-- (UTC+12:45) Chatham Islands -->
			<mapZone other="Chatham Islands Standard Time" territory="001" type="Pacific/Chatham"/>
			<mapZone other="Chatham Islands Standard Time" territory="NZ" type="Pacific/Chatham"/>

			<!-- (UTC+13:00) Coordinated Universal Time+13 -->
			<mapZone other="UTC+13" territory="001" type="Etc/GMT-13"/>
			<mapZone other="UTC+13" territory="KI" type="Pacific/Enderbury"/>
			<mapZone other="UTC+13" territory="TK" type="Pacific/Fakaofo"/>
			<mapZone other="UTC+13" territory="ZZ" type="Etc/GMT-13"/>

			<!-- (UTC+13:00) Nuku alofa -->
			<mapZone other="Tonga Standard Time" territory="001" type="Pacific/Tongatapu"/>
			<mapZone other="Tonga Standard Time" territory="TO" type="Pacific/Tongatapu"/>

			<!-- (UTC+13:00) Samoa -->
			<mapZone other="Samoa Standard Time" territory="001" type="Pacific/Apia"/>
			<mapZone other="Samoa Standard Time" territory="WS" type="Pacific/Apia"/>

			<!-- (UTC+14:00) Kiritimati Island -->
			<mapZone other="Line Islands Standard Time" territory="001" type="Pacific/Kiritimati"/>
			<mapZone other="Line Islands Standard Time" territory="KI" type="Pacific/Kiritimati"/>
			<mapZone other="Line Islands Standard Time" territory="ZZ" type="Etc/GMT-14"/>
		</mapTimezones>
	</windowsZones>
</supplementalData>
';

-- Inserts the timezone mappings to the OlsonToWindows table
INSERT INTO OlsonToWindows (OlsonTz, WindowsTz)
SELECT DISTINCT
  mz.value('@type', 'varchar(300)'),
  mz.value('@other', 'varchar(50)')
FROM @xml.nodes('supplementalData/windowsZones/mapTimezones/mapZone') x(mz);

INSERT INTO Timezone
([Name], [CreatedDT])
VALUES
('America/Los_Angeles', GETUTCDATE()),
('Asia/Seoul', GETUTCDATE()),
('Europe/Paris', GETUTCDATE())

INSERT INTO Place
([Name], [Latitude], [Longitude], [CanUpdateIsOpen], [TimezoneId], [IsOpen])
VALUES
('Rock n Pies', '34.024903476595526', '-118.49102627355538', 1, 1, 0),
('Starbucks', '34.02424334880533', '-118.49170140239106', 1, 1, 0),
-- Can update is open, is open
('Should show', '34.02424334880533', '-118.49170140239106', 1, 1, 1),
-- Cannot update is open, is open
('Should show2', '34.02424334880533', '-118.49170140239106', 0, 1, 1),
-- Can update is open, is not open
('Should show3', '34.02424334880533', '-118.49170140239106', 1, 1, 0),
-- Cannot update is open, is not open
('Should show4', '34.02424334880533', '-118.49170140239106', 0, 1, 0),
('Ezysalad', '37.56714741551768', '126.98022368758403', 1, 2, 0),
('Epicure', '48.871777968541565', '2.31487934607973', 1, 3, 0);

INSERT INTO [Address]
([Name], [City], [CreatedDT], [PlaceId])
VALUES
('address 1', 'Santa Monica', GETUTCDATE(), 1),
('address 2', 'Santa Monica', GETUTCDATE(), 2),
('address 3', 'Santa Monica', GETUTCDATE(), 3),
('address 4', 'Santa Monica', GETUTCDATE(), 4),
('address 5', 'Santa Monica', GETUTCDATE(), 5),
('address 6', 'Santa Monica', GETUTCDATE(), 6),
('address 7', 'Seoul', GETUTCDATE(), 7),
('address 8', 'Paris', GETUTCDATE(), 8);

INSERT INTO StoreHour
([DayOfTheWeekId], [PlaceId], [OpenHour], [CloseHour])
VALUES
-- Place 1
('1', '1', '09:00', '19:00'),
('2', '1', '09:00', '19:00'),
('3', '1', '09:00', '19:00'),
('4', '1', '09:00', '19:00'),
('5', '1', '11:00', '20:00'),
('6', '1', '11:00', '20:00'),
('7', '1', '09:00', '19:00'),

-- Place 2
('1', '2', '09:00', '19:00'),
('2', '2', '09:00', '19:00'),
('3', '2', '09:00', '19:00'),
('4', '2', '09:00', '19:00'),
('5', '2', '11:00', '20:00'),
('6', '2', '11:00', '20:00'),
('7', '2', '09:00', '19:00'),

-- Place 3
('1', '3', '09:00', '19:00'),
('2', '3', '09:00', '19:00'),
('3', '3', '09:00', '19:00'),
('4', '3', '09:00', '19:00'),
('5', '3', '11:00', '20:00'),
('6', '3', '11:00', '20:00'),
('7', '3', '09:00', '19:00'),

-- Place 4
('1', '4', '09:00', '19:00'),
('2', '4', '09:00', '19:00'),
('3', '4', '09:00', '19:00'),
('4', '4', '09:00', '19:00'),
('5', '4', '11:00', '20:00'),
('6', '4', '11:00', '20:00'),
('7', '4', '09:00', '19:00'),

-- Place 5
('1', '5', '09:00', '19:00'),
('2', '5', '09:00', '19:00'),
('3', '5', '09:00', '19:00'),
('4', '5', '09:00', '19:00'),
('5', '5', '11:00', '20:00'),
('6', '5', '11:00', '20:00'),
('7', '5', '09:00', '19:00'),

-- Place 6
('1', '6', '09:00', '19:00'),
('2', '6', '09:00', '19:00'),
('3', '6', '09:00', '19:00'),
('4', '6', '09:00', '19:00'),
('5', '6', '11:00', '20:00'),
('6', '6', '11:00', '20:00'),
('7', '6', '09:00', '19:00'),
  
-- Place 7
('1', '7', '09:00', '19:00'),
('2', '7', '09:00', '19:00'),
('3', '7', '09:00', '19:00'),
('4', '7', '09:00', '19:00'),
('5', '7', '11:00', '20:00'),
('6', '7', '11:00', '20:00'),
('7', '7', '09:00', '19:00'),
-- Place 8
('1', '8', '09:00', '19:00'),
('2', '8', '09:00', '19:00'),
('3', '8', '09:00', '19:00'),
('4', '8', '09:00', '19:00'),
('5', '8', '11:00', '20:00'),
('6', '8', '11:00', '20:00'),
('7', '8', '09:00', '19:00');
542 rows affected

SELECT *
FROM Place;

SELECT *
FROM DayOfTheWeek;

SELECT *
FROM StoreHour;

SELECT *
FROM Address;

SELECT *
FROM Timezone;


PlaceId
Name
Latitude
Longitude
IsOpen
CanUpdateIsOpen
TimezoneId


1
Rock n Pies
34.0249034765955
-118.491026273555
False
True
1

2
Starbucks
34.0242433488053
-118.491701402391
False
True
1

3
Should show
34.0242433488053
-118.491701402391
True
True
1

4
Should show2
34.0242433488053
-118.491701402391
True
False
1

5
Should show3
34.0242433488053
-118.491701402391
False
True
1

6
Should show4
34.0242433488053
-118.491701402391
False
False
1

7
Ezysalad
37.5671474155177
126.980223687584
False
True
2

8
Epicure
48.8717779685416
2.31487934607973
False
True
3




DayOfTheWeekId
Name


1
Monday

2
Tuesday

3
Wednesday

4
Thursday

5
Friday

6
Saturday

7
Sunday




StoreHourId
OpenHour
CloseHour
CreatedDT
DayOfTheWeekId
PlaceId


1
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
1
1

2
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
2
1

3
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
3
1

4
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
4
1

5
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
5
1

6
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
6
1

7
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
7
1

8
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
1
2

9
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
2
2

10
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
3
2

11
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
4
2

12
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
5
2

13
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
6
2

14
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
7
2

15
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
1
3

16
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
2
3

17
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
3
3

18
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
4
3

19
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
5
3

20
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
6
3

21
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
7
3

22
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
1
4

23
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
2
4

24
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
3
4

25
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
4
4

26
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
5
4

27
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
6
4

28
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
7
4

29
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
1
5

30
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
2
5

31
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
3
5

32
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
4
5

33
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
5
5

34
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
6
5

35
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
7
5

36
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
1
6

37
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
2
6

38
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
3
6

39
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
4
6

40
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
5
6

41
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
6
6

42
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
7
6

43
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
1
7

44
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
2
7

45
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
3
7

46
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
4
7

47
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
5
7

48
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
6
7

49
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
7
7

50
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
1
8

51
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
2
8

52
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
3
8

53
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
4
8

54
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
5
8

55
11:00:00.0000000
20:00:00.0000000
2023-05-13 06:21:28.470
6
8

56
09:00:00.0000000
19:00:00.0000000
2023-05-13 06:21:28.470
7
8




AddressId
Name
City
CreatedDT
PlaceId


1
address 1
Santa Monica
2023-05-13 06:21:28.470
1

2
address 2
Santa Monica
2023-05-13 06:21:28.470
2

3
address 3
Santa Monica
2023-05-13 06:21:28.470
3

4
address 4
Santa Monica
2023-05-13 06:21:28.470
4

5
address 5
Santa Monica
2023-05-13 06:21:28.470
5

6
address 6
Santa Monica
2023-05-13 06:21:28.470
6

7
address 7
Seoul
2023-05-13 06:21:28.470
7

8
address 8
Paris
2023-05-13 06:21:28.470
8




TimezoneId
Name
CreatedDT


1
America/Los_Angeles
2023-05-13 06:21:28.470

2
Asia/Seoul
2023-05-13 06:21:28.470

3
Europe/Paris
2023-05-13 06:21:28.470


-- This function maps Olson timezones to Windows timezones
CREATE FUNCTION OlsonToWindowsMapper (@olson varchar(300))
RETURNS TABLE
AS RETURN
  SELECT [tz].[WindowsTz] AS [value]
  FROM [OlsonToWindows] AS [tz]
  WHERE [tz].[OlsonTz] = @olson;
CREATE PROCEDURE spTest
  @UserCity NVARCHAR(100)
AS
BEGIN

  SET NOCOUNT ON;

  UPDATE P

  SET
    IsOpen = CASE WHEN CanUpdateIsOpen = 1
      THEN OpenTimeResult
      ELSE IsOpen
    END,
    CanUpdateIsOpen = 0

  OUTPUT
    INSERTED.PlaceId,
    INSERTED.[Name],
    INSERTED.Latitude,
    INSERTED.Longitude,
    DELETED.OpenTimeResult,
    INSERTED.CanUpdateIsOpen
  INTO PlaceAudit(
    PlaceId,
    [Name],
    Latitude,
    Longitude,
    IsOpen,
    CanUpdateIsOpen
  )

  FROM (

    SELECT TOP (10) P.*, OpenTimeCalc.OpenPlace AS OpenTimeResult

    FROM Place AS P
  
    JOIN Timezone AS TZ
	  ON TZ.TimezoneId = P.TimezoneId
    JOIN Address AS A
	  ON P.PlaceId = A.PlaceId

    -- Returns the Windows timezone's NAME for each Place
    CROSS APPLY (
      SELECT [value] AS [WinTZ]
      FROM [OlsonToWindowsMapper]([TZ].[Name])
	) AS [OTWM]

    -- Returns a datetimeoffset value based on the timezone's name for each Place
    CROSS APPLY (
      SELECT
        GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE [OTWM].[WinTZ] AS [LocalDateTime2]
    ) AS [LT2]

    -- Returns the local day NAME, and the local TIME without date.
    CROSS APPLY (
      SELECT
        DATENAME(WEEKDAY, [LT2].[LocalDateTime2]) AS [LocalWeekdayName],
        CAST([LT2].[LocalDateTime2] AS [TIME]) AS [LocalTime]
    ) AS [DT]
      
    JOIN [DayOfTheWeek] AS [DOW]
      ON [DOW].[Name] = [DT].[LocalWeekdayName]

  -- select Place where its local datetime is between its open and closed hours,
    -- for the day returned from the local datetime
    CROSS APPLY (
      SELECT
        CASE WHEN EXISTS (
          SELECT *
            FROM [StoreHour] AS [SH]
              WHERE [SH].[PlaceId] = [P].[PlaceId]
              AND [SH].[DayOfTheWeekId] = [DOW].[DayOfTheWeekId]
              AND (DT.LocalTime BETWEEN SH.OpenHour AND SH.CloseHour)
        ) THEN 1 -- IsOpen = TRUE
          ELSE 0 -- IsOpen = FALSE
          END
          AS [OpenPlace] -- END, case AS Alias
    ) AS [OpenTimeCalc]


    WHERE A.City = @UserCity
    ORDER BY P.PlaceId

  ) AS P;

END
EXEC spTest @UserCity='Santa Monica';

SELECT * FROM PlaceAudit


PlaceId
Name
Latitude
Longitude
IsOpen
CanUpdateIsOpen


1
Rock n Pies
34.0249034765955
-118.491026273555
False
False

2
Starbucks
34.0242433488053
-118.491701402391
False
False

3
Should show
34.0242433488053
-118.491701402391
False
False

4
Should show2
34.0242433488053
-118.491701402391
False
False

5
Should show3
34.0242433488053
-118.491701402391
False
False

6
Should show4
34.0242433488053
-118.491701402391
False
False
PlaceId	Name	Latitude	Longitude	IsOpen	CanUpdateIsOpen	TimezoneId
1	Rock n Pies	34.0249034765955	-118.491026273555	False	True	1
2	Starbucks	34.0242433488053	-118.491701402391	False	True	1
3	Should show	34.0242433488053	-118.491701402391	True	True	1
4	Should show2	34.0242433488053	-118.491701402391	True	False	1
5	Should show3	34.0242433488053	-118.491701402391	False	True	1
6	Should show4	34.0242433488053	-118.491701402391	False	False	1
7	Ezysalad	37.5671474155177	126.980223687584	False	True	2
8	Epicure	48.8717779685416	2.31487934607973	False	True	3
StoreHourId	OpenHour	CloseHour	CreatedDT	DayOfTheWeekId	PlaceId
1	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	1	1
2	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	2	1
3	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	3	1
4	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	4	1
5	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	5	1
6	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	6	1
7	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	7	1
8	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	1	2
9	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	2	2
10	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	3	2
11	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	4	2
12	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	5	2
13	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	6	2
14	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	7	2
15	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	1	3
16	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	2	3
17	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	3	3
18	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	4	3
19	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	5	3
20	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	6	3
21	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	7	3
22	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	1	4
23	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	2	4
24	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	3	4
25	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	4	4
26	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	5	4
27	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	6	4
28	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	7	4
29	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	1	5
30	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	2	5
31	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	3	5
32	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	4	5
33	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	5	5
34	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	6	5
35	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	7	5
36	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	1	6
37	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	2	6
38	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	3	6
39	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	4	6
40	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	5	6
41	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	6	6
42	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	7	6
43	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	1	7
44	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	2	7
45	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	3	7
46	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	4	7
47	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	5	7
48	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	6	7
49	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	7	7
50	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	1	8
51	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	2	8
52	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	3	8
53	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	4	8
54	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	5	8
55	11:00:00.0000000	20:00:00.0000000	2023-05-13 06:21:28.470	6	8
56	09:00:00.0000000	19:00:00.0000000	2023-05-13 06:21:28.470	7	8
AddressId	Name	City	CreatedDT	PlaceId
1	address 1	Santa Monica	2023-05-13 06:21:28.470	1
2	address 2	Santa Monica	2023-05-13 06:21:28.470	2
3	address 3	Santa Monica	2023-05-13 06:21:28.470	3
4	address 4	Santa Monica	2023-05-13 06:21:28.470	4
5	address 5	Santa Monica	2023-05-13 06:21:28.470	5
6	address 6	Santa Monica	2023-05-13 06:21:28.470	6
7	address 7	Seoul	2023-05-13 06:21:28.470	7
8	address 8	Paris	2023-05-13 06:21:28.470	8
TimezoneId	Name	CreatedDT
1	America/Los_Angeles	2023-05-13 06:21:28.470
2	Asia/Seoul	2023-05-13 06:21:28.470
3	Europe/Paris	2023-05-13 06:21:28.470