3

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:

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.

Any advice would be great.

Improve this question
4
  • You don't need the cast to do the comparison. Commented Dec 25, 2012 at 10:59
  • i did try running the query with Dec-2006 and the Nov-2006 dataset appears but not the Dec-2006 dataset. Commented Dec 25, 2012 at 11:02
  • 1
    When the GRANTDATE column is of type date there is no need to do to_date(GRANTDATE,'YYYY-MM-DD'). It will only slow things down as it first has to turn GRANTDATE into a string and then convert it back again. As for the solution see Igor's answer. Commented Dec 25, 2012 at 11:27
  • 2
    Contradicting: The GRANTDATE column is type date and contains values in the format 2006-09-04. If the column is of type date, it doesn't "have" a format. If it "has" a format, it's not a date. Commented Dec 25, 2012 at 11:44

2 Answers 2

Reset to default
2

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.

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');
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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.

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)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.