| From: | Mike Withers <M(dot)withers(at)uws(dot)edu(dot)au> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | problem with selects based on dates |
| Date: | 2001-09-06 07:15:50 |
| Message-ID: | 3.0.32.20010906171549.01c572c0@sovereign.macarthur.uws.edu.au |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Hi everyone
I have a problem with SELECTs using dates and WHERE.
Some examples:
SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+------------+------------+------+------------+------+------+--------
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | 0 | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | |
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | | 10
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | | 20
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | | 30
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | | 20
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | | 10
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | | 30
7839 | KING | PRESIDENT | | 1981-11-17 | 5000 | | 10
(14 rows)
db2001=# SELECT * FROM emp WHERE hiredate > 1981;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+------------+------------+------+------------+------+------+--------
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | 0 | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | | 10
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | | 20
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | | 30
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | | 20
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | | 10
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | | 30
7839 | KING | PRESIDENT | | 1981-11-17 | 5000 | | 10
(13 rows)
db2001=# SELECT * FROM emp WHERE hiredate > 1981-01-01;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+------------+------------+------+------------+------+------+--------
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | 0 | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | |
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | | 10
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | | 20
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | | 30
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | | 20
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | | 10
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | | 30
7839 | KING | PRESIDENT | | 1981-11-17 | 5000 | | 10
(14 rows)
It is not clear to me why in the last query the 1980-12-17 hiredate record
is selected as well.
Mike Withers
University of Western Sydney
Australia
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Rob Brown-Bayliss | 2001-09-06 07:58:55 | Primary keys and speed |
| Previous Message | Karel Zak | 2001-09-06 07:01:42 | Re: CREATE USER vs. createuser |