WinBatch Technical Support Forum

All Things WinBatch => WinBatch => Topic started by: stanl on February 01, 2021, 11:22:24 am

Title: DOM Extender: Json dates are evil
Post by: stanl on February 01, 2021, 11:22:24 am

Jim;

I am creating Json but the dates are .NET serialized [sample file below based on query Select Top 10 * FROM DOW" from a local Access Table, saved to .json file - data below.


Powershell easily reports dates in m/d/yyyy format, but if I load the json into your extender it will be returned as original string [something I probably have to apologize for after other data/scripts I uploaded illustrated issues with numerics].


Be interested in how Newtonsoft handles these is either Tony or Chuck weigh in... basically Unix TimeStamps :'(


Code: [Select]

[
    {
        "Date":  "\/Date(1316404800000)\/",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Mon",
        "EndDate":  "\/Date(1316923200000)\/",
        "Range":  "Sep19-Sep25"
    },
    {
        "Date":  "\/Date(1316491200000)\/",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Tue",
        "EndDate":  "\/Date(1317009600000)\/",
        "Range":  "Sep20-Sep26"
    },
    {
        "Date":  "\/Date(1316577600000)\/",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Wed",
        "EndDate":  "\/Date(1317096000000)\/",
        "Range":  "Sep21-Sep27"
    },
    {
        "Date":  "\/Date(1316664000000)\/",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Thu",
        "EndDate":  "\/Date(1317182400000)\/",
        "Range":  "Sep22-Sep28"
    },
    {
        "Date":  "\/Date(1316750400000)\/",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Fri",
        "EndDate":  "\/Date(1317268800000)\/",
        "Range":  "Sep23-Sep29"
    },
    {
        "Date":  "\/Date(1316836800000)\/",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Sat",
        "EndDate":  "\/Date(1317355200000)\/",
        "Range":  "Sep24-Sep30"
    },
    {
        "Date":  "\/Date(1316923200000)\/",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Sun",
        "EndDate":  "\/Date(1317441600000)\/",
        "Range":  "Sep25-Oct01"
    },
    {
        "Date":  "\/Date(1317009600000)\/",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Mon",
        "EndDate":  "\/Date(1317528000000)\/",
        "Range":  "Sep26-Oct02"
    },
    {
        "Date":  "\/Date(1317096000000)\/",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Tue",
        "EndDate":  "\/Date(1317614400000)\/",
        "Range":  "Sep27-Oct03"
    },
    {
        "Date":  "\/Date(1317182400000)\/",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Wed",
        "EndDate":  "\/Date(1317700800000)\/",
        "Range":  "Sep28-Oct04"
    }
]

Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 01, 2021, 11:40:05 am
Is this a standard Date format in JSON?

    "\/Date(1316404800000)\/"


I will see what I can do.   In the middle of resolving an unrelated issue right now so may be tomorrow before I can get to it.

Jim
Title: Re: DOM Extender: Json dates are evil
Post by: stanl on February 01, 2021, 02:19:14 pm
Quote from: JTaylor on February 01, 2021, 11:40:05 am
Is this a standard Date format in JSON?

    "\/Date(1316404800000)\/"


There is no Standard, AFAIK!  Did not the use of the word Evil in the original post give you a hint ::)
Title: Re: DOM Extender: Json dates are evil
Post by: td on February 01, 2021, 02:38:07 pm
I believe there is a JSON standard and my understanding is that JS date objects are not supposed to appear as JSON data. But then what do I know besides what I read...
Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 01, 2021, 04:41:40 pm
I expanded dmjGetType() to return UTC or Time_t  when it thinks it matches that format.

2012-04-23T18:25:43.511Z
/Date(1316404800000)/     


    http://www.jtdata.com/anonymous/domdata.zip

Have more plans but family time right now :)

Jim
Title: Re: DOM Extender: Json dates are evil
Post by: td on February 01, 2021, 05:38:00 pm
The JSON RFC states:

