PostgreSQL timestamp字段统计误区

最新推荐文章于 2021-03-11 22:09:55 发布

weixin_34206899

最新推荐文章于 2021-03-11 22:09:55 发布

阅读量194

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/Kenyon/blog/125081

版权

为什么80%的码农都做不了架构师？>>>

开发人员说两个类似的SQL统计出来的结果差别很大，对此不是很理解，还原一下场景及分析处理方案

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,'2013-04-24'::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)

所以统计时需要注意一下。

转载于:https://my.oschina.net/Kenyon/blog/125081