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?
PeterBaileyUkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PeterBaileyUkAuthor Commented:
is that the id of gustavs the 42388306?
0
PeterBaileyUkAuthor Commented:
I was looking for some pointers how to use the function itself how to integrate,
0
MacroShadowCommented:
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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PeterBaileyUkAuthor Commented:
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.
0
aikimarkCommented:
The time zone should be in the regional settings of the user's Windows registry.
0
Gustav BrockCIOCommented:
You would get your local bias first:

LocalBias = GetLocalTimeZoneBias(Date, False)

Open in new window

Then determine the remote bias. If Shanghai, it is for timezone CST:

RemoteBias = 480 ' 8 hours relative to UTC.

Open in new window

Now, you can convert:

DateRemote = DateAddTimeZoneDiff(Date, LocalBias, RemoteBias)

Open in new window

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterBaileyUkAuthor Commented:
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?
0
PeterBaileyUkAuthor Commented:
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

0
Gustav BrockCIOCommented:
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
0
Gustav BrockCIOCommented:
That's a missing function:

Public Function DateMonthWeekday( _
  Optional ByVal bytWeekdayOccurrence As Byte, _
  Optional ByVal datDateInMonth As Date, _
  Optional ByVal bytWeekday As Byte) _
  As Date
  
  Const cintDaysInWeek  As Integer = 7
  Dim intDayOffset      As Integer
  Dim datMonthFirst     As Date
  Dim datWeekday        As Date
  
' Calculates occurrence of bytWeekday of the month of datDateInMonth.
' If bytWeekdayOccurrence is 0 the first occurrence of bytWeekday of the month is assumed.
' If bytWeekdayOccurrence is 5 or anything else different from 0 to 4, the
' last occurrence of bytWeekday of the month is assumed.
' If no date for the month is supplied, today's date is used.
' If bytWeekday is not the Value of a weekday, the weekday of datDateInMonth is used.
' Returns the date as a date/time Value.
'
' 2008-09-12, Cactus Data ApS, CPH.
' 2009-12-10. Calculation of last occurrence changed to call of DateMonthLastWeekday().

  If datDateInMonth = 0 Then
    ' No date is specified.
    datDateInMonth = Date
  End If

  ' Validate bytWeekday.
  Select Case bytWeekday
    Case _
      vbMonday, _
      vbTuesday, _
      vbWednesday, _
      vbThursday, _
      vbFriday, _
      vbSaturday, _
      vbSunday
    Case Else
      ' Zero, none or invalid value for weekday.
      bytWeekday = Weekday(datDateInMonth, vbSunday)
  End Select
  
  ' Validate bytWeekdayOccurrence.
  If bytWeekdayOccurrence = 0 Then
    bytWeekdayOccurrence = 1
  End If
  
  Select Case bytWeekdayOccurrence
    Case 1, 2, 3, 4
      datMonthFirst = DateSerial(Year(datDateInMonth), Month(datDateInMonth), 1)
      ' Find offset of bytWeekday from first day of month.
      intDayOffset = (bytWeekday - Weekday(datMonthFirst, vbSunday) + cintDaysInWeek) Mod cintDaysInWeek
      ' Find offset for occurence no. of bytWeekday from first day of month.
      intDayOffset = intDayOffset + cintDaysInWeek * (bytWeekdayOccurrence - 1)
      datWeekday = DateAdd("d", intDayOffset, datMonthFirst)
    Case Else
      datWeekday = DateMonthLastWeekday(datDateInMonth, bytWeekday)
  End Select
  
  DateMonthWeekday = datWeekday

End Function


Public Function DateMonthLastWeekday( _
  Optional ByVal datDateInMonth As Date, _
  Optional ByVal bytWeekday As Byte) _
  As Date

' Calculates last occurrence of bytWeekday of the month of datDateInMonth.
' If no date for the month is supplied, today's date is used.
' If no weekday is supplied, the weekday of the supplied date is used.
' Returns the date as a date/time Value.
'
' 2007-01-25, Cactus Data ApS, CPH.
' 2009-12-10. Calculation of bytDayDiff simplified.
'             Parameters made optional.
  
  Dim datLastDay As Date
  Dim bytDayDiff As Byte
  
  ' No specific error handling.
  On Error Resume Next
  
  If datDateInMonth = 0 Then
    datDateInMonth = Date
  End If
  
  ' Validate bytWeekday.
  Select Case bytWeekday
    Case _
      vbMonday, _
      vbTuesday, _
      vbWednesday, _
      vbThursday, _
      vbFriday, _
      vbSaturday, _
      vbSunday
    Case Else
      ' Zero, none or invalid value for weekday.
      bytWeekday = Weekday(datDateInMonth, vbSunday)
  End Select
  
  ' Find last day of the month of datDateInMonth.
  datLastDay = DateSerial(Year(datDateInMonth), Month(datDateInMonth) + 1, 0)
  
  ' Determine number of days between last day of month and last lngWeekday of month
  ' by assuming lngWeekday being the first day of a week.
  bytDayDiff = Weekday(datLastDay, bytWeekday) - 1
  
  ' Calculate closest preceding weekday of the last day including this.
  DateMonthLastWeekday = DateAdd("d", -bytDayDiff, datLastDay)
  
End Function

Open in new window

/gustav
0
Gustav BrockCIOCommented:
It appears, that China stopped using DST (CDT) in 1991, thus it's CST onwards.

/gustav
0
PeterBaileyUkAuthor Commented:
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.
0
PeterBaileyUkAuthor Commented:
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
0
Gustav BrockCIOCommented:
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
0
PeterBaileyUkAuthor Commented:
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?
0
PeterBaileyUkAuthor Commented:
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
0
Gustav BrockCIOCommented:
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
0
PeterBaileyUkAuthor Commented:
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
ex
0
PeterBaileyUkAuthor Commented:
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
0
Gustav BrockCIOCommented:
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
0
PeterBaileyUkAuthor Commented:
your earlier reply didn't have the -480 "RemoteBias = 480 ' 8 hours relative to UTC.", that's what it was.
0
Gustav BrockCIOCommented:
Sorry. It can be hard to keep track of.

/gustav
0
PeterBaileyUkAuthor Commented:
is it possible to know if a remote locations summertime is in force?
0
Gustav BrockCIOCommented:
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
0
PeterBaileyUkAuthor Commented:
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.
0
Gustav BrockCIOCommented:
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
0
Gustav BrockCIOCommented:
Answer provided.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.