We have requirements to add a Calendar to an existing application. After much deliberation, we decided on using the ctxCalendar ActiveX control from DBI - a bit of a learning curve, but has met expectations.
One of the requirements was to store (when saved) appointment start and end datetimes as UTC and then convert back when the calendar loaded the appointments. The idea is that when users travel with laptops, their calendar appointments will show the correct datetimes no matter the timezone - for this to work properly, users will have to create appointments to their local (before travel) timezones. We are testing the code below (note that the code to update ctxCalendar is not included):
Note that daylight savings time is not factored into the offset bias. This was successfully tested before and after the recent switch in DST. If the DST offset was included, then the conversion from UTC would be inaccurate with a DST switch.
As stated above, this will only work if users create appointments in their local (before travel) timezones. However, users will want to create appointments in different timezones. I'm not quite clear on a solution yet. Will probably have to have a dropdown of all timezones for users to select and then also store the UTC time offset... and then figure out how to covert (with this offset) back to the local (before travel) timezone?? Confusing. Has anyone done anything like this? Any help will be appreciated.
One of the requirements was to store (when saved) appointment start and end datetimes as UTC and then convert back when the calendar loaded the appointments. The idea is that when users travel with laptops, their calendar appointments will show the correct datetimes no matter the timezone - for this to work properly, users will have to create appointments to their local (before travel) timezones. We are testing the code below (note that the code to update ctxCalendar is not included):
Code:
DECLARE INTEGER GetTimeZoneInformation IN kernel32.dll ;
STRING @lpTimeZoneInformation
#DEFINE TIME_ZONE_ID_UNKNOWN 0
#DEFINE TIME_ZONE_ID_STANDARD 1
#DEFINE TIME_ZONE_ID_DAYLIGHT 2
ltAppBeg = DATETIME(2021, 11, 9, 10, 30) && Sample Appointment start
ltAppEnd = DATETIME(2021, 11, 9, 12, 0) && Sample Appointment end
ltUTCBeg = GetUTCtime(ltAppBeg) && Sample Appointment UTC start stored in database
ltUTCEnd = GetUTCtime(ltAppEnd) && Sample Appointment UTC end stored in database
ltCalBeg = ltUTCBeg - (GetTimeZone("BIAS", .F.) * 60) && Sample Appointment UTC start converted from database
ltCalEnd = ltUTCEnd - (GetTimeZone("BIAS", .F.) * 60) && Sample Appointment UTC end converted from database
?ltAppBeg
?ltAppEnd
?
?ltUTCBeg
?ltUTCEnd
?
?ltCalBeg
?ltCalEnd
FUNCTION GetUTCtime(ttDate AS Datetime) AS Datetime
RETURN ttDate + (GetTimeZone("BIAS", .F.) * 60)
ENDFUNC
*!* Returns the Time Zone OffSet Bias or Time Zone Name
*!* Parameters: tcFunc = "BIAS" (or blank) returns the offset bias in minutes: UTC (Coordinated Universal Time) = Local Time + Offset Bias
*!* tcFunc = "NAME" returns the time zone name.
*!* tlDSToffset = Option to include DST in offset bias.
FUNCTION GetTimeZone(tcFunc AS String, tlDSToffset AS Logical) AS Variant
LOCAL lcTZInfo, lcDesc, liInfo, liBias
#DEFINE TIME_ZONE_SIZE 172
lcTZInfo = REPLICATE(CHR(0), TIME_ZONE_SIZE)
liInfo = GetTimeZoneInformation(@lcTZInfo)
liBias = Buf2DWord(SUBSTR(lcTZInfo, 1,4)) - IIF(tlDSToffset AND (liInfo = TIME_ZONE_ID_DAYLIGHT), 60, 0)
IF VARTYPE(tcFunc) = "C" AND UPPER(tcFunc) = "NAME"
DO CASE
CASE liInfo = TIME_ZONE_ID_STANDARD
lcDesc = STRTRAN(SUBSTR(lcTZInfo, 5, 64), CHR(0), "")
CASE liInfo = TIME_ZONE_ID_DAYLIGHT
lcDesc = STRTRAN(SUBSTR(lcTZInfo, 89, 64), CHR(0), "")
CASE liInfo = TIME_ZONE_ID_UNKNOWN
lcDesc = "Time Zone Unknown"
OTHERWISE
lcDesc = "System Error"
ENDCASE
RETURN lcDesc
ENDIF
RETURN liBias
ENDFUNC
FUNCTION Buf2DWord(tcBuffer AS String)
RETURN ASC(SUBSTR(tcBuffer, 1,1)) + ;
BITLSHIFT(ASC(SUBSTR(tcBuffer, 2,1)), 8) + ;
BITLSHIFT(ASC(SUBSTR(tcBuffer, 3,1)), 16) + ;
BITLSHIFT(ASC(SUBSTR(tcBuffer, 4,1)), 24)
ENDFUNCNote that daylight savings time is not factored into the offset bias. This was successfully tested before and after the recent switch in DST. If the DST offset was included, then the conversion from UTC would be inaccurate with a DST switch.
As stated above, this will only work if users create appointments in their local (before travel) timezones. However, users will want to create appointments in different timezones. I'm not quite clear on a solution yet. Will probably have to have a dropdown of all timezones for users to select and then also store the UTC time offset... and then figure out how to covert (with this offset) back to the local (before travel) timezone?? Confusing. Has anyone done anything like this? Any help will be appreciated.
