Postgres 9.5.3JSON字段将日期与Mac 10上的操作符怪异行为进行比较

Question

我在这两台Mac电脑上都有问题，但在我的Linux机器上没有问题。在我的Mac上使用这样的日期比较操作符是行不通的：

# Ruby code
dt_start = DateTime.current - 10.days
dt_end = DateTime.current
id = 1
# last_seen field looks like this in db when we store it:
# {"1":"2016-11-21T22:17:47.269Z"}
User.where("(last_seen->'?')::text <> 'null'", id
).where( "(last_seen->'?')::text > ?", id, dt_start
).where( "(last_seen->'?')::text <= ?", id, dt_end)

SELECT "public"."users".* FROM "public"."users" WHERE ((last_seen->'1')::text <> 'null') AND ((last_seen->'1')::text > '2016-11-12 18:13:03.432534') AND ((last_seen->'1')::text <= '2016-11-22 18:13:03.432534')

在我的Mac上不返回记录，但在Linux上工作。

在分解该查询时，当我使用>运算符时，无论我输入的日期范围如何，我都不会得到任何记录。

User.where( "(last_seen->'?')::text > ?", id, 10.years.ago).count
SELECT COUNT(*) FROM "public"."users" WHERE ((last_seen->'1')::text > '2006-11-22 23:46:59.199255')
=> 0

当我只使用<运算符时，我会得到所有具有非空last_seen字段的记录，不管我输入了什么日期。

User.where( "(last_seen->'?')::text < ?", id, 10.years.ago).count
SELECT COUNT(*) FROM "public"."users" WHERE ((last_seen->'1')::text > '2006-11-22 23:46:59.199255')
=> 42

我甚至通过在我的Mac上切换时间来匹配我的linux机箱时区( UTC )进行了测试。有什么想法吗？

更新： So DateTime和ActiveSupport::TimeWithZone格式化为ISO8601返回不同格式：

DateTime.current.iso8601 # => "2016-11-23T19:18:36+00:00"
Time.zone.now.iso8601    # => "2016-11-23T19:18:44Z"

由于last_seen JSON字段使用ActiveSupport::TimeWithZone存储日期，所以我尝试更改该格式的SQL查询，但问题相同：

last_seen: {"1"=>"2016-10-20T14:30:00Z"}

SELECT COUNT(*) FROM "public"."users" WHERE ((last_seen->'1')::text <> 'null') AND ((last_seen->'1')::text > '2016-01-23T19:03:11Z') AND ((last_seen->'1')::text <= '2016-11-23T19:01:10Z')
=> 0

然后，我将last_seen JSON改为使用DateTime的第二种格式，并使用DateTime查询，而不是使用相同的问题。

Answer 1

您可以说您的JSON列包含如下内容：

{"1":"2016-11-21T22:17:47.269Z"}

该对象中的值是一个真正的ISO-8601时间戳。ActiveRecord正在产生的查询：

SELECT "public"."users".*
FROM "public"."users"
WHERE ... ((last_seen->'1')::text > '2016-11-12 18:13:03.432534') ...

使用的不是完全-ISO-8601时间戳，而不是缺少的T之间的日期和时间组件在'2016-11-12 18:13:03.432534'。text比较的结果将取决于'T'和' '的比较结果，这并不一定是您希望的结果，甚至是跨平台一致的结果。

如果您要这样做，您需要确保格式是一致的。我会使用严格的ISO-8601，因为这是一种真正的时间戳格式，而且它在任何地方都会有一致的行为。#iso8601方法将为您处理格式：

User.where("(last_seen->'?')::text <> 'null'", id)
    .where( "(last_seen->'?')::text > ?", id, dt_start.iso8601)
    .where( "(last_seen->'?')::text <= ?", id, dt_end.iso8601)

自己调用#iso8601将为ActiveRecord提供一个字符串，因此您将绕过AR想要使用的时间戳到字符串格式设置。如果你一秒钟的精度还不够好的话，iso8601还有一个iso8601参数。

顺便提一下，您确定JSON是正确的方法吗？一张单独的桌子也许更合适。