4.3.  Time and Date Handling

   Protocols often contain data items that are designed to contain
   timestamps or time durations.  It is RECOMMENDED that all such data
   items be expressed as string values in ISO 8601 format, as specified
   in [RFC3339], with the additional restrictions that uppercase rather
   than lowercase letters be used, that the timezone be included not
   defaulted, and that optional trailing seconds be included even when
   their value is "00".  It is also RECOMMENDED that all data items
   containing time durations conform to the "duration" production in
   Appendix A of RFC 3339, with the same additional restrictions.

ISO 8601 is described here:

https://en.wikipedia.org/wiki/ISO_8601 (https://en.wikipedia.org/wiki/ISO_8601)

Which is pretty much what you have.
Title: Re: DOM Extender: Json dates are evil
Post by: td on February 01, 2021, 06:08:46 pm
Also since javascipt's Date object's millisecond constructor uses Unix time, I imagine the JSON stuff above is the same - milliseconds from  Jan 1, 1970 UTC+0.
Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 01, 2021, 08:33:05 pm
That is how I took it.  Trying to convert that to a SYSTEMTIME structure but beating my head against the wall so far.  Got the UTC stuff but getting a year of 1601 from the milliseconds for todays date.   Think I need some sleep and then it will be clear :)

Jim
Title: Re: DOM Extender: Json dates are evil
Post by: stanl on February 02, 2021, 03:01:28 am
Quote from: JTaylor on February 01, 2021, 04:41:40 pm
I expanded dmjGetType() to return UTC or Time_t  when it thinks it matches that format.

2012-04-23T18:25:43.511Z
/Date(1316404800000)/     



Should be 9/19/2011 for Date and 9/25/2011 for EndDate - the last column 'Sep19-Sep25' is just a calculation between the 2. Hope that helps.  Data was originally in Excel; Imported to Access; loaded with Ace Provider; read into Data Table then exported as Json.


As for Tony's comments - I should have worded 'no acceptable standard' - since Json is text you can use a variety of formats for a date. I would assume since the data was exported from a data table, which is typed, a TimeStamp would be converted to a Json object type - calculated either as a Unix Timestamp or from Gregorian Calendar (hence the 1601 date you came up with).


[EDIT] It is Unix Time.
Code: Winbatch

AddExtender("WWHUG44I.DLL", 0, "WWHUG64I.DLL")
t1 = "1970:01:01:00:00:00"
num1 = "1316404800000"
num2 = "1000"
x= huge_Divide(num1,num2)
Message(t1,TimeAdd(t1,"0000:00:00:00:00:%x%") )


 

Title: Re: DOM Extender: Json dates are evil
Post by: td on February 02, 2021, 08:10:10 am
The JSON RFC recommends using ISO rules for date representation.  RFC requirement words have specific meanings. In the case of RECOMMENDED:

The RFC for RFCs states:
  This word, or the adjective "RECOMMENDED", mean that there
   may exist valid reasons in particular circumstances to ignore a
   particular item, but the full implications must be understood and
   carefully weighed before choosing a different course.
Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 02, 2021, 09:46:59 am
On the off-chance it is helpful.   You can tell the dmjGetValue() function to return YmdHms format of the date, assuming the Type returns as UTC or Time_t.

      http://www.jtdata.com/anonymous/domdata.zip

Jim
Title: Re: DOM Extender: Json dates are evil
Post by: stanl on February 02, 2021, 11:26:19 am
Quote from: td on February 02, 2021, 08:10:10 am
The JSON RFC recommends using ISO rules for date representation.  RFC requirement words have specific meanings. In the case of RECOMMENDED:

The RFC for RFCs states:
  This word, or the adjective "RECOMMENDED", mean that there
   may exist valid reasons in particular circumstances to ignore a
   particular item, but the full implications must be understood and
   carefully weighed before choosing a different course.


wow, same as CDC rules about masks.
Title: Re: DOM Extender: Json dates are evil
Post by: ChuckC on February 02, 2021, 12:21:31 pm
Didn't see this until today... ironically, I had my head down in the trench with my nose to the grindstone, so to speak, dealing with dates in JSON.

