Обсуждение: JSON / ASP.NET AJAX Dates support in PostgreSQL

P {margin-top:0;margin-bottom:0;}

Hello,

Is there an easy way to convert JSON data containing ASP.NET AJAX Dates into PostgreSQL timestamp?

I have this kind of JSON data:

{

    "PurchaseOrder" : "4500000000",

    "CreationDate" : "\/Date(1672358400000)\/",

    "LastChangeDateTime" : "\/Date(1672692813062+0100)\/"

}

Warning: Note the backslash before the slashes!
According to JSON spec this is valid JSON and used by AJAX Date format.

When loading that data in Firefox, the JSON visualizer shows AJAX Date values as

"/Date(1672358400000)/"

and

"/Date(1672692813062+0100)/"

(without the backslash)

My understanding is that backslash + slash = slash in JSON strings.

On 4/13/23 08:31, Sebastien Flaesch wrote:
> 
>  >From the investigation we did here, I can confirm that the number in 
> AJAX Date is the milliseconds since Epoch (1970-01-01 00:00:00), and 
> it's always in UTC, even if there is a timezone offset provided. I 
> mention this because it's different from ISO 8601 datetimes, where the 
> datetime part is in local time corresponding to the offset, when one is 
> specified (check https://momentjs.com/docs/#/parsing/asp-net-json-date/ 
> <https://momentjs.com/docs/#/parsing/asp-net-json-date/>)
> 
> So, is there some built-in JSON API in PostgreSQL to convert an AJAX 
> Date from JSON or do I have to do this by hand?

By hand as this is a MS/ASP thing:

https://weblogs.asp.net/bleroy/dates-and-json

not a JSON thing.

> 
> Seb
> ------------------------------------------------------------------------


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2023-04-13 10:07:09 -0500, Ron wrote:
> On 4/13/23 09:44, Sebastien Flaesch wrote:
>     Is there an easy way to convert JSON data containing ASP.NET AJAX Dates
>     into PostgreSQL timestamp?
>
>     I have this kind of JSON data:
>
>     {
>         "PurchaseOrder" : "4500000000",
>         "CreationDate" : "\/Date(1672358400000)\/",
>         "LastChangeDateTime" : "\/Date(1672692813062+0100)\/"
>     }
>
>     Warning: Note the backslash before the slashes!

That's a Noop. According to RFC 8259, "\/" is the same as "/" (no idea
why they even specified that - it seems quite pointless).

>     According to JSON spec this is valid JSON and used by AJAX Date format.

It's valid JSON, but for JSON it's just a string, not a date.

Any interpretation is strictly by convention between the sender and the
receiver.


> This looks like "milliseconds since the Unix epoch:
>
> $ date -d @1672692813.062
> Mon 02 Jan 2023 02:53:33 PM CST
>
> Thus:
> select to_timestamp(cast(1672692813062 as bigint))::timestamp;

ITYM:

    select to_timestamp(1672692813062/1000.0);

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 4/14/23 9:31 AM, Peter J. Holzer wrote:
> On 2023-04-13 10:07:09 -0500, Ron wrote:
>> On 4/13/23 09:44, Sebastien Flaesch wrote:
>>      Is there an easy way to convert JSON data containing ASP.NET AJAX Dates
>>      into PostgreSQL timestamp?
>>
>>      I have this kind of JSON data:
>>
>>      {
>>          "PurchaseOrder" : "4500000000",
>>          "CreationDate" : "\/Date(1672358400000)\/",
>>          "LastChangeDateTime" : "\/Date(1672692813062+0100)\/"
>>      }
>>
>>      Warning: Note the backslash before the slashes!
> 
> That's a Noop. According to RFC 8259, "\/" is the same as "/" (no idea
> why they even specified that - it seems quite pointless).

It is a cheat explained here:

https://weblogs.asp.net/bleroy/dates-and-json


"Our current approach is using a small loophole in the JSON specs. In a 
JSON string literal, you may (or may not) escape some characters. Among 
those characters, weirdly enough, there is the slash character ('/'). 
This is weird because there actually is no reason that I can think of 
why you'd want to do that. We've used it to our benefit to disambiguate 
a string from a date literal.

The new format is "\/Date(1198908717056)\/" where the number is again 
the number of milliseconds since January 1st 1970 UTC. I would gladly 
agree that this is still not super readable, which could be solved by 
using ISO 8601 instead.

The point is that this disambiguates a date literal from a string that 
looks like the same date literal, while remaining pure JSON that will be 
parsed by any standard JSON parser. Of course, a parser that doesn't 
know about this convention will just see a string, but parsers that do 
will be able to parse those as dates without a risk for false positives 
(except if the originating serializer escaped slashes, but I don't know 
of one that does).
"
> 
>>      According to JSON spec this is valid JSON and used by AJAX Date format.
> 
> It's valid JSON, but for JSON it's just a string, not a date.
> 
> Any interpretation is strictly by convention between the sender and the
> receiver.
> 
> 
>> This looks like "milliseconds since the Unix epoch:
>>
>> $ date -d @1672692813.062
>> Mon 02 Jan 2023 02:53:33 PM CST
>>
>> Thus:
>> select to_timestamp(cast(1672692813062 as bigint))::timestamp;
> 
> ITYM:
> 
>      select to_timestamp(1672692813062/1000.0);
> 
>          hp
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2023-04-14 10:44:08 -0700, Adrian Klaver wrote:
> On 4/14/23 9:31 AM, Peter J. Holzer wrote:
> > On 2023-04-13 10:07:09 -0500, Ron wrote:
> > > On 4/13/23 09:44, Sebastien Flaesch wrote:
> > >      Is there an easy way to convert JSON data containing ASP.NET AJAX Dates
> > >      into PostgreSQL timestamp?
> > >
> > >      I have this kind of JSON data:
> > >
> > >      {
> > >          "PurchaseOrder" : "4500000000",
> > >          "CreationDate" : "\/Date(1672358400000)\/",
> > >          "LastChangeDateTime" : "\/Date(1672692813062+0100)\/"
> > >      }
> > >
> > >      Warning: Note the backslash before the slashes!
> >
> > That's a Noop. According to RFC 8259, "\/" is the same as "/" (no idea
> > why they even specified that - it seems quite pointless).
>
> It is a cheat explained here:
>
> https://weblogs.asp.net/bleroy/dates-and-json

