Re: MySQL Date/Timezone Conversion/GMT

[Derek Meyer]

I think I am following you.. I am close to pushing on this table the max keys for indexes. (I understand one index can contain up to 16 fields but I would get low speed on it) I think I am CLOSE with the following but it is not quite correct as I get some incorrect results still where Site_Full_Timezones
contain the following (for now soon it will have many more)

America/Boise
America/Chicago
America/Denver
America/Detroit
America/Indiana/Indianapolis
America/Indiana/Tell_City
America/Los_Angeles
America/New_York
America/Phoenix
America/Puerto_Rico
America/Toronto
Pacific/Honolulu


update Site set Site_Day_Status =
case

when dayname(convert_TZ(date_sub(now(),INTERVAL 1 HOUR),'EST','UTC')) = 'Sunday'
and convert_TZ(now(),'EST','UTC') >=convert_TZ(Site_Sun_Open,Site_Full_Timezone,'UTC')
and convert_TZ(now(),'EST','UTC') <=convert_TZ(Site_Sun_Close,Site_Full_Timezone,'UTC')
then 'Open'

when dayname(convert_TZ(date_sub(now(),INTERVAL 1 HOUR),'EST','UTC')) = 'Monday'
and convert_TZ(now(),'EST','UTC') >=convert_TZ(Site_Mon_Open,Site_Full_Timezone,'UTC')
and convert_TZ(now(),'EST','UTC') <=convert_TZ(Site_Mon_Close,Site_Full_Timezone,'UTC')
then 'Open'

when dayname(convert_TZ(date_sub(now(),INTERVAL 1 HOUR),'EST','UTC')) = 'Tuesday'
and convert_TZ(now(),'EST','UTC') >=convert_TZ(Site_Tue_Open,Site_Full_Timezone,'UTC')
and convert_TZ(now(),'EST','UTC') <=convert_TZ(Site_Tue_Close,Site_Full_Timezone,'UTC')
then 'Open'

when dayname(convert_TZ(date_sub(now(),INTERVAL 1 HOUR),'EST','UTC')) = 'Wednesday'
and convert_TZ(now(),'EST','UTC') >=convert_TZ(Site_Wed_Open,Site_Full_Timezone,'UTC')
and convert_TZ(now(),'EST','UTC') <=convert_TZ(Site_Wed_Close,Site_Full_Timezone,'UTC')
then 'Open'

when dayname(convert_TZ(date_sub(now(),INTERVAL 1 HOUR),'EST','UTC')) = 'Thursday'
and convert_TZ(now(),'EST','UTC') >=convert_TZ(Site_Thu_Open,Site_Full_Timezone,'UTC')
and convert_TZ(now(),'EST','UTC') <=convert_TZ(Site_Thu_Close,Site_Full_Timezone,'UTC')
then 'Open'

when dayname(convert_TZ(date_sub(now(),INTERVAL 1 HOUR),'EST','UTC')) = 'Friday'
and convert_TZ(now(),'EST','UTC') >=convert_TZ(Site_Fri_Open,Site_Full_Timezone,'UTC')
and convert_TZ(now(),'EST','UTC') <=convert_TZ(Site_Fri_Close,Site_Full_Timezone,'UTC')
then 'Open'

when dayname(convert_TZ(date_sub(now(),INTERVAL 1 HOUR),'EST','UTC')) = 'Saturday'
and convert_TZ(now(),'EST','UTC') >=convert_TZ(Site_Sat_Open,Site_Full_Timezone,'UTC')
and convert_TZ(now(),'EST','UTC') <=convert_TZ(Site_Sat_Close,Site_Full_Timezone,'UTC')
then 'Open'


else 'Closed' End where