Postgres 9.5.3 JSON字段将日期与MAC OSX 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）进行了测试。有什么想法吗？

更新：因此，DateTime和ActiveSupport :: TimeWithzone格式为ISO 8601返回不同格式：

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时间戳，而不是'2016-11-12 18:13:03.432534'中的日期和时间组件之间丢失的T。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想要使用的内容。如果您的第二精度还不够好，也有precision的CC_14参数。

---

顺便说一句，您确定JSON是正确的方法吗？单独的表可能更合适。