OS:CentOS 5
DB:Postgres 9.2.4
1.数据准备
[postgres@localhost ~]$ psql psql (9.2.4) Type "help" for help. postgres=# create table t_kenyon(id serial,ctime timestamp without time zone,mtime date,remark text); NOTICE: CREATE TABLE will create implicit sequence "t_kenyon_id_seq" for serial column "t_kenyon.id" CREATE TABLE postgres=# insert into t_kenyon(ctime,mtime,remark) select generate_series('2013-04-22'::date,'2013-04-24'::date,'1h'),generate_series('2013-04-22'::date,'1d'),'Kenyon_Good_Boy!'; INSERT 0 147 postgres=# select * from t_kenyon limit 10; id | ctime | mtime | remark -----+---------------------+------------+------------------ 736 | 2013-04-22 00:00:00 | 2013-04-22 | Kenyon_Good_Boy! 737 | 2013-04-22 01:00:00 | 2013-04-23 | Kenyon_Good_Boy! 738 | 2013-04-22 02:00:00 | 2013-04-24 | Kenyon_Good_Boy! 739 | 2013-04-22 03:00:00 | 2013-04-22 | Kenyon_Good_Boy! 740 | 2013-04-22 04:00:00 | 2013-04-23 | Kenyon_Good_Boy! 741 | 2013-04-22 05:00:00 | 2013-04-24 | Kenyon_Good_Boy! 742 | 2013-04-22 06:00:00 | 2013-04-22 | Kenyon_Good_Boy! 743 | 2013-04-22 07:00:00 | 2013-04-23 | Kenyon_Good_Boy! 744 | 2013-04-22 08:00:00 | 2013-04-24 | Kenyon_Good_Boy! 745 | 2013-04-22 09:00:00 | 2013-04-22 | Kenyon_Good_Boy! (10 rows)2.统计sql
postgres=# select count(1) from t_kenyon where ctime>'2013-04-22'; count ------- 144 (1 row) postgres=# select count(1) from t_kenyon where ctime>='2013-04-23'; count ------- 75 (1 row) postgres=# select count(1) from t_kenyon where mtime>='2013-04-23'; count ------- 98 (1 row) postgres=# select count(1) from t_kenyon where mtime>'2013-04-22'; count ------- 98 (1 row)3.分析
同事觉得第二个字段统计是正常,第一个是非正常的,表示不解,但是仔细观察一下,这两个字段类型是不一样的,ctime是timestamp类型,mtime是date类型,当条件是ctime>'2013-04-22'时其实是等价于ctime>='2013-04-22 00:00:00',所以会取到2013-04-22 01:00:00这些数据,但是ctime>='2013-04-23'时,其实是等价于ctime>='2013-04-23 00:00:00'或者ctime>'2013-04-22 23:59:59',所以两者有很大的出入,但是date类型的就不受此影响了。
例子如下:
postgres=# select count(1) from t_kenyon where ctime>'2013-04-22 23:59:59'; count ------- 75 (1 row) postgres=# select count(1) from t_kenyon where ctime>='2013-04-23 00:00:00'; count ------- 75 (1 row) postgres=# select count(1) from t_kenyon where ctime>'2013-04-22 00:00:00'; count ------- 144 (1 row) postgres=# select count(1) from t_kenyon where ctime>='2013-04-22 00:00:00'; count ------- 147 (1 row) postgres=# select count(1) from t_kenyon where mtime>'2013-04-22'; count ------- 98 (1 row) postgres=# select count(1) from t_kenyon where mtime>'2013-04-22 10:00:00'; count ------- 98 (1 row) postgres=# select count(1) from t_kenyon where mtime>='2013-04-23'; count ------- 98 (1 row) postgres=# select count(1) from t_kenyon where mtime>='2013-04-23 13:00:00'; count ------- 98 (1 row)所以统计时需要注意一下。