postgresql - JSONb dates: actual dates internally?

Question

I'm using the postgresql jdbc adapter to migrate a bunch of data into a jsonb field (postgres 9.4).

After importing, date fields look correct but are displayed surrounded with doublequotes. Is there a way to tell if they are actually stored internally as date values ? If they're strings, I don't think range lookups will be very efficient.

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

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

I can now search on, 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 is reparsing a string value in the jsonb field each time, as a date.

I could create an index where I cast these values to dates, and use that to search, but it seems somewhat inelegant. I'd really prefer to know that the value stored is a date. Integers and floats appear to be their actual native values, not strings, as in these:

shares_sold": 5900000,

"latitude": 33.561467,

Any feedback highly appreciated.

Answer 1

JSON does not have a "date" type. The JSONB type (added in Pg 9.4) maps but does not extend upon JSON primitive types. The value seen is what it is, text.

While an index can be added over string properties the current "English prose" format will not be able to participate in range queries because such values are not well-ordered over dates.

Different ways of storing well-ordered dates, given the primitive type restrictions.

• Specific ISO 8601 variation (with same timezone); text

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

• Julian Day (see the 'J' date format); numeric

(Equality index probes can be used even if ranges cannot .. provided the values match exactly, which is still subject to using a consistent data representation.)

When applying a range query over the (JSONB / GIN) index convert the DATE values to the appropriate JSON data type (mapped to integer, numeric, text in Pg) used for the "date" property; not the other way.

When fetching the values convert the chosen format to a DATE - it's 'okay' since this is done after the range query and 'required' since JSONB does not natively support dates or times.