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

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/)

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?

Seb
________________________________
From: Ron <ronljohnsonjr(at)gmail(dot)com>
Sent: Thursday, April 13, 2023 5:07 PM
To: pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: JSON / ASP.NET AJAX Dates support in PostgreSQL

EXTERNAL: Do not click links or open attachments if you do not recognize the sender.

On 4/13/23 09:44, Sebastien Flaesch wrote:
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.

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;

--
Born in Arizona, moved to Babylonia.