pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedJSON / ASP.NET AJAX Dates support in PostgreSQL
9+ messages / 4 participants
[nested] [flat]
* JSON / ASP.NET AJAX Dates support in PostgreSQL
@ 2023-04-13 14:44 Sebastien Flaesch <sebastien.flaesch@4js.com>
0 siblings, 1 reply; 9+ messages in thread
From: Sebastien Flaesch @ 2023-04-13 14:44 UTC (permalink / raw)
To: PostgreSQL General <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JSON / ASP.NET AJAX Dates support in PostgreSQL
@ 2023-04-13 15:07 Ron <ronljohnsonjr@gmail.com>
parent: Sebastien Flaesch <sebastien.flaesch@4js.com>
0 siblings, 2 replies; 9+ messages in thread
From: Ron @ 2023-04-13 15:07 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JSON / ASP.NET AJAX Dates support in PostgreSQL
@ 2023-04-13 15:31 Sebastien Flaesch <sebastien.flaesch@4js.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Sebastien Flaesch @ 2023-04-13 15:31 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JSON / ASP.NET AJAX Dates support in PostgreSQL
@ 2023-04-13 15:53 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Sebastien Flaesch <sebastien.flaesch@4js.com>
0 siblings, 0 replies; 9+ messages in thread
From: Adrian Klaver @ 2023-04-13 15:53 UTC (permalink / raw)
To: Sebastien Flaesch <sebastien.flaesch@4js.com>; Ron <ronljohnsonjr@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JSON / ASP.NET AJAX Dates support in PostgreSQL
@ 2023-04-14 16:31 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Peter J. Holzer @ 2023-04-14 16:31 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JSON / ASP.NET AJAX Dates support in PostgreSQL
@ 2023-04-14 17:44 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2023-04-14 17:44 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JSON / ASP.NET AJAX Dates support in PostgreSQL
@ 2023-04-15 10:46 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 9+ messages in thread
From: Peter J. Holzer @ 2023-04-15 10:46 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JSON / ASP.NET AJAX Dates support in PostgreSQL
@ 2023-04-15 16:12 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2023-04-15 16:12 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: JSON / ASP.NET AJAX Dates support in PostgreSQL
@ 2023-04-15 16:34 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 9+ messages in thread
From: Peter J. Holzer @ 2023-04-15 16:34 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2023-04-15 16:34 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-13 14:44 JSON / ASP.NET AJAX Dates support in PostgreSQL Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-04-13 15:07 ` Ron <ronljohnsonjr@gmail.com>
2023-04-13 15:31 ` Sebastien Flaesch <sebastien.flaesch@4js.com>
2023-04-13 15:53 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-04-14 16:31 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-04-14 17:44 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-04-15 10:46 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-04-15 16:12 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-04-15 16:34 ` Peter J. Holzer <hjp-pgsql@hjp.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox