Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    MsAxes is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    69

    How to return current time for specific time zones?

    Hello - I have a table named Time Zones with two rows.

    TimeZone UTC
    EST -5
    GMT 0

    All I need is to return the current time for those two time zones. I added these fields:

    Code:
    CurrentTime: Time()+[UTC]

    Code:
    CurrTime: DateAdd('h',5+[UTC],Time())
    Neither returns the correct time for those time zones. I'm not sure what else to try. I haven't found too many examples with what I need. Do the UTC codes change during day light savings time?

    Thoughts?
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,338
    You want to adjust your local time to GMT and EST? What time zone are you in?

    UTC doesn't utilize daylight saving but most of the United States does. So the difference will change depending on time of year.
    Review
    http://www.cpearson.com/excel/LocalAndGMTTimes.htm
    https://stackoverflow.com/questions/...-time-to-local
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    10,325
    have you tried

    CurrTime: DateAdd('h',[UTC],Time())

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    10,325
    have you tried

    CurrTime: DateAdd('h',[UTC],Time())

    Do the UTC codes change during day light savings time?
    In the UK you would use GMT and BST (British Summer Time), other countries will have something similar if they make a summer (or perhaps winter) adjustment - Europe uses EST. Some European countries will be on GMT, others on a different time.

    BST and EST come into effect on the last Sunday of March and are switched back on last Sunday of October So can easily be calculated if required. Time is usually set to change for BST/EST automatically (it is or was a Windows setting) so if you are in UK/Europe you should be able to ignore. I think the US changes first Sunday in April/November so if you need that adjustment, there are two weeks in the year where time is out of adjustment with UK

  5. #5
    MsAxes is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    69
    My DB just needs to capture the time for those two time zones into a table. I am in central time.

    I did read the stackoverflow response but I didn't know that VBA had to be involved plus if I'm being honest, wasn't sure what to do after I copied it.

    Yes Ajax, I did give your response a try but still no luck.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    10,325
    what does ' but still no luck.' mean?

    Neither returns the correct time for those time zones.
    so what is it returning and what is correct?

  7. #7
    MsAxes is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    69
    time was being returned but not for those time zones.

    So which time zone should I be using for now? GMT or BST? If it helps to be specific, I need London time.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    10,325
    So I ask what time are you expecting and what time are you getting - you tell me 'time was being returned but not for those time zones.'

    Don't think I am any further forward in helping you

    I was expecting an answer along the lines of

    I enter a time in BST of 1pm and the result for EST is 2am, I was expecting 6pm



  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,458
    Attached is one solution which I think is far easier to understand than what I think you are trying to do.

    First of all, I've renamed your table TimeZones (no space) and added an extra boolean field LocalZone & an extra record for Central Time (CT)
    I am based in the UK so I've ticked GMT as LocalZone. You need to change that to CT.
    Click image for larger version. 

Name:	Capture1.PNG 
Views:	15 
Size:	7.4 KB 
ID:	46607


    Next I've created a standard module with these two functions
    Code:
    Function GetLocalZoneUTC() As Integer   'get UTC value for local time zone
        GetLocalZoneUTC = Nz(DLookup("UTC", "TimeZones", "LocalZone=True"), 0)
    End Function
    
    Function GetTimeZoneAdjustment(intID As Integer) As Integer
        'calculate difference in time for specified time zone
        GetTimeZoneAdjustment = DLookup("UTC", "TimeZones", "ID =" & intID) - GetLocalZoneUTC()
    End Function
    Then I used these in a query:
    Code:
    SELECT TimeZone, Desc, DateAdd('h',GetTimeZoneAdjustment([ID]),Time()) AS LocalTimeFROM TimeZones;
    Finally I used that query as the record source for this form:
    Click image for larger version. 

Name:	Capture.PNG 
Views:	21 
Size:	12.0 KB 
ID:	46602

    You can add as many time zones as you wish. However, as written the code doesn't allow for
    a) Time Zones which aren't integer values compared to UTC
    b) Daylight Saving Times

    If you need to deal with either of those I can suggest how to modify this

    Hope that helps
    Attached Files Attached Files
    Colin, MVP 2022, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    MsAxes is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    69
    I managed to make the changes and run the query but I think the time returned doesn't match because I'm lost here:

    "I am based in the UK so I've ticked GMT as LocalZone. You need to change that to CT."

    and yes, please, this would need to change when daylight savings time does.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,458
    Apologies. One of the screenshots and the attachment were missing from my last post.
    I've now added both of these.
    Have a look and let me know whether it does what you want (apart from daylight savings).
    We'll come back to managing that afterwards!
    Colin, MVP 2022, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    MsAxes is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    69
    This works perfect, all that is left is to account for daylight savings time.

    I can't thank you enough

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,458
    OK - I'm on the case! Will get back to you tomorrow ....
    Last edited by isladogs; 11-10-2021 at 07:49 PM.
    Colin, MVP 2022, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,458
    Sorry about the delay. I spent some time on this so I could test it properly.
    I hope to add a video related to this on my new you tube channel Using Access with Isladogs - YouTube... hopefully later today
    If you would like to subscribe, you will be notified whenever any new content is added

    Version 2.1 attached including adjustments for daylight savings. I've added 2 date fields DSTStart & DSTEnd to the table plus an extra record for NZ for testing purposes.
    Click image for larger version. 

Name:	Capture0.PNG 
Views:	11 
Size:	15.3 KB 
ID:	46624

    I've also modified the underlying functions and the form to allow for daylight savings. This is using the current date with GMT set as the default local zone. Currently only NZ is on daylight savings.
    Click image for larger version. 

Name:	Capture1.PNG 
Views:	11 
Size:	24.2 KB 
ID:	46620

    and here with CT as the local zone
    Click image for larger version. 

Name:	Capture2.PNG 
Views:	11 
Size:	24.1 KB 
ID:	46621
    I tested changing the date/time in Windows settings e.g. 4 Nov where the USA was also on daylight savings but not the UK

    Click image for larger version. 

Name:	Capture3.PNG 
Views:	11 
Size:	24.6 KB 
ID:	46622

    Where appropriate, the form shows where zones are on a different day.

    You will need to manually enter the DSTStart & DSTEnd dates each year. Easy to find online e.g. World time zones by country list, UTC (GMT) standard (countries-ofthe-world.com)
    Or you could use a separate table for this and include those values for several years ahead if you prefer.
    Attached Files Attached Files
    Last edited by isladogs; 11-11-2021 at 06:56 AM. Reason: Updated screenshots & attached file
    Colin, MVP 2022, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,458
    A further update. This time to allow for countries with no daylight savings e.g. Egypt and countries with non integer times zones e.g. Iran = UTC+3.5
    You can also now select the default zone from the form by double clicking the TimeZone field.
    Click image for larger version. 

Name:	Capture4.PNG 
Views:	11 
Size:	35.1 KB 
ID:	46635

    Hopefully now finished
    Attached Files Attached Files
    Colin, MVP 2022, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 12-09-2019, 05:34 AM
  2. Replies: 8
    Last Post: 07-18-2019, 01:36 AM
  3. Replies: 6
    Last Post: 08-17-2016, 07:13 AM
  4. Replies: 2
    Last Post: 01-16-2015, 04:22 PM
  5. Replies: 1
    Last Post: 08-14-2012, 03:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums