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.
