JSON / ASP.NET AJAX Dates support in PostgreSQL
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
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.
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@gmail.com>
Sent: Thursday, April 13, 2023 5:07 PM
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.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.
On 4/13/23 08:31, Sebastien Flaesch wrote:
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/
<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?
By hand as this is a MS/ASP thing:
https://weblogs.asp.net/bleroy/dates-and-json
not a JSON thing.
Seb
------------------------------------------------------------------------
--
Adrian Klaver
adrian.klaver@aklaver.com
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).
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 CSTThus:
select to_timestamp(cast(1672692813062 as bigint))::timestamp;
ITYM:
select to_timestamp(1672692813062/1000.0);
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
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 CSTThus:
select to_timestamp(cast(1672692813062 as bigint))::timestamp;ITYM:
select to_timestamp(1672692813062/1000.0);
hp
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2023-04-14 10:44:08 -0700, Adrian Klaver wrote:
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:
Yes, but it needs a specialized JSON parser to note that. As they write:
Of course, a parser that doesn't know about this convention will just
see a string,
And not only will it just see a string, it will output a string that's
indistinguishable from a string with the input
"/Date(1672692813062+0100)/". So any code after the parser can't detect
those extra backslashes. (This would include for example the object_hook
in the Python json Decoder which gets the decoded strings, not the raw
strings).
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
On 4/15/23 03:46, Peter J. Holzer wrote:
On 2023-04-14 10:44:08 -0700, Adrian Klaver wrote:
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:
Yes, but it needs a specialized JSON parser to note that. As they write:
Of course, a parser that doesn't know about this convention will just
see a string,And not only will it just see a string, it will output a string that's
indistinguishable from a string with the input
"/Date(1672692813062+0100)/". So any code after the parser can't detect
those extra backslashes. (This would include for example the object_hook
in the Python json Decoder which gets the decoded strings, not the raw
strings).
I would encourage you to read the whole post, it is short. Bottom line,
this is a cheat MS created for the Microsoft Ajax Library. Their hope
was(from the post):
"
We're pretty much satisfied with this solution to the date problem, but
of course for the moment very few serializers and parsers support that
convention. It would be great if this could become the consensus across
the industry.
"
NOTE: This is from Friday, January 18, 2008
I'm going to go out on a limb and say whatever JSON parsing Postgres is
doing does not recognize this format.
hp
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2023-04-15 09:12:41 -0700, Adrian Klaver wrote:
On 4/15/23 03:46, Peter J. Holzer wrote:
On 2023-04-14 10:44:08 -0700, Adrian Klaver wrote:
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:
"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:
Yes, but it needs a specialized JSON parser to note that. As they write:
Of course, a parser that doesn't know about this convention will just
see a string,And not only will it just see a string, it will output a string that's
indistinguishable from a string with the input
"/Date(1672692813062+0100)/". So any code after the parser can't detect
those extra backslashes. (This would include for example the object_hook
in the Python json Decoder which gets the decoded strings, not the raw
strings).I would encourage you to read the whole post, it is short. Bottom line, this
is a cheat MS created for the Microsoft Ajax Library. Their hope was(from
the post):
Yes, I got that.
[...]
I'm going to go out on a limb and say whatever JSON parsing Postgres is
doing does not recognize this format.
Exactly. This was my point. Nor is any other JSON parser (except the one
which invented that cheat of course) likely to recognize it. And it's
sort of hard to add to existing parsers without breaking app
compatibility. In short: I don't think adding that to Postgres is a good
idea.
hp
PS: I do appreciate it as a hack, though.
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"