Tony hit the key part with the reference to ISO 8601.  This corresponds to the .NET "RoundTrip" format for DATETIME values, with particular focus on using the "O" or "o" format string when calling the ToString() method, and is the most desired format for a date time string that is passed to the Parse() / TryParse() methods on the DATETIME class.

This corresponds to a custom format string of "yyyy-MM-ddTHH:mm:ss.fffffffK".  If necessary, refer to the following:

https://docs.microsoft.com/en-us/dotnet/standard/base-types/how-to-round-trip-date-and-time-values

https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings


Regarding issues with Win32 API functions that involve date & time values in SYSTEMTIME or FILETIME structures, refer to the following:

https://docs.microsoft.com/en-us/windows/win32/api/minwinbase/ns-minwinbase-systemtime

https://docs.microsoft.com/en-us/windows/win32/api/minwinbase/ns-minwinbase-filetime


A SYSTEMTIME structure contains the time broken down into individual fields that are convenient for displaying in a human-readable format.  A FILETIME structure is nothing more than an older method of storing & representing a 64-bit integer value; in modern usage, a c# "ulong" or C++ "unsigned long long" is equivalent.


A .NET DATETIME stores the date & time in ticks, which are 100 nanosecond intervals, along with a time zone where UTC time is the zulu/zero time zone.  The base epoch for DATETIME values is Midnight on 1/1/0001, with each tick counting up forward in time.  Calling the ToBinary() method returns a signed 64-bit integer value representing the date & time value.

The SYSTEMTIME & FILETIME structures store date & time values in 100ns increments, too, but their epoch is Midnight on 1/1/1601.


The JSON text with the date & time value "\/Date(1316404800000)\/" is a legacy format used by Microsoft prior to .NET their adoption of ISO 8601.

https://www.newtonsoft.com/json/help/html/DatesInJSON.htm

https://davidsekar.com/javascript/converting-json-date-string-date-to-date-object


If the assumption is that the date & time value in the JSON example is a time_t value with millisecond precision, then the following snippet of c# should convert it:

var datetime1 = DATETIME.new(1970,1,1).AddMilliseconds(131640480000)

Which gives a DATETIME value of "Monday, March 4, 1974 2:48:00 PM".

The online time_t converter site, https://www.onlineconversion.com/unix_time.htm, gives the date & time value of "Mon, 04 Mar 1974 14:48:00 GMT" when (131640480000 / 1000) as an input value of seconds.

Title: Re: DOM Extender: Json dates are evil
Post by: td on February 02, 2021, 01:45:38 pm
Quote from: stanl on February 02, 2021, 11:26:19 am

wow, same as CDC rules about masks.

Kinda what sometimes happens when the goal is to make something "simple" but "flexible".

[edit] Maybe JSON should come with a health warning requirement in some kind of mandatory head.
Title: Re: DOM Extender: Json dates are evil
Post by: td on February 02, 2021, 03:02:14 pm
Quote from: ChuckC on February 02, 2021, 12:21:31 pm

The JSON text with the date & time value "\/Date(1316404800000)\/" is a legacy format used by Microsoft prior to .NET their adoption of ISO 8601.

https://www.newtonsoft.com/json/help/html/DatesInJSON.htm

https://davidsekar.com/javascript/converting-json-date-string-date-to-date-object


If the assumption is that the date & time value in the JSON example is a time_t value with millisecond precision, then the following snippet of c# should convert it:


Thanks, Chuck. I had forgotten about the MSFT basis of that time notation.  Maybe I will remember this the next time I encounter it.
Title: Re: DOM Extender: Json dates are evil
Post by: ChuckC on February 02, 2021, 07:15:44 pm
Oh, yeah, I realized that I forgot to call out the Unix/Linux time_t epoch as being Midnight, 1/1/1970, for a time_t value of 0.  Sometime post-Y2K, time_t was expanded from a 32-bit integer to a 64-bit integer.

Epochalypse / Y2K38:

https://en.wikipedia.org/wiki/Year_2038_problem#:~:text=The%20Year%202038%20problem%20(also,UTC%20on%2019%20January%202038.

