Re: JSON / ASP.NET AJAX Dates support in PostgreSQL

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(dot)klaver(at)aklaver(dot)com