JSONb日期：内部实际日期？ [英] JSONb dates: actual dates internally?

问题描述

我使用postgresql jdbc适配器将一堆数据迁移到jsonb字段（postgres 9.4）。

导入后，日期字段看起来正确，但是显示被双引号包围。有没有办法告诉他们是否真的在内部存储为日期值？如果他们是字符串，我不认为范围查找将是非常有效的。

例如，一个属性 jsonb字段如下所示：

created_on：2012年9月1日12:00:00 AM code>

我现在可以搜索，例如

SELECT CAST（property->>'created_on'AS DATE

和

SELECT extract（yearfrom cast（properties->>'established_on'as timestamp））

并且都可以正常工作，但不要告诉我Postgres是否每次在jsonb字段中重新排列一个字符串值，作为日期。

我可以创建一个索引在这里我将这些值转换为日期，并使用它进行搜索，但似乎有些不太实际，我更希望知道存储的值是一个日期。整数和浮点似乎是他们的实际本机值，而不是字符串，在这些：

shares_sold：5900000，

纬度：33.561467，

任何反馈高度赞赏。

解决方案

JSON没有日期类型。 JSONB类型（在 Pg 9.4 中添加）映射，但 不会在JSON原始类型上扩展。它是什么，文本。

虽然可以通过字符串属性添加索引，但是当前的英语散文格式将无法参与查询因为这些值在日期之前没有被很好的排序。

给定原始类型限制，存储正确的日期的不同方式。
$ b

• 具体 ISO 8601变体（具有相同的时区）;文字




• UNIX时间或JavaScript time（毫秒）;整数




• 朱利安日 （请参阅J日期格式）;数字

（即使范围不能，也可以使用均衡索引探针。如果在（JSONB / GIN）索引上应用范围查询将DATE值转换为相应的JSON数据类型（映射到整数，数字，Pg中的文本）用于date属性;

当获取值将所选格式转换为DATE时 - 这是'好'，因为这是在范围之后完成的查询和必需，因为JSONB本身不支持日期或时间。

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.

解决方案

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.

这篇关于JSONb日期：内部实际日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！