AFAIK, time_t precision is still formally defined with a unit of seconds in the POSIX standards, although there are are a variety of alternate time-related API functions in various operating systems that allow for getting a time_t type of value with millisecond precision.

Title: Re: DOM Extender: Json dates are evil
Post by: td on February 02, 2021, 07:34:36 pm
Only tangentially related but I like to use Windows' 100 nanosecond Performance Counters, for example, QueryPerformanceCounter. They are handy when time intervals are so brief that milliseconds contain more random noise than useful information.
Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 02, 2021, 07:47:52 pm
I used the nanosecond stuff for the conversion of time in my extender.  For some odd reason though things would go wonky if I divided milliseconds by 1000 and then tried converting it.   Every time I ran the script I would get different responses often with years in the 5,000s.  All the same code other than dividing by 1000 before I read it in.   Finally had to just remove 3 digits off the end and put them in milliseconds and convert the seconds.

Jim
Title: Re: DOM Extender: Json dates are evil
Post by: td on February 02, 2021, 11:02:59 pm
Not sure which Win32/Drive API functions you were using so this may be completely off base but I have had good luck using the Win32/Drive SDK functions that work in 100 nanosecond increments. The timing is so fine that it varies from machine to machine so you have to get a frequency to seed the other function calls with. Generally, I use them for timing but as Chuck mentioned, the SYSTEMTIME and FILETIME structures store info in 100ns increments.  WinBatch makes use of both structures and associated functions internally.
Title: Re: DOM Extender: Json dates are evil
Post by: stanl on February 03, 2021, 03:37:12 am
Quote from: ChuckC on February 02, 2021, 12:21:31 pm

If the assumption is that the date & time value in the JSON example is a time_t value with millisecond precision, then the following snippet of c# should convert it:

var datetime1 = DATETIME.new(1970,1,1).AddMilliseconds(131640480000)

Which gives a DATETIME value of "Monday, March 4, 1974 2:48:00 PM".

The online time_t converter site, https://www.onlineconversion.com/unix_time.htm (https://www.onlineconversion.com/unix_time.htm), gives the date & time value of "Mon, 04 Mar 1974 14:48:00 GMT" when (131640480000 / 1000) as an input value of seconds.


Chuck;


The actual datetime from raw Excel date was 9/19/2011 - which my huge math snippet calculated as 2011:09:19:04:00:00 and PS ConvertFrom-Json as 9/19/2011; the .NET Table the Json derived from created from Ace Provider had 9/19/2011 12:00 am.


Think I'll try to adjust the Ace Provider SQL to accept the date fields as UTC format and see what I get for Json.
Title: Re: DOM Extender: Json dates are evil
Post by: stanl on February 03, 2021, 04:14:17 am
Jim;


Code: [Select]

[
    {
        "Date":  "2011-09-19T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Mon",
        "EndDate":  "2011-09-25T12:00:00:00",
        "Range":  "Sep19-Sep25"
    },
    {
        "Date":  "2011-09-20T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Tue",
        "EndDate":  "2011-09-26T12:00:00:00",
        "Range":  "Sep20-Sep26"
    },
    {
        "Date":  "2011-09-21T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Wed",
        "EndDate":  "2011-09-27T12:00:00:00",
        "Range":  "Sep21-Sep27"
    },
    {
        "Date":  "2011-09-22T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Thu",
        "EndDate":  "2011-09-28T12:00:00:00",
        "Range":  "Sep22-Sep28"
    },
    {
        "Date":  "2011-09-23T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Fri",
        "EndDate":  "2011-09-29T12:00:00:00",
        "Range":  "Sep23-Sep29"
    },
    {
        "Date":  "2011-09-24T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Sat",
        "EndDate":  "2011-09-30T12:00:00:00",
        "Range":  "Sep24-Sep30"
    },
    {
        "Date":  "2011-09-25T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Sun",
        "EndDate":  "2011-10-01T12:00:00:00",
        "Range":  "Sep25-Oct01"
    },
    {
        "Date":  "2011-09-26T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Mon",
        "EndDate":  "2011-10-02T12:00:00:00",
        "Range":  "Sep26-Oct02"
    },
    {
        "Date":  "2011-09-27T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Tue",
        "EndDate":  "2011-10-03T12:00:00:00",
        "Range":  "Sep27-Oct03"
    },
    {
        "Date":  "2011-09-28T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Wed",
        "EndDate":  "2011-10-04T12:00:00:00",
        "Range":  "Sep28-Oct04"
    }
]
Title: Re: DOM Extender: Json dates are evil
Post by: stanl on February 03, 2021, 05:50:38 am
and for all.... doesn't seem to matter but PS always comes up with 4am [at least on my laptop]