Yes, but it needs a specialized JSON parser to note that. As they write:

> Of course, a parser that doesn't know about this convention will just
> see a string,

And not only will it just see a string, it will output a string that's
indistinguishable from a string with the input
"/Date(1672692813062+0100)/". So any code after the parser can't detect
those extra backslashes. (This would include for example the object_hook
in the Python json Decoder which gets the decoded strings, not the raw
strings).

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 4/15/23 03:46, Peter J. Holzer wrote:
> On 2023-04-14 10:44:08 -0700, Adrian Klaver wrote:
>> On 4/14/23 9:31 AM, Peter J. Holzer wrote:
>>> On 2023-04-13 10:07:09 -0500, Ron wrote:
>>>> On 4/13/23 09:44, Sebastien Flaesch wrote:
>>>>       Is there an easy way to convert JSON data containing ASP.NET AJAX Dates
>>>>       into PostgreSQL timestamp?
>>>>
>>>>       I have this kind of JSON data:
>>>>
>>>>       {
>>>>           "PurchaseOrder" : "4500000000",
>>>>           "CreationDate" : "\/Date(1672358400000)\/",
>>>>           "LastChangeDateTime" : "\/Date(1672692813062+0100)\/"
>>>>       }
>>>>
>>>>       Warning: Note the backslash before the slashes!
>>>
>>> That's a Noop. According to RFC 8259, "\/" is the same as "/" (no idea
>>> why they even specified that - it seems quite pointless).
>>
>> It is a cheat explained here:
>>
>> https://weblogs.asp.net/bleroy/dates-and-json
> 
> Yes, but it needs a specialized JSON parser to note that. As they write:
> 
>> Of course, a parser that doesn't know about this convention will just
>> see a string,
> 
> And not only will it just see a string, it will output a string that's
> indistinguishable from a string with the input
> "/Date(1672692813062+0100)/". So any code after the parser can't detect
> those extra backslashes. (This would include for example the object_hook
> in the Python json Decoder which gets the decoded strings, not the raw
> strings).

I would encourage you to read the whole post, it is short. Bottom line, 
this is a cheat MS created for the Microsoft Ajax Library. Their hope 
was(from the post):

"
We're pretty much satisfied with this solution to the date problem, but 
of course for the moment very few serializers and parsers support that 
convention. It would be great if this could become the consensus across 
the industry.
"

NOTE: This is from Friday, January 18, 2008

I'm going to go out on a limb and say whatever JSON parsing Postgres is 
doing does not recognize this format.



> 
>          hp
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2023-04-15 09:12:41 -0700, Adrian Klaver wrote:
> On 4/15/23 03:46, Peter J. Holzer wrote:
> > On 2023-04-14 10:44:08 -0700, Adrian Klaver wrote:
> > > On 4/14/23 9:31 AM, Peter J. Holzer wrote:
> > > > On 2023-04-13 10:07:09 -0500, Ron wrote:
> > > > > On 4/13/23 09:44, Sebastien Flaesch wrote:
> > > > >           "LastChangeDateTime" : "\/Date(1672692813062+0100)\/"
> > > > >
> > > > >       Warning: Note the backslash before the slashes!
> > > >
> > > > That's a Noop. According to RFC 8259, "\/" is the same as "/" (no idea
> > > > why they even specified that - it seems quite pointless).
> > >
> > > It is a cheat explained here:
> > >
> > > https://weblogs.asp.net/bleroy/dates-and-json
> >
> > Yes, but it needs a specialized JSON parser to note that. As they write:
> >
> > > Of course, a parser that doesn't know about this convention will just
> > > see a string,
> >
> > And not only will it just see a string, it will output a string that's
> > indistinguishable from a string with the input
> > "/Date(1672692813062+0100)/". So any code after the parser can't detect
> > those extra backslashes. (This would include for example the object_hook
> > in the Python json Decoder which gets the decoded strings, not the raw
> > strings).
>
> I would encourage you to read the whole post, it is short. Bottom line, this
> is a cheat MS created for the Microsoft Ajax Library. Their hope was(from
> the post):

Yes, I got that.

[...]
> I'm going to go out on a limb and say whatever JSON parsing Postgres is
> doing does not recognize this format.

Exactly. This was my point. Nor is any other JSON parser (except the one
which invented that cheat of course) likely to recognize it. And it's
sort of hard to add to existing parsers without breaking app
compatibility. In short: I don't think adding that to Postgres is a good
idea.

        hp

PS: I do appreciate it as a hack, though.

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"