UTC Time Conversion

Started by Jeff, June 19, 2018, 11:36:35 AM

Previous topic - Next topic

Jeff

Does anyone have an example of converting UTC to the current time of the computer?

For example extracting a windows event log using sample event log dumps found on winbatchs support site. The problem is, it's using UTC format.

Jeff

td

It not the hard to do but the approach depends on the date format as it is present in your script.   For example, once you get the date into the WIL YYYY:MM:DD:HH:MM:SS format you can simply lookup the timezone bias in the registry and use the WIL time functions to add or subtract the indicated number of hours.   

There are multiple examples in the Tech Database the demonstrate this and other approaches.  Try searching on UTC or Timezone to find the one that best fits your specifics.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

kdmoyers

Here's getting the offset from the registry:
Code (winbatch) Select
  s = "System\CurrentControlSet\control\TimeZoneInformation[ActiveTimeBias]"
  ActiveTimeBias = RegQueryDword(@REGMACHINE, s)
  GMTBiasHours = - (ActiveTimeBias / 60)
The mind is everything; What you think, you become.

stanl

Maybe I'm wrong but I thought Windows Log timestamps were converted to current computer time, or does the OP have more of an issue with converting the UTC timestamp string into something WB can handle?

td

Event log times are time zone adjusted when viewed using the Event Viewer utility but I don't recall if the that is the case when extracting events using WMI, for example.

Here is a hack that adjusts a text date and time for the time zone using Kirby's registry key:

Code (winbatch) Select
YmdDate = ObjectType('DATE', '06/21/2018 1:30 AM')
strValue = "System\CurrentControlSet\control\TimeZoneInformation[ActiveTimeBias]"
nTimeBias = RegQueryDword(@REGMACHINE, strValue)
if nTimeBias > 0
   AdjDate = TimeAdd(YmdDate,'00:00:00:00:':nTimeBias:':00')
elseif nTimeBias < 0
   nTimeBias = Abs(nTimeBias)
   AdjDate = TimeSubTract(YmdDate,'00:00:00:00:':nTimeBias:':00')
else
   AdjDate = YmdDate
endif
Message(YmdDate, AdjDate)


[edit] Forgot to add the call to the Abs function in the original version of the example...
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

My muddled brain was thinking back to doing this with LogParser, then discovered those scripts used the TO_LOCALTIME function. :o :o :o

td

Muddling about a bit myself.  The previously posted script is still not correct because the formula for using the timezone bias is "UTC = local time + bias". So to convert from UTC to local time you need to take the negative of the bias.

Code (winbatch) Select
YmdDate = ObjectType('DATE', '06/22/2018 1:30 AM')
strValue = "System\CurrentControlSet\control\TimeZoneInformation[ActiveTimeBias]"
nTimeBias = RegQueryDword(@REGMACHINE, strValue)
if nTimeBias > 0
   AdjDate = TimeSubTract(YmdDate,'00:00:00:00:':nTimeBias:':00')
elseif nTimeBias < 0
   nTimeBias = Abs(nTimeBias)
   AdjDate = TimeAdd(YmdDate,'00:00:00:00:':nTimeBias:':00')
else
   AdjDate = YmdDate
endif
Message('UTC to Local Time','UTC: ':YmdDate:@crlf:'Local Time: ':AdjDate)


"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

kdmoyers

<< So to convert from UTC to local time you need to take the negative of the bias. >>
Thanks for this...   quite useful!
The mind is everything; What you think, you become.

stanl

Quote from: kdmoyers on June 22, 2018, 09:13:16 AM
<< So to convert from UTC to local time you need to take the negative of the bias. >>
Thanks for this...   quite useful!

Too bad you couldn't combine timeadd/subtract into something like
Code (WINBATCH) Select

TimeSet(YmdDate,'00:00:00:00:':nTimeBias*-1:':00')

stanl

Quote from: stanl on June 22, 2018, 03:11:32 AM
My muddled brain was thinking back to doing this with LogParser, then discovered those scripts used the TO_LOCALTIME function. :o :o :o

Did some more thinking about this. Logparser

SELECT timegenerated, timewritten  [from the event logs] returns local computer time with no conversion needed.

SYSTEM_UTCOFFSET() will return the correct bias

SYSTEM_TIMESTAMP()  always returns UTC

TO_LOCALTIME(SYSTEM_TIMESTAMP().... [any UTC timestamp]) will convert UTC to local time