postgresql timestamp的精度问题

PostgreSQL: 浅谈 PostgreSQL 的 timestamp 类型  

2012-06-07 14:36:19|  分类： Postgres基础|举报|字号 订阅

 

      今天再次有同事问我 PostgreSQL 中时间类型的格式显示问题，例如 " 2012-06-07 14:00:02.412827+08"
如何显示成 " 2012-06-07 14:00:02" ，既然问这个问题的人还不少，于是决定记下来，尽管内容比较简单。在
回答这个问题之前，先来看看 PG 的手册，补充下基础知识。

一 PostgreSQL 中的时间类型如下

Name	Storage Size	Description	Low Value	High Value	Resolution
timestamp [ (p) ] [ without time zone ]	8 bytes	both date and time (no time zone)	4713 BC	294276 AD	1 microsecond / 14 digits
timestamp [ (p) ] with time zone	8 bytes	both date and time, with time zone	4713 BC	294276 AD	1 microsecond / 14 digits
date	4 bytes	date (no time of day)	4713 BC	5874897 AD	1 day
time [ (p) ] [ without time zone ]	8 bytes	time of day (no date)	00:00:00	24:00:00	1 microsecond / 14 digits
time [ (p) ] with time zone	12 bytes	times of day only, with time zone	00:00:00+1459	24:00:00-1459	1 microsecond / 14 digits
interval [ fields ] [ (p) ]	12 bytes	time interval	-178000000 years	178000000 years	1 microsecond / 14 digits

 
  备注：这里不准备详细介绍各种类型，请注意上面表格中的[ (p) ] ，这个是什么呢？这个是指时间的精度，
             time, timestamp, 和 interval 类型都可以指定精度，精度的取值范围是 0 到 6， 下面通过具体
             实验来体验下精度。
        
       

二 current_timestamp 实验
--2.1 查询  current_timestamp

skytf=> select current_timestamp;               now               -------------------------------  2012-06-07 14:00:02.412827+08 (1 row)

   

 备注： current_timestamp 函数返回时间类型为 timestamp with  time zone，故返回结果后面包括时区 +08 ，
              以及精度 412827，那么如何去掉精度和时区呢？
 
        
--2.2 去掉精度

skytf=> select current_timestamp(0);       timestamptz        ------------------------  2012-06-07 14:07:17+08 (1 row)

   

--2.3 去掉时区

skytf=> select current_timestamp(0)::timestamp without time zone;       timestamp       ---------------------  2012-06-07 14:07:49 (1 row)

   

--2.4 也可以用 cast 函数类型转换

skytf=> select cast (current_timestamp(0) as  timestamp without time zone);       timestamp       ---------------------  2012-06-07 14:14:55 (1 row)

   
                 
            
--2.5 了解 [p] 的含义

skytf=> select current_timestamp(2)::timestamp without time zone;        timestamp         ------------------------  2012-06-07 14:15:42.64 (1 row) skytf=> select current_timestamp(6)::timestamp without time zone;          timestamp           ----------------------------  2012-06-07 14:15:46.281422 (1 row)

   

   备注：可见 [p] 是指时间类型小数点后面的精度，如果 p 指定 2，则精度为2，如果 p 指定 6
              则精度为 6； 所以在定义表的时候就应该事先定义 timestamp 时间类型的精度。
        

三 创建表测试，定义时间类型精度为0

skytf=> create table test_p (id int4 primary key, create_time  timestamp(0) without time zone); NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_p_pkey" for table "test_p" CREATE TABLE skytf=> \d test_p                    Table "skytf.test_p"    Column    |              Type              | Modifiers  -------------+--------------------------------+-----------  id          | integer                        | not null  create_time | timestamp(0) without time zone |  Indexes:     "test_p_pkey" PRIMARY KEY, btree (id) skytf=> select current_timestamp;               now               -------------------------------  2012-06-07 14:18:31.683105+08 (1 row)                                skytf=> insert into test_p values (1,current_timestamp); INSERT 0 1 skytf=> select * from test_p;  id |     create_time      ----+---------------------   1 | 2012-06-07 14:19:02 (1 row)