Code: [Select]

$fmt = "MM/dd/yyyy hh:mm:ss tt"
$d = '2015-07-03 10:58:00'
([DateTime] $d).Date.ToUniversalTime().ToString($fmt)
Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 03, 2021, 06:54:03 am
Just to make sure I read things correctly....since these dates don't have a Z on the end does this mean they are local time and not GMT?    Currently my extender checks for the Z and assume GMT as well as reporting as a date.

Jim

Quote from: stanl on February 03, 2021, 04:14:17 am
Jim;


Code: [Select]

[
    {
        "Date":  "2011-09-19T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Mon",
        "EndDate":  "2011-09-25T12:00:00:00",
        "Range":  "Sep19-Sep25"
    },
    {
        "Date":  "2011-09-20T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Tue",
        "EndDate":  "2011-09-26T12:00:00:00",
        "Range":  "Sep20-Sep26"
    },
    {
        "Date":  "2011-09-21T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Wed",
        "EndDate":  "2011-09-27T12:00:00:00",
        "Range":  "Sep21-Sep27"
    },
    {
        "Date":  "2011-09-22T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Thu",
        "EndDate":  "2011-09-28T12:00:00:00",
        "Range":  "Sep22-Sep28"
    },
    {
        "Date":  "2011-09-23T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Fri",
        "EndDate":  "2011-09-29T12:00:00:00",
        "Range":  "Sep23-Sep29"
    },
    {
        "Date":  "2011-09-24T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Sat",
        "EndDate":  "2011-09-30T12:00:00:00",
        "Range":  "Sep24-Sep30"
    },
    {
        "Date":  "2011-09-25T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Sun",
        "EndDate":  "2011-10-01T12:00:00:00",
        "Range":  "Sep25-Oct01"
    },
    {
        "Date":  "2011-09-26T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Mon",
        "EndDate":  "2011-10-02T12:00:00:00",
        "Range":  "Sep26-Oct02"
    },
    {
        "Date":  "2011-09-27T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Tue",
        "EndDate":  "2011-10-03T12:00:00:00",
        "Range":  "Sep27-Oct03"
    },
    {
        "Date":  "2011-09-28T12:00:00:00",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Wed",
        "EndDate":  "2011-10-04T12:00:00:00",
        "Range":  "Sep28-Oct04"
    }
]


Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 03, 2021, 07:02:47 am
I got to wondering if I stripped off the decimal from the division.  I know I tried to account for it but not sure how at the moment.   Converting the date seemed like such a simple thing but turned into a few hours of work but as usual, learned a lot I wouldn't have otherwise.   In any event, decided the extra feature I was trying to add probably wasn't necessary so stuck with what I had and what worked. 

Was going to post what I did but now VS is telling me it can't start when Setup is in progress so hopefully that problem will go away soon.  No idea what "Setup" it is doing.

Thanks.

Jim

Quote from: td on February 02, 2021, 11:02:59 pm
Not sure which Win32/Drive API functions you were using so this may be completely off base but I have had good luck using the Win32/Drive SDK functions that work in 100 nanosecond increments. The timing is so fine that it varies from machine to machine so you have to get a frequency to seed the other function calls with. Generally, I use them for timing but as Chuck mentioned, the SYSTEMTIME and FILETIME structures store info in 100ns increments.  WinBatch make use of both structures and associated functions internally.
Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 03, 2021, 07:04:22 am
Wouldn't that be right?   Apart from being a couple minutes off?

