PST => EST Via UTC

[stanl]

Having hijacked the previous thread with several sidebars, felt I would bring this up separately. I daily query reports and pipeline runs from an API and translate the Json in to .csv:  the start and end dates are Json UTC Timezone stamps created by a server out west for data is recorded as PST in UTC format:  [example]

Code Select Expand



startTime   stopTime
2022-10-03T16:34:46.601-07:00   2022-10-03T18:50:49.093-07:00



This has to be converted to DateTime in EST in order to calculate duration in Excel. While it is possible to scrub the cell.Text object, add the 3 hours and return as a cell.Value, I'm sure the -7:00 will be -8:00 in a few weeks.  I found I could do this with PS

Code Select Expand



$pst = "2022-10-03T16:34:46.601-07:00"
$est =[System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId([datetime]::Parse($pst, $Null, [System.Globalization.DateTimeStyles]::AssumeUniversal),  'Eastern Standard Time').ToString('yyyy-MM-dd HH:mm:ss')
$pst
$est         
2022-10-03T16:34:46.601-07:00
2022-10-03 19:34:46



and by having ' System.Globalization.DateTimeStyles to test on any location in the world. Again, won't know if the PST=>EST will work after Nov 6, but Hopeful.


I took a stab at converting the PS to WB. Got stuck on Parse().....   

[JTaylor]

Would my JSON extender do what you need?   It has an option to return datetime values in WinBatch format, which then makes adding timezone adjustments easy.

Jim

[stanl]

Not sure. Already converting Json to .csv and loaded into Excel... so the parsing is done on those specific Excel columns. Then an additional column is added named duration and the difference between end-start times calculated.  Power query can do this and I can use the M-code as a template. I attached my export of TimeZones and tested my PS conversion with a number of them and got valid results. Funny that even though my active bias is  -4 and the Eastern Standard Time is -5, the conversion still works.  Be great for areas that don't observe DST. But before generalizing have to recognize that UTC time can be displayed differently than the Json output.

Code Select Expand



$tz = "Israel Standard Time"
$pst = "2022-10-03T16:34:46.601-07:00"
$est =[System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId([datetime]::Parse($pst, $Null, [System.Globalization.DateTimeStyles]::AssumeUniversal), $tz).ToString('yyyy-MM-dd HH:mm:ss')
$pst
$est       
2022-10-03T16:34:46.601-07:00
2022-10-04 02:34:46


[ChuckC]

A couple of things to consider:

Any time that you rely on converting from one local time in one time zone to a local time in a different time zone, there are concerns about what the DST offset is that is in effect at the time of the conversion and whether it is the same DST offset that was in effect when the source date & time stamp was generated.  This becomes important if you are performing elapsed time calculations.

It is far better to simply ditch time zones entirely and work exclusively with UTC time values and only convert to a local time for display purposes.

Code Select Expand


PS C:\> $dto1 = [System.DateTimeOffset]::Parse("2022-10-03T16:34:46.601-07:00")
PS C:\> $dto2 = [System.DateTimeOffset]::Parse("2022-10-03T21:34:46.601-04:00")
PS C:\> $tspan1 = [TimeSpan]::FromTicks($dto2.UtcTicks - $dto1.UtcTicks)
PS C:\> $tspan1                                                                                                                                                                                                                     Days              : 0
Hours             : 2
Minutes           : 0
Seconds           : 0
Milliseconds      : 0
Ticks             : 72000000000
TotalDays         : 0.0833333333333333
TotalHours        : 2
TotalMinutes      : 120
TotalSeconds      : 7200
TotalMilliseconds : 7200000


PS C:\> $dto1.UtcDateTime

Monday, October 3, 2022 11:34:46 PM

PS C:\> $dto2.UtcDateTime

Tuesday, October 4, 2022 1:34:46 AM


PS C:\> $tzi1 = [TimeZoneInfo]::FindSystemTimeZoneById("Eastern Standard Time")
PS C:\> [TimeZoneInfo]::ConvertTime($dto1, $tzi1)                                                                                                                                                                                   DateTime      : 10/3/2022 7:34:46 PM
UtcDateTime   : 10/3/2022 11:34:46 PM
LocalDateTime : 10/3/2022 7:34:46 PM
Date          : 10/3/2022 12:00:00 AM
Day           : 3
DayOfWeek     : Monday
DayOfYear     : 276
Hour          : 19
Millisecond   : 601
Minute        : 34
Month         : 10
Offset        : -04:00:00
Second        : 46
Ticks         : 638004224866010000
UtcTicks      : 638004368866010000
TimeOfDay     : 19:34:46.6010000
Year          : 2022


PS C:\> [TimeZoneInfo]::ConvertTime($dto2, $tzi1)

DateTime      : 10/3/2022 9:34:46 PM
UtcDateTime   : 10/4/2022 1:34:46 AM
LocalDateTime : 10/3/2022 9:34:46 PM
Date          : 10/3/2022 12:00:00 AM
Day           : 3
DayOfWeek     : Monday
DayOfYear     : 276
Hour          : 21
Millisecond   : 601
Minute        : 34
Month         : 10
Offset        : -04:00:00
Second        : 46
Ticks         : 638004296866010000
UtcTicks      : 638004440866010000
TimeOfDay     : 21:34:46.6010000
Year          : 2022




Note that DateTimeOffset parses the date & time format including the embedded offset values at the end and maintains an internal UTC time [e.g. offset is 00:00] plus the original offset.  Any subtraction involving the UtcTicks value gives you the true elapsed time difference, which can easily represented as a TimeSpan.  Conversions of DateTimeOffset values to DateTime values relative to a specific time zone is easy to perform, too.

[bottomleypotts]

It is far better to simply ditch time zones entirely and work exclusively with UTC time values and only convert to a local time for display purposes.

Seems perfectly logical. As I said elsewhere, it would be nice to have an IntControl in WB where I can say return all date/time functions in UTC time. Or even NON-DST time.

[stanl]

For Chuck:  Understood, but in my example I am not creating times, only interpreting them... offsetting one timezone value to another - using a formula that appears to work with json values.


Now, from the corporate side... this is a 'nice to have'.... it gives more real time preparation for managers to understand in  more real time what is going on with the server. That is all.

[td]

Seems perfectly logical. As I said elsewhere, it would be nice to have an IntControl in WB where I can say return all date/time functions in UTC time. Or even NON-DST time.

That is impossible because there is no way for most of the WIL Time* functions to know the offset of any given time. What is possible is to allow the file time related functions to return UTC because the offset of a file on the file system is known.

[bottomleypotts]

That is impossible because there is no way for most of the WIL Time* functions to know the offset of any given time. What is possible is to allow the file time related functions to return UTC because the offset of a file on the file system is known.

I don't mean to be argumentative, but the TimeDate function checks the registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\IniFileMapping\win.ini[Intl] for how it is going to format results. Surely it is not that much more onerous on winbatch functions to check other registry settings for DST offsets (SYSTEM\CurrentControlSet\Control\TimeZoneInformation[DaylightBias]) or UTC offsets (SYSTEM\CurrentControlSet\Control\TimeZoneInformation[ActiveTimeBias]).

I'm not asking for this function to be built in, because why would I want my code optimized to run the fastest when I can write UDFs to iterate through already converted data to convert it back???!!!???

[stanl]

I don't mean to be argumentative,

Maybe not, but you need to understand the scope of your request. Maybe, I'm wrong but you are asking to change core features of WB to accommodate something that can easily be addressed by a UDF - one of the top features for WB extensibility.  I opened this thread with a specific task, and suggested it could be handled with code. I would expect I could be slammed by Tony or Chuck for not grasping the task correctly, but I did understand the fix would not be embedded in the core as it's own solution. Referring to the thread you posted about dates, you never really set out a specific task you were having problems with, but more a general "why can't it do this"...


In that vein... maybe I or someone else should post... "Why can't WB CLR handle events"?


Good luck with any UDF you write. If it is superior to what has been posted on this or the previous thread, I assure you all in the community will appreciate it. :-\

[bottomleypotts]

Maybe not, but you need to understand the scope of your request.

I fully understand the scope of my request. (It's not actually a request, if it were implemented then awesome, but I'm not asking for it.)

I have no idea what the request entails exactly (It's not actually a request) but I doubt it's a significant rewrite of anything. Check the Int, skip some code here, calculate a time offset there.

Having said that, I'm sure any code I produce would run quicker inside Winbatch, than in a UDF. I would ask Jim to include it in his extender because I know it would run a lot quicker in there also, but I'm not asking for it!!!

In that vein... maybe I or someone else should post... "Why can't WB CLR handle events"?

Seems like a great suggestion actually. I second your request. I would like more documentation about what I can do with WB CLR. I'm just a hacker, not a coder. But I would like to understand more. For now I'm just reading through your posts to gain what little understanding about the topic there is available.

Good luck with any UDF you write. If it is superior to what has been posted on this or the previous thread, I assure you all in the community will appreciate it. :-\

I will post back when done. In the meantime, have appreciated the help you all have provided!!!

[td]

What exactly are you suggesting? The WIL time functions accessing the current time zone in the registry would accomplish nothing. If you are asking for automagic time zone conversion you would still need to provide time zone information which is what the .Net Time assemblies require, a.k.a., CLR requires. And if you know the time zone offset converting from one time zone to another already only requires two lines in a script. If you are asking for a method for converting date/times from time with GMT offset to WIL YMSHMS time format that can be done with a few line script. However, it is something to consider as an enhancement in a future release.

Note that the documentation for the TimeDate is no longer accurate. The reference to win.ini needs to be removed. It dates back to the Win95 days. Also, note that the performance difference between a script implementation of a simple task and encoding it in machine instructions is minimal until you start working with CPU-intensive tasks like loop structures with many iterations.

[stanl]

One final note:


Tony; the comment I made about WIL CLR doing events was not meant to be taken literally but more as a razor per the OPS request. As per my task for this thread. Seems I can make a dropdown on my API request GUI: 
Easter Standard Time
Central Standard Time
Mountain Standard Time
Pacific Standard Time


so one executable to how end user wishes to see UTC converted Timestamps. Now, after Nov6 if the code fails I will try to come up with another plan B.


Tested my 0ne-liner PS conversion and it seems to work.... Happy Holloween 8)

[td]

One final note:
Tony; the comment I made about WIL CLR doing events was not meant to be taken literally but more as a razor per the OPS request.

The answer to the question is that WIL cannot handle .Net framework events because MSFT does not permit it to.

[stanl]

Thread can be closed. I have set up a GUI for implementation after Nov 6. I think it will work with a one-line conversion based on Windows TimeZone enumeration: 


I have looked at 2 different UTC strings I receive in files:

Code Select Expand



dt = "2022-10-04T00:13:28.288-07:00"
dt = "2022-09-29T04:19:55.000Z"       



and both seem to convert as needed with dropdown lookup where

tz = "Eastern Standard Time" or
tz = "Central Standard Time" or
tz = "Mountain Standard Time" or
tz = "Pacific Standard Time" or
tz = "UTC" or


Hoping one executable can be used in offices across US and in Hawaii. Cannot expect WB to magically do everything but it does support figuring it out.


The more WB can work with .NET [regardless of how much one might hate that :o ] the more we learn.

[td]

More or less off topic but out of an abundance of curiosity tried to port your one-liner time zone conversion. Got it to sort of work.  The three obvious limitations are that it is more than one line, the UTC offset is lost, and some precision is lost. The latter two are because the variant date datatype does not support them. For what it is worth:

Code (winbatch) Select Expand

strDt = "2022-10-03T16:34:46.601-07:00"

objDt = ObjectClrNew("System.DateTime", 2022, 1, 1, 1, 1, 1, 0 ) ; Any valid date to keep the constructor happy.
parsedDt = objDt.Parse(strDt)
castDt = ObjectClrType("System.DateTime", parsedDt)
objTzi = ObjectClrNew("System.TimeZoneInfo")
vtDtEastern = objTzi.ConvertTimeBySystemTimeZoneId(castDt, "Eastern Standard Time")

Message("Timezone Conversion Test", TimeFormat(vtDtEastern, "yyyy-MM-ddTHH:mm:ss")) 

[stanl]

As I mentioned originally, I stumbled with the parse() function. Since most of what I do has to be translated to Excel, I do know that Power Query seems to have no issues with translating UTC strings to DataTime... I have written PQ code for Let...In for WB that creates the queries... but found the best PQ could do between to dates [for duration' was TotalMinutes.. accurate but :'(  ugly], so I abandoned in favor of using straight Excel for formatting.


So, I guess the point of all this: The functions are out there, can we find and use them :o

[td]

At least WinBatch users have a quick and dirty way to convert from one time zone to another using the descriptive text of the targeted time zone via WIL CLR hosting. Finding that out was the only motivation here.

[stanl]

At least WinBatch users have a quick and dirty way to convert from one time zone to another using the descriptive text of the targeted time zone via WIL CLR hosting. Finding that out was the only motivation here.

Since, as usual, you did the heavy lifting, I did see your code could be extended for operations like I do with Excel. Maybe the conversion to ObjectType() is redundant, but I have issues with Office 365 in terms of setting cell values as .Text or .Value or .FormulaR1C1 [which seemed to work here]

Code (WINBATCH) Select Expand



tz =  "Eastern Standard Time" ;can be changed for testing
strDt = "2022-10-03T16:34:46.601-07:00"
objDt = ObjectClrNew("System.DateTime", 2022, 1, 1, 1, 1, 1, 0 ) ; Any valid date to keep the constructor happy.
parsedDt = objDt.Parse(strDt)
castDt = ObjectClrType("System.DateTime", parsedDt)
objTzi = ObjectClrNew("System.TimeZoneInfo")
vtDtEastern = objTzi.ConvertTimeBySystemTimeZoneId(castDt, tz)
cvtDate = ObjectType("DATE",TimeFormat(vtDtEastern, "MM/dd/yyyy HH:mm:ss"))
;just display conversion
Display(2,"Converted Date",cvtDate)
;place it into Excel
oXL = CreateObject("Excel.Application")
If oXL == 0 Then Exit
oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.WorkBooks.Add()
oWS = oXL.ActiveWorkBook.WorkSheets(1)
oWS.Activate()
oWS.Cells(1,1).FormulaR1C1 = cvtDate
oWS=0
oXL=0
Exit


[td]

You are correct to question the need for

Code (winbatch) Select Expand

cvtDate = ObjectType("DATE",TimeFormat(vtDtEastern, "MM/dd/yyyy HH:mm:ss"))
as variant date datatypes are the same no matter the source. In other words, the line is not necessary.