Re: JSON / ASP.NET AJAX Dates support in PostgreSQL - Mailing list pgsql-general

On 4/13/23 09:44, Sebastien Flaesch wrote:

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.

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;

--