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@xxxxxxxxxxx
