使用BETWEEN时间戳的SQL查询的意外结果 [英] Unexpected results from SQL query with BETWEEN timestamps

问题描述

我创建了一个小测试应用程序来跟踪我在Heroku上使用Postgres时遇到的问题： http：// snippi.com/s/xd511rf

正如您在 49 行中看到的那样，我想要检索创建的所有条目>今天。这是我用Ruby Gem DataMapper测试数据的前两项。当我在笔记本上运行这个应用时（Ubuntu 12.10 ，HP，Ruby 1.9.3）所有我得到这个结果，这是正确的：

  [
 {
id：1，
text：在awsomenewss上工作，
category：0，
starttime：2013-03-21T15：56： 00 + 01：00，
endtime：2013-03-21T18：26：00 + 01：00，
creation：2013-03-21T16：15：21+ 01:00
，
 {
id：2，
text：facebooking，
category：0，
starttime：2013-03-21T20：48：00 + 01：00，
endtime：2013-03-21T22：26：00 + 01：00，
创作：2013-03-21T16：15：21 + 01：00
} 
] 
  

在我的调试控制台中记录了这个SQL查询：

  SELECTid，text， 类别，开始时间，结束时间，创建
从条目
 WHEREstarttime
 BETWEEN'2013-03-2 1T00：00：00 + 00：00'
''2013-03-21T23：59：59 + 00：00'
 ORDER BYid
  

但是在将应用程序推送到Heroku后，发生了一个非常奇怪的错误。当我现在运行它（ http://afternoon-everglades-4239.herokuapp.com/）这是回应：

  [] 
  

为什么它是空的？

这个数据肯定在数据库中，这是由Heroku的Dataclip证明的： https://dataclips.heroku.com/hygziosyxwperyctwfbhjzgbzhbj

另外，当我通过'heroku pg：psql'手动运行SQL命令时，它实际上可以与此输出一起工作：

 id |文字|类别| starttime | endtime |创建
 ---- + ----------------------------- + ---------- + --------------------- + --------------------- + ----- ---------------- 
 1 |工作一些awsomenewss | 0 | 2013-03-21 15:56:00 | 2013-03-21 18:26:00 | 2013-03-21 16:15:21 
 2 | facebooking | 0 | 2013-03-21 20:48:00 | 2013-03-21 22:26:00 | 2013-03-21 16:15:21 
（2行）
 

日志中不包含任何错误或更多信息。
在这两种情况下（生产和本地），我都使用了一个远程Heroku PostgreSQL数据库。为什么这不起作用？ 请检查列的数据类型和您的时区。 。您可能会混淆 时间戳与时区 和 时间戳 。

看起来您的表中有 timestamp ，但是查询时带有 timestamptz 。这样，这一切都取决于您的会话的本地时区（如果未另行指定，则默认为服务器的时区）。

切换到 timestamptz 或 timestamp ，如果时区与您完全无关。 （如果有疑问，请使用 timestamptz 。）

不是问题的原因，但您的查询应该可能是：

  SELECT id，text，category，starttime，endtime，creation 
 FROM entries 
 WHERE starttime> ; =时间戳'2013-03-21' - 默认为00:00时间
 AND starttime< timestamp'2013-03-22'
 ORDER BY id 
  

对于 timestamp 类型，由于分数的原因，x和y之间的几乎总是错误的！您的查询将如何处理 starttime ='2013-03-21T23：59：59.123 + 00'？

I have created a little test app to track down a problem I experienced with Postgres on Heroku: http://snippi.com/s/xd511rf

As you can see in line 49, I want to retrieve all entries created today. This would be the first two items of my test data with the Ruby Gem DataMapper.

When I run this app on my notebook (Ubuntu 12.10, HP, Ruby 1.9.3) everything I get this result, which is right:

[
{
    "id": 1,
    "text": "Working on some awsomenewss",
    "category": 0,
    "starttime": "2013-03-21T15:56:00+01:00",
    "endtime": "2013-03-21T18:26:00+01:00",
    "creation": "2013-03-21T16:15:21+01:00"
},
{
    "id": 2,
    "text": "facebooking",
    "category": 0,
    "starttime": "2013-03-21T20:48:00+01:00",
    "endtime": "2013-03-21T22:26:00+01:00",
    "creation": "2013-03-21T16:15:21+01:00"
}
]

In my debug console this SQL query is logged:

SELECT "id", "text", "category", "starttime", "endtime", "creation" 
  FROM "entries" 
  WHERE "starttime" 
    BETWEEN '2013-03-21T00:00:00+00:00' 
      AND '2013-03-21T23:59:59+00:00' 
  ORDER BY "id"

But after pushing the app to Heroku a very strange error occurrs. When I run it now (http://afternoon-everglades-4239.herokuapp.com/) this is the response:

[]

Why is it empty?

The data is definitely in the database which is proved by this Dataclip from Heroku: https://dataclips.heroku.com/hygziosyxwperyctwfbhjzgbzhbj

Also when I run the SQL command manually via ´heroku pg:psql´ it actually works with this output:

 id |            text             | category |      starttime      |       endtime       |      creation       
----+-----------------------------+----------+---------------------+---------------------+---------------------
  1 | Working on some awsomenewss |        0 | 2013-03-21 15:56:00 | 2013-03-21 18:26:00 | 2013-03-21 16:15:21
  2 | facebooking                 |        0 | 2013-03-21 20:48:00 | 2013-03-21 22:26:00 | 2013-03-21 16:15:21
(2 rows)

The logs do not contain any errors or further information. I have used a Remote Heroku PostgreSQL Database in both cases (Production and Local).

So why does this not work?

解决方案

Check the data type of the columns and your time zone. You may be confusing timestamp with time zone and timestamp.

Looks like you have timestamp in your table, but query with timestamptz. This way, it all depends on the local time zone of your session (which defaults to the time zone of the server if not specified otherwise.)

Switch both to timestamptz, or timestamp if time zones are completely irrelevant to you. (If in doubt, use timestamptz.)

Not the cause of your problem, but your query should probably be:

SELECT id, text, category, starttime, endtime, creation 
FROM   entries 
WHERE  starttime >= timestamp '2013-03-21' -- defaults to 00:00 time
AND    starttime <  timestamp '2013-03-22'
ORDER  BY id

a BETWEEN x AND y is almost always wrong for timestamp types due to fractional numbers! What would your query do with starttime = '2013-03-21T23:59:59.123+00'?

这篇关于使用BETWEEN时间戳的SQL查询的意外结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！