PeterBaileyUk
asked on
Timezone question in access
I cant find the question ID but Gustav has a solution that I might be able to use if I can have some pointer how to use it.
Ive inserted the code from this question
Time zones in Access: Specifying/displaying/conv erting
into my module 1
currently I have a start time which is my local time (spain time so gmt plus whatever) I have a chinese class start time currently this is coded when I start the class it does
for now its manageable but now I will have teachers from other timezones so itll be difficult to hard code and make changes.
so I really need to get the correct time of the teacher timezone and from there it tell me what time the class starts in my time for me and in my bosses time for her (chinese time)
How do I use gustavs solution to achieve this?
Ive inserted the code from this question
Time zones in Access: Specifying/displaying/conv
into my module 1
currently I have a start time which is my local time (spain time so gmt plus whatever) I have a chinese class start time currently this is coded when I start the class it does
TChinese = DateAdd("h", 7, TStartTime)
obviously bad when summertime starts as gmt changes as does spanish time.for now its manageable but now I will have teachers from other timezones so itll be difficult to hard code and make changes.
so I really need to get the correct time of the teacher timezone and from there it tell me what time the class starts in my time for me and in my bosses time for her (chinese time)
How do I use gustavs solution to achieve this?
ASKER
I was looking for some pointers how to use the function itself how to integrate,
How are you going to retrieve the teachers timezone?
Is the code running on their machine?
If you store all times as UTC (aka GMT), you will easily be able to offset the time based of the teachers location.
Is the code running on their machine?
If you store all times as UTC (aka GMT), you will easily be able to offset the time based of the teachers location.
ASKER
I wasnt sure how to store that, so I should store their country code. I wonder if in my case as all the classes are in china if I store all chinese time and then can gustavs function to track back. I am not sure time always confuses me as to which is best way to adopt.
The time zone should be in the regional settings of the user's Windows registry.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the access database is only on my computer so not distributed to anyone else. So I assume I can then create a utc field and then use that. does the funcion deal with british summer time automatically? I can set the remote bias but what happens with shanghai relative to gmt or bst?
ASKER
I wonder if windows 10 has changed somehow it says
function DateMonthWeekday not defined
function DateMonthWeekday not defined
Public Function IsLocalDaylightSavingTime( _
Optional datDate As Date) _
As Boolean
' Returns True if the date is within the local Daylight Saving Time period as defined for the current year.
' Limitation: Does not count for previous or future changes in definition of DST period.
' 2010-02-16. Cactus Data ApS, CPH.
Dim tzi As TIME_ZONE_INFORMATION
Dim booDST As Boolean
Dim lngTimeZoneID As Long
Dim datDaylight As Date
Dim datStandard As Date
Dim intYear As Integer
lngTimeZoneID = GetTimeZoneInformation(tzi)
If datDate = 0 Or datDate = Date Then
' GetTimeZoneInformation returns the timezone ID for the current date.
booDST = (lngTimeZoneID = TIME_ZONE_ID_DAYLIGHT)
Else
' Calculate daylight starting date and standard starting date for intYear.
intYear = Year(datDate)
datDaylight = DateMonthWeekday(tzi.DaylightDate.wDay, DateSerial(intYear, tzi.DaylightDate.wMonth, 1), vbSunday)
datStandard = DateMonthWeekday(tzi.StandardDate.wDay, DateSerial(intYear, tzi.StandardDate.wMonth, 1), vbSunday)
' Check if datDate falls within the period of daylight saving time for year intYear.
If datDate >= datDaylight And datDate < datStandard Then
booDST = True
End If
End If
IsLocalDaylightSavingTime = booDST
End Function
There are several ways. One is to store UTC time with or without the local offset/bias. If with, local time can be found directly, without you will need a function to determine if the UTC time should be converted to Standard or Dst time. You can also store both UTC and local time.
China seems to switch between CST and CDT around mid April and mid September.
UTC has per definition no daylight saving time.
/gustav
China seems to switch between CST and CDT around mid April and mid September.
UTC has per definition no daylight saving time.
/gustav
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It appears, that China stopped using DST (CDT) in 1991, thus it's CST onwards.
/gustav
/gustav
ASKER
I am just experimenting now, Ive added a date field called [Start time universal] and I know the scheduled time is always the chinese start time so I guess I can just deal with the correct offsets. I assume from ID: 42388630 that I need to create a function that knows if the dst should be applied to the offset? In this case china never has. I assume that has to be manually input somewhere.
ASKER
So it appears that as we dopnt know the dates in advance that I have to maybe store them or maybe have a button switching them on and off I guess the dst offsets are the same all the time
You can always find the local bias with the function GetLocalTimeZoneBias ...
Also, note the Offset and Bias are not the same:
Bias is used in the Windows API while Offset is used in Unix.
/gustav
Also, note the Offset and Bias are not the same:
Offset
The difference in seconds between local time and UTC time.
Calculation method is: UTC time + Offset = local time
Bias
The difference in minutes between UTC time and local time.
Calculation method is: UTC time = local time + Bias
Bias is used in the Windows API while Offset is used in Unix.
/gustav
ASKER
so chinese time and utc will always be a constant value @ 8 hours and now I deal with offset so central european is utc + 1
I think thats correct. yes I ran a query to get the localtime bias and it says -60 which I guess means in relation too?
I think thats correct. yes I ran a query to get the localtime bias and it says -60 which I guess means in relation too?
ASKER
its ok I realised I read it
Offset The difference in seconds between local time and UTC time.
Calculation method is: UTC time + Offset = local time
Bias
The difference in minutes between UTC time and local time.
Calculation method is: UTC time = local time + Bias
Offset The difference in seconds between local time and UTC time.
Calculation method is: UTC time + Offset = local time
Bias
The difference in minutes between UTC time and local time.
Calculation method is: UTC time = local time + Bias
Yes:
/gustav
' Calculates the date/time of datLocal in a remote time zone.
' Adds the difference in minutes between the local time zone bias and
' the remote time zone bias, if both bias are relative to UTC.
/gustav
ASKER
ASKER
I used ID: 42388412
with intermediate window
local bias says -60
I wasnt sure why it wants -480 to get correct result when I know china is utc +8
?DateAddTimeZoneDiff(Now() , -60, -480)
02/12/2017 15:37:35
+480 remote bias
01/12/2017 23:37:25
with intermediate window
local bias says -60
I wasnt sure why it wants -480 to get correct result when I know china is utc +8
?DateAddTimeZoneDiff(Now()
02/12/2017 15:37:35
+480 remote bias
01/12/2017 23:37:25
Bias and Offset, I explained above.
Both you and Shanghai are East of Greenwich, thus:
Both you and Shanghai are East of Greenwich, thus:
LocalTime = #2017-12-02 11:30:00#
RemoteTime = DateAddTimeZoneDiff(LocalTime, -60, -480)
RemoteTime -> 2017-12-02 18:30:00
/gustav
ASKER
your earlier reply didn't have the -480 "RemoteBias = 480 ' 8 hours relative to UTC.", that's what it was.
Sorry. It can be hard to keep track of.
/gustav
/gustav
ASKER
is it possible to know if a remote locations summertime is in force?
Yes, fx here:
Time Zone in Beijing, Beijing Municipality, China
But:
Time Zone in Beijing, Beijing Municipality, China
But:
It appears, that China stopped using DST (CDT) in 1991, thus it's CST onwards./gustav
ASKER
aha no china is fine Gustav Ive taken their time which is now always fixed to utc. I have teachers in other time zones and didnt know if it was possible to get that data for now Ive created a table and written the dates in, this might be the easiest way to do it as they are local they know the dates of the dst.
A good data-ready source is timezonedb which uses the data from IANA, a trusted source.
They offer both an API and a download of the data.
/gustav
They offer both an API and a download of the data.
/gustav
Answer provided.
ASKER