我的桌子看起来像这样:
库存
inventory | creationDate
{} | 2017-04-27 14:15:15.25+02
{} | 2017-04-27 13:03:02.205+02
{} | 2017-04-27 13:03:01.766+02
{} | 2017-04-27 13:02:19.8+02
{} | 2017-04-27 12:35:52.12+02
查询:
SELECT * FROM "Inventory"
WHERE "registerTillInventoryId" = 1
AND "creationDate" <= '2017-04-27 12:02:38.000 +00:00'
ORDER BY "creationDate" DESC;
结果:
inventory | creationDate
{} | 2017-04-27 13:03:02.205+02
{} | 2017-04-27 13:03:01.766+02
{} | 2017-04-27 13:02:19.8+02
{} | 2017-04-27 12:35:52.12+02
我的问题是为什么我从
中获取数据13:03:02.205+02 and 13:03:01.766+02
我查询的结果应为
inventory | creationDate
{} | 2017-04-27 13:02:19.8+02
{} | 2017-04-27 12:35:52.12+02
好的,谢谢您的帮助。我在Vao Tsun的评论的帮助下解决了问题。
我没有描述的是我试图从12:02 00:00
获得13:02我真的不需要时区。因此,如果我查询
creationDate < '2017-04-27 13:02:38.000'
直接。没有时区,我会得到正确的结果。
{} | 2017-04-27 13:02:19.8+02
{} | 2017-04-27 12:35:52.12+02
...
'2017-04-27 12:02:38.000 +00:00'
是 '2017-04-27 14:02:38.000 +00:02'
,因此,它返回除'2017-04-27 14:15:15.25+02'
以外的所有内容,因为它唯一不遵守"creationDate" <= '2017-04-27 12:02:38.000 +00:00'
是正确的。示例:
t=# with a as (select '2017-04-27 12:02:38.000 +00:00' at time zone 'utc' ts)
select ts, ts at time zone 'gmt+2' from a;
ts | timezone
---------------------+------------------------
2017-04-27 12:02:38 | 2017-04-27 14:02:38+00
(1 row)
Time: 0.480 ms
为什么不只是"creationDate" <= '2017-04-27 12:02:38.000'
避免混合TZ?..