Обсуждение: 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.
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:
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;
--
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;
--
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
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:
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;
--
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 CST > > Thus: > 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 CST >> >> Thus: >> 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: > > https://weblogs.asp.net/bleroy/dates-and-json 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: >> >> https://weblogs.asp.net/bleroy/dates-and-json > > 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: > > > > > > https://weblogs.asp.net/bleroy/dates-and-json > > > > 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!"