Postgres 9.3 - JSON & proper Date Object

Question

I'm using PostgreSQL 9.3 and looking for a way to incorporate TIMESTAMPTZ as true JavaScript date objects.

I understand that JavaScript does not offer Date literal notation, so I was thinking

'{ "key" : new Date(datevalue) }'::JSON

would do the trick.

The problem is that I get the error: Token "new" is invalid.

Is it possible for me to update the Postgres JSON parser?

Maybe there is some other way I can get a bit more datatype consistency as I pass data between the front and back end... any thoughts?

Richard Huxton · Accepted Answer · 2013-11-30 06:50:06Z

1

That doesn't make any sense. JSON doesn't support atomic elements other than string /number / boolean. See the spec for details.

One of the main purposes of the format is to be (machine) language independent so having embedded Javascript Date objects (or Regex or Window or any other) makes no sense. What would it mean when you load that JSON in e.g. C++ - how would you call the methods on the embedded Date object?

A timestamp is text. Anything other than a number or a boolean is text. If that's not good enough for you then you either need to layer some more meaning on top of JSON or pick a different format.

answered Nov 30, 2013 at 6:50

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

losthorse Over a year ago

I disagree. A timestamp should not be text, it should be an object and if it can't be, it would have to be a number. JSON is more than just "string / number / boolean". From the docs: A value can be a string in double quotes, or a number, or true or false or null, or an object or an array. You're correct, imbedding a function call into JSON may create problems (if I were to send that function to a language that can't call it). My original question still stands, how can I update the Postgres JSON parser to allow the Date constructor?

Richard Huxton Over a year ago

I fully support your right to be wrong, and I'm afraid this time you are. Re-read the specs. A JSON "object" is nothing more than what Perl calls a hash and PHP an associative array. JSON cannot contain functions in any form because they are not strings, numbers, boolean or null. To modify PostgreSQL's JSON parser, just download the source-code open a text editor and edit some C (start with files with json in their name). It won't be a JSON parser any more though, will it? You'll need a different name for the format and you'll be the only one who uses it.

Chris Travers Over a year ago

A timestamp should not be text, Ideally I would agree. Unfortunately the spec doesn't agree.

Collectives™ on Stack Overflow

Postgres 9.3 - JSON & proper Date Object

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related