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.klaver@aklaver.com