PostgreSQL - 日期比较

Question

我正在使用pgAdmin3对我的PostgreSQL数据库运行查询。我想知道为什么它会返回值，但不是所有预期的值。查询如下：

SELECT P.SURNAME, 
       TO_CHAR(TO_DATE(SFE.GRANTDATE,'YYYY-MM-DD'),'DD Mon YY') AS GRANTDATE,
       TO_CHAR(TO_DATE(SFE.REVOKEDATE,'YYYY-MM-DD'),'DD Mon YY') AS REVOKEDATE, 
       SFE.feedname  
FROM   SYS_FEED SFE,
       PRINCIPLE P
WHERE  SFE.USERID = P.ID   
AND    to_date(GRANTDATE,'YYYY-MM-DD') <= to_date('Nov-2006','Mon-YYYY')

GRANTDATE列的数据类型为日期，格式为2006-09-04。我想返回所有GRANTDATE小于或等于给定月份和年份的行。问题在于它只返回所有“小于”匹配的行，而不返回“等于”的行。因此，Jan-2006到Oct-2006是有的，但Nov-2006没有。希望能提供一些建议。

- Graham

---

你不需要进行强制类型转换来进行比较。 - wildplasser

我尝试使用 "Dec-2006" 运行查询，但只出现了 "Nov-2006" 数据集而没有出现 "Dec-2006" 数据集。 - Graham

当GRANTDATE列的类型为日期时，无需执行to_date（GRANTDATE，'YYYY-MM-DD'）。这只会减慢速度，因为它首先必须将GRANTDATE转换为字符串，然后再将其转换回来。至于解决方案，请参见Igor的答案。 - Eelke

矛盾之处在于：“GRANTDATE”列的类型为“date”，并包含格式为“2006-09-04”的值。如果该列的类型为“date”，则它没有“格式”。如果它有一个“格式”，那么它就不是“date”类型了。 - Erwin Brandstetter

Answer 1

尝试：

SELECT P.SURNAME, 
       TO_CHAR(TO_DATE(SFE.GRANTDATE,'YYYY-MM-DD'),'DD Mon YY') AS GRANTDATE,
       TO_CHAR(TO_DATE(SFE.REVOKEDATE,'YYYY-MM-DD'),'DD Mon YY') AS REVOKEDATE, 
       SFE.feedname  
FROM   SYS_FEED SFE,
       PRINCIPLE P
WHERE  SFE.USERID = P.ID   
AND    to_date(GRANTDATE,'YYYY-MM-DD') < (to_date('Nov-2006','Mon-YYYY') + interval '1 month');

问题：当你执行to_date('Nov-2006','Mon-YYYY')时，你会得到2006-11-01。因此，唯一与to_date('Nov-2006','Mon-YYYY')相等的日期是2006-11-01，而不是整个月份。

编辑。另一种方式：

SELECT P.SURNAME, 
       TO_CHAR(TO_DATE(SFE.GRANTDATE,'YYYY-MM-DD'),'DD Mon YY') AS GRANTDATE,
       TO_CHAR(TO_DATE(SFE.REVOKEDATE,'YYYY-MM-DD'),'DD Mon YY') AS REVOKEDATE, 
       SFE.feedname  
FROM   SYS_FEED SFE,
       PRINCIPLE P
WHERE  SFE.USERID = P.ID   
AND    date_trunc('month', to_date(GRANTDATE,'YYYY-MM-DD')) <= to_date('Nov-2006','Mon-YYYY');

- Ihor Romanchenko

Answer 2

正如Erwin所说：日期没有格式。此外，无需对日期进行过多的字符转换。另外，即使对于最终的查询结果，你也可以使用SET datestyle;（针对最常用的样式），避免所有过度的格式化。

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE SYS_FEED
        ( USERID INTEGER NOT NULL PRIMARY KEY
        , GRANTDATE DATE NOT NULL
        , REVOKEDATE DATE NOT NULL
        , feedname varchar
        );
INSERT INTO SYS_FEED(USERID, GRANTDATE, REVOKEDATE, feedname)
SELECT sv
        , date('2005-01-01') + sv
        , date('2006-01-01') + sv
        , 'silent_' || sv::text
FROM generate_series( 1,1000) sv
        ;
CREATE TABLE PRINCIPLE
        ( ID INTEGER NOT NULL PRIMARY KEY
        , SURNAME varchar
        );
INSERT INTO PRINCIPLE(ID,SURNAME)
SELECT val
        , 'SHOUT_' || val::text
FROM generate_series( 1,1000) val
        ;

DELETE FROM SYS_FEED WHERE random() < 0.9;
DELETE FROM PRINCIPLE WHERE random() < 0.9;

-- EXPLAIN ANALYZE
SELECT P.SURNAME
       , to_char(SFE.GRANTDATE, 'DD Mon YY') AS GRANTDATE
       , to_char(SFE.REVOKEDATE, 'DD Mon YY') AS REVOKEDATE
       , SFE.feedname AS DONTSHOUTNAME
FROM    SYS_FEED SFE
JOIN    PRINCIPLE P ON SFE.USERID = P.ID
WHERE   GRANTDATE < '2006-12-01'
        ;

结果：

DROP SCHEMA
CREATE SCHEMA
SET
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sys_feed_pkey" for table "sys_feed"
CREATE TABLE
INSERT 0 1000
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "principle_pkey" for table "principle"
CREATE TABLE
INSERT 0 1000
DELETE 901
DELETE 904
  surname  | grantdate | revokedate | dontshoutname 
-----------+-----------+------------+---------------
 SHOUT_53  | 23 Feb 05 | 23 Feb 06  | silent_53
 SHOUT_173 | 23 Jun 05 | 23 Jun 06  | silent_173
 SHOUT_308 | 05 Nov 05 | 05 Nov 06  | silent_308
 SHOUT_337 | 04 Dec 05 | 04 Dec 06  | silent_337
 SHOUT_531 | 16 Jun 06 | 16 Jun 07  | silent_531
 SHOUT_543 | 28 Jun 06 | 28 Jun 07  | silent_543
 SHOUT_566 | 21 Jul 06 | 21 Jul 07  | silent_566
(7 rows)

- wildplasser