JSONb dates: actual dates inside?

Question

I am using the postgresql jdbc adapter to transfer the dataset to the jsonb field (postgres 9.4).

After import, the date fields look correct, but are displayed in an environment with double entries. Is there a way to determine if they are actually stored internally as date values? If they are strings, I don’t think range searching will be very efficient.

For example, an entry in the jsonb properties field looks like this:

"founded_on": "Sep 1, 2012 12:00:00 AM",

Now I can do a search, let's say

SELECT CAST(properties->>'founded_on' AS DATE

and

SELECT extract('year' from cast(properties->>'founded_on' as timestamp))

and both work fine, but don't tell me whether Postgres will redraw the string value in the jsonb field each time as a date.

I could create an index in which I passed these values ​​as dates and used them to search, but it seems somewhat inelegant. I would rather know that the stored value is a date. Integers and floats are apparently their eigenvalues, not strings, for example:

shares_sold": 5900000,

"latitude": 33.561467,

Any feedback is much appreciated.

Answer 1

JSON is not of type date. The JSONB type (added in Pg 9.4 ), but does not apply to JSON primitive types. The visible meaning is what it is, text.

While an index can be added over the properties of a string, the current "English prose" format will not be able to participate in range queries, since such values ​​are not sorted by date.

Different ways to store well-ordered dates given primitive type constraints.

• Specific ISO 8601 (with the same time zone); text

• UNIX time or "JavaScript time" with milliseconds; integer

• Julian Day (see date format 'J' ); Numerical

(Equality index identifiers can be used even if ranges cannot .. provided that the values ​​match exactly, which still depends on the consistent presentation of the data.)

When applying a range query by index (JSONB / GIN), convert the DATE values ​​to the corresponding JSON data type (associated with the integer, numeric, textual in Pg) used for the date property; and not vice versa.

When a selection of values ​​converts the selected format to DATE, this is normal, as it is done after a range request and is "required" because JSONB does not support dates or times.