Jim

Quote from: stanl on February 03, 2021, 05:50:38 am
and for all.... doesn't seem to matter but PS always comes up with 4am [at least on my laptop]


Code: [Select]

$fmt = "MM/dd/yyyy hh:mm:ss tt"
$d = '2015-07-03 10:58:00'
([DateTime] $d).Date.ToUniversalTime().ToString($fmt)
Title: Re: DOM Extender: Json dates are evil
Post by: ChuckC on February 03, 2021, 09:54:56 am
I think there's still some discrepancy in understanding of how a date & time value like "" in JSON is converted to an Excel DATE type.

I'm working with the following:

https://www.excelforum.com/excel-general/1224076-convert-json-date-to-excel-date.html


If I have a cell with a DATE value of "0/19/2011 0:00" being represented as a number of days since the Excel date epoch, I get 40805.00.  If that value is in cell B1, then the following formula:

=(B1 - DATE(1970,1,1))*86400*1000

converts to Unix time with millisecond precision and a value of 1316390400000.00.

If I drop the decimal faction portion and just take 1316390400000 and feed it in to PowerShell as follows:

[DATETIME]::new(1970,1,1).AddMilliseconds(1316390400000)

I get "Monday, September 19, 2011 12:00:00 AM", roundtrips back to the original value in Excel.


This, however, disagrees with your code converting 131640480000 to a date & time value result of 9/19/2011 00:00.

Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 03, 2021, 10:02:03 am
Hmmmmmmmm...what am I missing?  Aren't they both the same time.  One in 12hr format and the other in 24-hr format?

Jim
Title: Re: DOM Extender: Json dates are evil
Post by: ChuckC on February 03, 2021, 10:54:30 am
Take a closer look at the big integer numbers involved for the two different date & time conversions being discussed... they are no the same, although at a quick glance the fact that they are different may not be obvious.

The 12 hour vs. 24 hour clock format difference is just an artifact of the copy & paste work done taking the conversion results from different sources and putting them into the posting w/o having made sure that they were all using the same format.
Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 03, 2021, 12:09:33 pm
I see.   

Jim
Title: Re: DOM Extender: Json dates are evil
Post by: td on February 03, 2021, 02:40:59 pm
Let see... We now have the Excel Epoch to add to the Gregorian Calendar, the Unix Epoch, ISO 8601, and the  Y2032 Epochalypse.  An interesting discussion no doubt for those with time on their hands.
Title: Re: DOM Extender: Json dates are evil
Post by: ChuckC on February 03, 2021, 03:45:22 pm
Equally as interesting for those who have time on their minds even if they have very little on their hands... time-stains are so difficult to wash off of the hands.

At times, it's most annoying having to deal with all of the various methods of representing time that have been thought up over all the years that computers have existed...  it's almost as annoying as having a car with "knockoffs" that are threaded left-handed & right-handed depending on which side of the car the wheels are on... ditto for lug nuts/bolts.
Title: Re: DOM Extender: Json dates are evil
Post by: stanl on February 03, 2021, 04:19:15 pm
So we may
possibly
perhaps
doubt it


agree/not agree that


Json dates
are
could be
depends on how you look at it
time is relevant




Evil
Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 03, 2021, 04:29:56 pm
Yeah...I remember we used to have a dodge something or other when I was a kid and always had to remember that when changing the tires.

Jim


Quote from: ChuckC on February 03, 2021, 03:45:22 pm
At times, it's most annoying having to deal with all of the various methods of representing time that have been thought up over all the years that computers have existed...  it's almost as annoying as having a car with "knockoffs" that are threaded left-handed & right-handed depending on which side of the car the wheels are on... ditto for lug nuts/bolts.
Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 03, 2021, 04:34:13 pm
I did what I could.  As I mentioned, if you find any other commonly used formats let me know and I will add them along with needed tweaks.

Jim
Title: Re: DOM Extender: Json dates are evil
Post by: stanl on February 04, 2021, 02:37:11 am
Quote from: JTaylor on February 03, 2021, 04:34:13 pm
I did what I could.  As I mentioned, if you find any other commonly used formats let me know and I will add them along with needed tweaks.

