Postgresql - 日期比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14029979/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql - Date comparison
提问by Graham
I am running a query from pgAdmin3 against my postgresql database. I am wondering why it is returning values but all expected values. The query is:
我正在从 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')
The GRANTDATE column is type date and contains values in the format 2006-09-04. I want to return all rows where the GRANTDATE is less than or equal to the month-year provided. The problem i have is that it returns all the "less than" matching rows but does not return the "equals to" rows. So Jan-2006 to Oct-2006 is there but not Nov-2006.
GRANTDATE 列的类型为日期并包含格式为 2006-09-04 的值。我想返回 GRANTDATE 小于或等于提供的月-年的所有行。我遇到的问题是它返回所有“小于”匹配行但不返回“等于”行。所以 2006 年 1 月到 2006 年 10 月在那里，但 2006 年 11 月没有。
Any advice would be great.
任何建议都会很棒。
采纳答案by Igor Romanchenko
Try:
尝试：
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');
The problem: When you do to_date('Nov-2006','Mon-YYYY')you get the 2006-11-01. So the only date, that will be equal to to_date('Nov-2006','Mon-YYYY')is 2006-11-01, not the whole month.
问题：当to_date('Nov-2006','Mon-YYYY')你得到2006-11-01. 因此，唯一的日期，这将是相等to_date('Nov-2006','Mon-YYYY')的2006-11-01，而不是整个一个月。
Edit. The other way:
编辑。另一种方法：
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');
回答by wildplasser
As Erwin stated: dates have no format. Also: there is no need to do all the excessive casting of dates to and from characters.
Besides: even for the final query result, you could do a SET datestyle;(for the most common styles in use), and avoid all the excessive formatting.
正如 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'
;
RESULT:
结果：
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)
