Sign in to follow this  
Followers 0
mjolnirmarkiv

Converting time zone to UTC

9 posts in this topic

Hello there,

I need to convert time from CETCEST to UTC, so naturally I want to take into account that extra hour depending on if summer time is active or not, e.g. 16:00 CET > 15:00 UTC, but from April to October (or whatevere the transition dates are) I need to return 16:00 CEST > 14:00 UTC. My local time is different from CE(S)T, so local to UTC conversion is not an option.

Any suggestions what is the easiest way to do it? Probably sending request to NTP server might help to get the correct offset, but I'm not entirely sure how to do it...

$http = ObjCreate ("winhttp.winhttprequest.5.1")
$http.Open("GET", "http://pool.ntp.org/", false)
$http.Send()
MsgBox (0, "", $http.GetResponseHeader("Date"))

Thanks!

Share this post


Link to post
Share on other sites



If you're going to have to process random timestamps from random places, then the problem is not as simple as you'd like it to be. DST conventions and even timezone boundaries have changed significantly over time and are going to change in the future as well.

That's why it's terribly difficult to convert past dates reliably.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

I suggest to have a look at the Date UDF that comes with AutoIt.

Functions _Date_Time_SystemTimeToTzSpecificLocalTime and _Date_Time_TzSpecificLocalTimeToSystemTime should do what you want.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

as jhcd states, time conversion which involves DST may get quite mischievous. as to water's reference, one may find it is a bit more difficult than usual, due to the calling script being required to directly use some DLL structs and pointers. to make this a bit simpler, i made> this UDF. it does not directly support non-local timezones, but that (i believe) should not be too hard to work out.

Share this post


Link to post
Share on other sites

The actual issue with DST is that it has changed (and keeps on changing here and there) a lot over time in various areas. Thanks to political decisions made by people not realizing the consequences, you'd have to maintain a huge table of changes for locations/areas and dates all over the world to perform an accurate conversion. Maybe such table is available somewhere in the netspace but I've no idea where.

Some timezones also have seen their boundaries change in several occurences but I seem to recall that inhabited locations involved where only few and mostly small islands. I may be wrong on this.

In all cases a general purpose correct conversion is anything but simple.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Maybe such table is available somewhere in the netspace but I've no idea where.

 

surprisingly enough, it's in the Windows registry. you can browse it in a decently-presented manner using the TZedit utility. it is constantly updated by MS via Windows Update. it may be not properly updated, for any or all of the reasons mentioned above; but for the most part, if your Windows is updated, it can be considered quite a reliable source of information. after all, it's what Windows itself is using. when you change your time zone, Windows does not load the new timezone data - i.e. offset, DST transitions dates, etc - from the web. Windows relies on what is stored in its own registry for that.

Share this post


Link to post
Share on other sites

You misunderstand the problem: the question is not what time shift if any exist now and where you live, but determine what was (or will be) the time shift at a particular date and a particular location on Earth.

The (huge) IANA database is not garanteed to be complete nor correct and only goes back to 1970 and only for major towns. It certainly isn't going to fit in a Windows registry anytime soon...

Also latest time I had to check, Windows didn't maintain such data per user, only machine-wide. This was (is?) problematic for accounts used remotely.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

Thanks everyone for the input.

With a little research on how NST servers function, I see now that they return only UTC time, no offsets, no anything but UTC. In other words there's no way to say for sure what is offset for specific time zone.

It seems there's no better solution in converting CE(S)T to UTC but to change bias depending on the date being converted by myself (-60 now or -120 during summer in my case) and hope some bureaucrat or whoever responsible for that won't change DST offset or time of transition.

Smth like this, even though _Date_Time_* functions are not necessity here, simple check for is date between last Sunday of March and last Sunday of October (for CEST) > increase bias to -120 would do the trick.

#include <Array.au3>
#include <Date.au3>

Global $date, $time
_DateTimeSplit ("2014/11/15 15:00", $date, $time)

Global $cet_tzinfo = DllStructCreate ($tagTIME_ZONE_INFORMATION)
DllStructSetData ($cet_tzinfo, "Bias", -60)
;DllStructSetData ($cet_tzinfo, "StdName", "Western Europe (winter)")
;DllStructSetData ($cet_tzinfo, "StdDate", _Date_Time_EncodeSystemTime (10, 5, 0, 3, 0, 0))
;DllStructSetData ($cet_tzinfo, "StdBias", 0)
;DllStructSetData ($cet_tzinfo, "DayName", "Western Europe (summer)")
;DllStructSetData ($cet_tzinfo, "DayDate", _Date_Time_EncodeSystemTime (3, 5, 0, 2, 0, 0))
;DllStructSetData ($cet_tzinfo, "DayBias", -60)

Global $cet = _Date_Time_EncodeSystemTime ($date[2], $date[3], $date[1], $time[1], $time[2], $time[3])
Global $utc = _Date_Time_TzSpecificLocalTimeToSystemTime (DllStructGetPtr ($cet), DllStructGetPtr ($cet_tzinfo))

MsgBox (0, "", _Date_Time_SystemTimeToDateTimeStr ($utc, 1))
Edited by mjolnirmarkiv

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

surprisingly enough, it's in the Windows registry. you can browse it in a decently-presented manner using the TZedit utility. it is constantly updated by MS via Windows Update. it may be not properly updated, for any or all of the reasons mentioned above; but for the most part, if your Windows is updated, it can be considered quite a reliable source of information. after all, it's what Windows itself is using. when you change your time zone, Windows does not load the new timezone data - i.e. offset, DST transitions dates, etc - from the web. Windows relies on what is stored in its own registry for that.

Speaking about registry...

That's not a 100% guarantee, but smth like this I believe? This one correctly converts to UTC depending on the date (within DST -2 hours, otherwise -1 hour).

#include <Array.au3>
#include <Date.au3>

$s = DateTimeToUTC ("2014/10/26 12:00", "Central European Standard Time")
MsgBox (0, @error, $s)

Func DateTimeToUTC ($p_date_time, $p_time_zone)
  Local $bin = RegRead ("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\" & $p_time_zone, "TZI")
  If @error Then Return SetError (1, 0, $p_date_time)
  If not IsBinary ($bin) or BinaryLen ($bin) <> 44 Then Return SetError (2, 0, $p_date_time)

  Local $tz_info = DllStructCreate ($tagTIME_ZONE_INFORMATION)
  DllStructSetData ($tz_info, "Bias", BinaryMid ($bin, 1, 4))
  DllStructSetData ($tz_info, "StdBias", BinaryMid ($bin, 5, 4))
  DllStructSetData ($tz_info, "DayBias", BinaryMid ($bin, 9, 4))
  $n = 0
  For $i = 13 To 27 Step 2
    $n += 1
    DllStructSetData ($tz_info, "StdDate", BinaryMid ($bin, $i, 2), $n)
  Next
  $n = 0
  For $i = 29 To 43 Step 2
    $n += 1
    DllStructSetData ($tz_info, "DayDate", BinaryMid ($bin, $i, 2), $n)
  Next

  Local $date, $time
  _DateTimeSplit ($p_date_time, $date, $time)
  Local $date_time = _Date_Time_EncodeSystemTime ($date[2], $date[3], $date[1], $time[1], $time[2], $time[3])
  Local $utc = _Date_Time_TzSpecificLocalTimeToSystemTime (DllStructGetPtr ($date_time), DllStructGetPtr ($tz_info))

  Return _Date_Time_SystemTimeToDateTimeStr ($utc, 1)
EndFunc

That was PITA to wrap my head around dll structures and time conversion I must say, more complicated than I thought.

Edited by mjolnirmarkiv

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0