Jim


Dates as returned by Sales Force
Code: [Select]

[
    {
        "Date":  "2011-09-19T12:00:00.00Z",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Mon",
        "EndDate":  "2011-09-25T12:00:00.00Z",
        "Range":  "Sep19-Sep25"
    },
    {
        "Date":  "2011-09-20T12:00:00.00Z",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Tue",
        "EndDate":  "2011-09-26T12:00:00.00Z",
        "Range":  "Sep20-Sep26"
    },
    {
        "Date":  "2011-09-21T12:00:00.00Z",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Wed",
        "EndDate":  "2011-09-27T12:00:00.00Z",
        "Range":  "Sep21-Sep27"
    },
    {
        "Date":  "2011-09-22T12:00:00.00Z",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Thu",
        "EndDate":  "2011-09-28T12:00:00.00Z",
        "Range":  "Sep22-Sep28"
    },
    {
        "Date":  "2011-09-23T12:00:00.00Z",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Fri",
        "EndDate":  "2011-09-29T12:00:00.00Z",
        "Range":  "Sep23-Sep29"
    },
    {
        "Date":  "2011-09-24T12:00:00.00Z",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Sat",
        "EndDate":  "2011-09-30T12:00:00.00Z",
        "Range":  "Sep24-Sep30"
    },
    {
        "Date":  "2011-09-25T12:00:00.00Z",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Sun",
        "EndDate":  "2011-10-01T12:00:00.00Z",
        "Range":  "Sep25-Oct01"
    },
    {
        "Date":  "2011-09-26T12:00:00.00Z",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Mon",
        "EndDate":  "2011-10-02T12:00:00.00Z",
        "Range":  "Sep26-Oct02"
    },
    {
        "Date":  "2011-09-27T12:00:00.00Z",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Tue",
        "EndDate":  "2011-10-03T12:00:00.00Z",
        "Range":  "Sep27-Oct03"
    },
    {
        "Date":  "2011-09-28T12:00:00.00Z",
        "cMonth":  "2011/10",
        "Qtr":  "4Q2011",
        "DOW":  "Wed",
        "EndDate":  "2011-10-04T12:00:00.00Z",
        "Range":  "Sep28-Oct04"
    }
]
Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 04, 2021, 07:06:58 am
Assuming you have the latest, those return a Type of "UTC", don't they?    Also, you should be able to return a YmdHms format if desired.

Jim
Title: Re: DOM Extender: Json dates are evil
Post by: stanl on February 04, 2021, 07:15:38 am
Quote from: JTaylor on February 04, 2021, 07:06:58 am
Assuming you have the latest, those return a Type of "UTC", don't they?    Also, you should be able to return a YmdHms format if desired.

Jim


Yes, but if you check the Newtonsoft thread I just uploaded, dates are converted w/out .000Z.  This goes back to my original statement that you should just return everything as Text and let the user decide how to deal with it.
Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 04, 2021, 07:34:42 am
Not sure I understand.   I do return the dates as they are unless you explicitly request the YmdHms format.   

Are you getting .000Z tacked onto something?   I can convert dates that don't have a Z on the end if that is what you mean and if needed.   That is why I asked about them earlier in this thread but no one responded. 

The examples you posted have the Z so that is creating a bit of confusion in my mind.   Plus it sounds like you are getting back dates from my extender in some odd form that NewtonSoft doesn't.   If you mean they are just chopping off the milliseconds I can make that an option as well, if you think that would be helpful to people.   I am just trying to make things as easy for people as I can.



Jim
Title: Re: DOM Extender: Json dates are evil
Post by: td on February 04, 2021, 08:15:12 am
Stan's Newtonsoft script is preprocessing the data values through a database assembly, so an examination of the intermediate data that the Newtonssoft assembly is converting to JSON text seems warranted. Also, it is not clear what the rationale is for making Newtonsoft the arbitrator of some JSON conversion standard. That is not to say there isn't a rationale but just that there should be some justification.
Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 04, 2021, 08:49:03 am
i just like to help :)      As noted, one can get the raw value or an YmdHms value if the raw text meets the two stated formats (can account for more if requested).   Apart from that I will let everyone else ponder the bigger questions.

