JSON / ASP.NET AJAX Dates support in PostgreSQL

From: Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com>
To: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: JSON / ASP.NET AJAX Dates support in PostgreSQL
Date: 2023-04-13 14:44:45
Message-ID: DBAP191MB12896A817739B869A781C00CB0989@DBAP191MB1289.EURP191.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

How can I load this record into a table defined as:

CREATE TABLE custorder (
"PurchaseOrder" BIGINT NOT NULL PRIMARY KEY,
"CreationDate" TIMESTAMP NOT NULL,
"LastChangeDateTime" TIMESTAMP NOT NULL
)

?

Seb

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2023-04-13 15:02:11 Re: "PANIC: could not open critical system index 2662" - twice
Previous Message Tom Lane 2023-04-13 14:37:40 Re: Unexpected behavior when combining `generated always` columns and update rules