viewpoint-particle

Author Topic: DOM Extender: Json dates are evil  (Read 499 times)

stanl

  • Pundit
  • *****
  • Posts: 1390
DOM Extender: Json dates are evil
« 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"
    }
]





JTaylor

  • Pundit
  • *****
  • Posts: 1595
    • Data & Stuff Inc.
Re: DOM Extender: Json dates are evil
« Reply #1 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

stanl

  • Pundit
  • *****
  • Posts: 1390
Re: DOM Extender: Json dates are evil
« Reply #2 on: February 01, 2021, 02:19:14 pm »
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 ::)

td

  • Tech Support
  • *****
  • Posts: 3634
    • WinBatch
Re: DOM Extender: Json dates are evil
« Reply #3 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...
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

JTaylor

  • Pundit
  • *****
  • Posts: 1595
    • Data & Stuff Inc.
Re: DOM Extender: Json dates are evil
« Reply #4 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

td

  • Tech Support
  • *****
  • Posts: 3634
    • WinBatch
Re: DOM Extender: Json dates are evil
« Reply #5 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

Which is pretty much what you have.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

td

  • Tech Support
  • *****
  • Posts: 3634
    • WinBatch
Re: DOM Extender: Json dates are evil
« Reply #6 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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

JTaylor

  • Pundit
  • *****
  • Posts: 1595
    • Data & Stuff Inc.
Re: DOM Extender: Json dates are evil
« Reply #7 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

stanl

  • Pundit
  • *****
  • Posts: 1390
Re: DOM Extender: Json dates are evil
« Reply #8 on: February 02, 2021, 03:01:28 am »
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%") )


 


td

  • Tech Support
  • *****
  • Posts: 3634
    • WinBatch
Re: DOM Extender: Json dates are evil
« Reply #9 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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

JTaylor

  • Pundit
  • *****
  • Posts: 1595
    • Data & Stuff Inc.
Re: DOM Extender: Json dates are evil
« Reply #10 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

stanl

  • Pundit
  • *****
  • Posts: 1390
Re: DOM Extender: Json dates are evil
« Reply #11 on: February 02, 2021, 11:26:19 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.

ChuckC

  • Sr. Member
  • ****
  • Posts: 289
Re: DOM Extender: Json dates are evil
« Reply #12 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.


td

  • Tech Support
  • *****
  • Posts: 3634
    • WinBatch
Re: DOM Extender: Json dates are evil
« Reply #13 on: February 02, 2021, 01:45:38 pm »

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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

td

  • Tech Support
  • *****
  • Posts: 3634
    • WinBatch
Re: DOM Extender: Json dates are evil
« Reply #14 on: February 02, 2021, 03:02:14 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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

ChuckC

  • Sr. Member
  • ****
  • Posts: 289
Re: DOM Extender: Json dates are evil
« Reply #15 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.


td

  • Tech Support
  • *****
  • Posts: 3634
    • WinBatch
Re: DOM Extender: Json dates are evil
« Reply #16 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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

JTaylor

  • Pundit
  • *****
  • Posts: 1595
    • Data & Stuff Inc.
Re: DOM Extender: Json dates are evil
« Reply #17 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

td

  • Tech Support
  • *****
  • Posts: 3634
    • WinBatch
Re: DOM Extender: Json dates are evil
« Reply #18 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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

  • Pundit
  • *****
  • Posts: 1390
Re: DOM Extender: Json dates are evil
« Reply #19 on: February 03, 2021, 03:37:12 am »

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.


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.

stanl

  • Pundit
  • *****
  • Posts: 1390
Re: DOM Extender: Json dates are evil
« Reply #20 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"
    }
]



stanl

  • Pundit
  • *****
  • Posts: 1390
Re: DOM Extender: Json dates are evil
« Reply #21 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)

JTaylor

  • Pundit
  • *****
  • Posts: 1595
    • Data & Stuff Inc.
Re: DOM Extender: Json dates are evil
« Reply #22 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

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"
    }
]



JTaylor

  • Pundit
  • *****
  • Posts: 1595
    • Data & Stuff Inc.
Re: DOM Extender: Json dates are evil
« Reply #23 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

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.

JTaylor

  • Pundit
  • *****
  • Posts: 1595
    • Data & Stuff Inc.
Re: DOM Extender: Json dates are evil
« Reply #24 on: February 03, 2021, 07:04:22 am »
Wouldn't that be right?   Apart from being a couple minutes off?

Jim

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)

ChuckC

  • Sr. Member
  • ****
  • Posts: 289
Re: DOM Extender: Json dates are evil
« Reply #25 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.


JTaylor

  • Pundit
  • *****
  • Posts: 1595
    • Data & Stuff Inc.
Re: DOM Extender: Json dates are evil
« Reply #26 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

ChuckC

  • Sr. Member
  • ****
  • Posts: 289
Re: DOM Extender: Json dates are evil
« Reply #27 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.

JTaylor

  • Pundit
  • *****
  • Posts: 1595
    • Data & Stuff Inc.
Re: DOM Extender: Json dates are evil
« Reply #28 on: February 03, 2021, 12:09:33 pm »
I see.   

Jim

td

  • Tech Support
  • *****
  • Posts: 3634
    • WinBatch
Re: DOM Extender: Json dates are evil
« Reply #29 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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

ChuckC

  • Sr. Member
  • ****
  • Posts: 289
Re: DOM Extender: Json dates are evil
« Reply #30 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.

stanl

  • Pundit
  • *****
  • Posts: 1390
Re: DOM Extender: Json dates are evil
« Reply #31 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

JTaylor

  • Pundit
  • *****
  • Posts: 1595
    • Data & Stuff Inc.
Re: DOM Extender: Json dates are evil
« Reply #32 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


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.

JTaylor

  • Pundit
  • *****
  • Posts: 1595
    • Data & Stuff Inc.
Re: DOM Extender: Json dates are evil
« Reply #33 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

stanl

  • Pundit
  • *****
  • Posts: 1390
Re: DOM Extender: Json dates are evil
« Reply #34 on: February 04, 2021, 02:37:11 am »
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"
    }
]