Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
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/converting

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)

Open in new window

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?
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

is that the id of gustavs the 42388306?
I was looking for some pointers how to use the function itself how to integrate,
Avatar of Joe Howard
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.
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
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
I wonder if windows 10 has changed somehow it says

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

Open in new window

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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It appears, that China stopped using DST (CDT) in 1991, thus it's CST onwards.

/gustav
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.
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:

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
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?
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
Yes:

' 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
Because windows handles the daylight saving times automatically I dont need worry about that I can just get the offset local bias to work out the UTC and then dependent on the teachers timezone and their UTC offset do a dateadd to get the local class time.

I can pull that offset from a table like
User generated image
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
Bias and Offset, I explained above.
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

Open in new window

/gustav
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
is it possible to know if a remote locations summertime is in force?
Yes, fx here:

Time Zone in Beijing, Beijing Municipality, China

But:
It appears, that China stopped using DST (CDT) in 1991, thus it's CST onwards.
/gustav
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
Answer provided.