Jim
Title: Re: DOM Extender: Json dates are evil
Post by: stanl on February 04, 2021, 08:50:04 am
Quote from: td on February 04, 2021, 08:15:12 am
Stan's Newtonsoft script is preprocessing the data values through a database assembly, so an examination of the intermediate data that the Newtonssoft assembly is converting to JSON text seems warranted. Also, it is not clear what the rationale is for making Newtonsoft the arbitrator of some JSON conversion standard. That is not to say there isn't a rationale but just that there should be some justification.


Au contraire. Tony already chipped in about standards. I throw stuff like the Newtonsoft script because I now know it works. I have also thrown out examples of different date interpretation.
I expect a STFU from someone on these threads pretty soon ::)
Title: Re: DOM Extender: Json dates are evil
Post by: ChuckC on February 04, 2021, 11:00:08 am
https://www.newtonsoft.com/json/help/html/T_Newtonsoft_Json_Converters_IsoDateTimeConverter.htm

Newtonsoft JSON has a built-in date & time converter class, IsoDateTimeConverter, using a time format string of "yyyy-MM-ddTHH:mm:ssZ".  However, usage of the converter is discretionary.  And, it's possible to derive new converter classes from it that set a different format string in their c'tor method.

What you have is still ISO 8601 compliant, even though the precision is truncated to whole seconds instead of going out to some decimal fractional amount.  If the trailing "Z" is missing, then the time is assumed to have an unknown time zone.  If the "Z" has been replaced with a
"+" or "-" followed by "HH:mm", then it's a local time in a time zone represented by an offset from GMT.

What is going to be key when dealing with dates in JSON is that there simply is no one single unified representation of a date & time value as a string for purposes of interchange via JSON.  As a result of this, there has to be either a contract where the provider & consumer agree on a format, or the consumer simply has to be aware of all possible representations and must be capable of handling them all.  You might be able to do some pattern matching with a RegEx that would permit some kind of heuristic for automatically choosing a date & time "decoder", but it won't be inexpensive in terms of performance and it won't be 100% reliable.

caveat emptor, YMMV, and all other suitably applicable disclaimers, etc...
Title: Re: DOM Extender: Json dates are evil
Post by: JTaylor on February 04, 2021, 12:21:25 pm
This gives me some ideas.   I will probably just add a function where one can submit a UTC or time_t  value for conversion to YmdHms.  That way if they are retrieving values for other time zones they can tweak to align with GMT and get it in local time.  Seems the most flexible and keeps me from "trying" to account for all possibilities.

Thanks.

Jim
Title: Re: DOM Extender: Json dates are evil
Post by: td on February 04, 2021, 01:38:00 pm
Quote from: stanl on February 04, 2021, 08:50:04 am

Au contraire. Tony already chipped in about standards. I throw stuff like the Newtonsoft script because I now know it works. I have also thrown out examples of different date interpretation.
  • Newtonsoft works with WB [it didn't for me in 2013, but that was my problem]
  • Json pretty much ends up everywhere no; for me personally- in an API [work-related, cannot share]
  • I could use CLR strings to create my own Json from anything
  • Json 'object' typing - like the way the dates came in as milli-seconds - is it's own topic.
I expect a STFU from someone on these threads pretty soon ::)

I can make no sense of your response.  I will just assume you took my very brief analysis to mean something other than what was intended and move on.
Title: Re: DOM Extender: Json dates are evil
Post by: ChuckC on February 04, 2021, 05:27:12 pm
When it comes to writing code that deals with date & time stamps, I always use UTC time.  It's a no-brainer to apply your local offset to that, and anybody with auto-correcting eyes can condition themselves quickly to mentally converting UTC to local time when looking at log file records and JSON content.

If anybody wants local time presented, they are welcome to convert the DATETIME or time_t values into local times, but any API that I develop explicitly requires UTC times.