oracle 增强型时间类型以及postgresql时间类型

oracle<wbr style=""><div style="">date 包含时间和日期类型，包括年月日，小时，分钟，秒。这个经常用，相信大家都熟悉</div> <div style="">timestamp 这个类型很精确，是精确到微妙的时间单位。指定精度的小数位，最多为9位，默认6位</div> <div style="">timestamp with time zone 这个除了包含了timestamp的外，又有了时区。</div> <div style="">timstamp with local time zone 这个类型不包含时区偏移量，由用户当地会话时区确定。</div> <div style="">interval year to month</div> <div style="">interval day to second</div> <div style="">timestamp可以通过to_timestamp_t转换到timestamp with time zone</div> <div style="">tz_offset可以查看时区的差位移</div> <div style="">查看数据库时区：</div> <div style="">SELECTdbtimezoneFROMdual;<br style="line-height:25px"> </div> <div style="">select sessiontimezone from dual;</div> <div style=""> <div style="line-height:25px">SQL&gt; select extract(year from sysdate) from dual;--获取年份</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">EXTRACT(YEARFROMSYSDATE)</div> <div style="line-height:25px">------------------------</div> <div style="line-height:25px"> 2012</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">SQL&gt; select extract(month from sysdate) from dual;--获取月份</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">EXTRACT(MONTHFROMSYSDATE)</div> <div style="line-height:25px">-------------------------</div> <div style="line-height:25px"> 9</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">SQL&gt; select extract(day from sysdate) from dual;--获取日</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">EXTRACT(DAYFROMSYSDATE)</div> <div style="line-height:25px">-----------------------</div> <div style="line-height:25px"> 6</div> </div> <div style=""> <div style="line-height:25px">SQL&gt; select from_tz(timestamp '2012-09-06 09:00:00','8:00') from dual; --timestamp转timestamp with time zone</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">FROM_TZ(TIMESTAMP'2012-09-0609:00:00','8:00')</div> <div style="line-height:25px">---------------------------------------------------------------------------</div> <div style="line-height:25px">06-SEP-12 09.00.00.000000000 AM +08:00</div> </div> <div style=""><br style="line-height:25px"></div> <div style=""><br style="line-height:25px"></div> <div style=""> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">SQL&gt; create table test_zone</div> <div style="line-height:25px"> 2 (timestamp_dt TIMESTAMP,</div> <div style="line-height:25px"> 3 zone_dt TIMESTAMP WITH TIME ZONE,</div> <div style="line-height:25px"> 4 local_zone_dt TIMESTAMP WITH LOCAL TIME ZONE);</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">Table created.</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">SQL&gt; insert into test_zone values (sysdate,sysdate,sysdate); </div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">1 row created.</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">SQL&gt; col TIMESTAMP_DT for a35</div> <div style="line-height:25px">SQL&gt; col ZONE_DT for a35</div> <div style="line-height:25px">SQL&gt; col LOCAL_ZONE_DT for a35</div> <div style="line-height:25px">SQL&gt; select * from test_zone;</div> </div> <div style=""> <div style="line-height:25px">TIMESTAMP_DT ZONE_DT LOCAL_ZONE_DT</div> <div style="line-height:25px">----------------------------------- ----------------------------------- -----------------------------------</div> <div style="line-height:25px">06-SEP-12 01.50.10.000000 PM 06-SEP-12 01.50.10.000000 PM +08:00 06-SEP-12 01.50.10.000000 PM</div> </div> <div style=""> <div style="line-height:25px">SQL&gt; insert into test_zone values(to_date('20090101','yyyymmdd'),to_timestamp('20090101','yyyymmdd'),to_timestamp_tz('20090101 010101 -8:00','yyyymmdd hh24miss TZH:TZM'));</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">1 row created.</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">SQL&gt; select * from test_zone;</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">TIMESTAMP_DT ZONE_DT LOCAL_ZONE_DT</div> <div style="line-height:25px">----------------------------------- ----------------------------------- -----------------------------------</div> <div style="line-height:25px">06-SEP-12 01.50.10.000000 PM 06-SEP-12 01.50.10.000000 PM +08:00 06-SEP-12 01.50.10.000000 PM</div> <div style="line-height:25px">01-JAN-09 12.00.00.000000 AM 01-JAN-09 12.00.00.000000 AM +08:00 01-JAN-09 05.01.01.000000 PM</div> </div> <div style=""> <div style="line-height:25px">SQL&gt; alter session set time_zone='+05:00';</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">Session altered.</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">SQL&gt; select * from test_zone;</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">TIMESTAMP_DT ZONE_DT LOCAL_ZONE_DT (提前了三个小时)</div> <div style="line-height:25px">----------------------------------- ----------------------------------- -----------------------------------</div> <div style="line-height:25px">06-SEP-12 01.50.10.000000 PM 06-SEP-12 01.50.10.000000 PM +08:00 06-SEP-12 10.50.10.000000 AM</div> <div style="line-height:25px">01-JAN-09 12.00.00.000000 AM 01-JAN-09 12.00.00.000000 AM +08:00 01-JAN-09 02.01.01.000000 PM</div> </div> <div style=""><br style="line-height:25px"></div> <div style=""> <div style="line-height:25px">SQL&gt; select * from v$timezone_names where rownum&lt;10; --查看时区名称</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">TZNAME TZABBREV</div> <div style="line-height:25px">---------------------------------------------------------------- ----------------------------------------------------------------</div> <div style="line-height:25px">Africa/Abidjan LMT</div> <div style="line-height:25px">Africa/Abidjan GMT</div> <div style="line-height:25px">Africa/Accra LMT</div> <div style="line-height:25px">Africa/Accra GMT</div> <div style="line-height:25px">Africa/Accra GHST</div> <div style="line-height:25px">Africa/Addis_Ababa LMT</div> <div style="line-height:25px">Africa/Addis_Ababa ADMT</div> <div style="line-height:25px">Africa/Addis_Ababa EAT</div> <div style="line-height:25px">Africa/Algiers LMT</div> </div> <div style=""> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">SQL&gt; select tz_offset('Africa/Accra') from dual;</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">TZ_OFFS</div> <div style="line-height:25px">-------</div> <div style="line-height:25px">+00:00</div> </div> <div style="">alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';--设置会话日期显示格式</div> <div style=""> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">SQL&gt; select to_timestamp('20120909 00:01:02.123456789','yyyymmdd hh24:mi:ss.ff') from dual; --to_timestamp函数使用</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">TO_TIMESTAMP('2012090900:01:02.123456789','YYYYMMDDHH24:MI:SS.FF')</div> <div style="line-height:25px">---------------------------------------------------------------------------</div> <div style="line-height:25px">09-SEP-12 12.01.02.123456789 AM</div> </div> <div style=""> <div style="line-height:25px">SQL&gt; select to_timestamp_tz('20120909 00:01:02.123456789 0:0','yyyymmdd hh24:mi:ss.ff tzh:tzm') from dual; --to_timestamp_tz使用</div> <div style="line-height:25px"><br style="line-height:25px"></div> <div style="line-height:25px">TO_TIMESTAMP_TZ('2012090900:01:02.1234567890:0','YYYYMMDDHH24:MI:SS.FFTZH:T</div> <div style="line-height:25px">---------------------------------------------------------------------------</div> <div style="line-height:25px">09-SEP-12 12.01.02.123456789 AM +00:00</div> </div> <div style=""><br style="line-height:25px"></div> <div style=""><br style="line-height:25px"></div> <div style="">postgresql时间类型：9.1.2版本</div> <div style=""><br style="line-height:25px"></div> <div style=""><br style="line-height:25px"></div> <div style=""> <span style="font-family:Verdana,'Lucida Grande',Arial,Helvetica,sans-serif; font-size:12px; line-height:18px"></span> <table border="0" align="center" style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; line-height:21px; margin:0px auto; width:650px; border-collapse:collapse; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid"><tbody style="line-height:21px"> <tr style="line-height:21px"> <td style="border-bottom:rgb(192,192,192) 1px solid; text-align:center; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px"><strong style="line-height:23px">名字</strong></span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; text-align:center; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px"><strong style="line-height:23px">存储空间</strong></span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; text-align:center; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px"><strong style="line-height:23px">描述</strong></span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; text-align:center; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px"><strong style="line-height:23px">最低值</strong></span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; text-align:center; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px"><strong style="line-height:23px">最高值</strong></span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; text-align:center; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px"><strong style="line-height:23px">分辨率</strong></span> </td> </tr> <tr style="line-height:21px"> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">timestamp[无时区]</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">8字节</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">包括日期和时间</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">4713 BC</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">5874897AD</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">1毫秒/14位</span> </td> </tr> <tr style="line-height:21px"> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">timestamp[含时区] with time zone</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">8字节</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">日期和时间，带时区</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">4713 BC</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">5874897AD</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">1毫秒/14位</span> </td> </tr> <tr style="line-height:21px"> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">interval</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">12字节</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">时间间隔</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">-178000000年</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">178000000年</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">1毫秒/14位</span> </td> </tr> <tr style="line-height:21px"> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">date</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">4字节</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">只用于日期</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">4713 BC</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">32767AD</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">1天</span> </td> </tr> <tr style="line-height:21px"> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">time[无时区]</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">8字节</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">只用于一日内时间</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">00:00:00</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">24:00:00</span> </td> <td style="border-bottom:rgb(192,192,192) 1px solid; border-left:rgb(192,192,192) 1px solid; padding-bottom:3px; line-height:21px; padding-left:3px; padding-right:3px; border-collapse:collapse; font-size:12px; word-break:normal!important; border-top:rgb(192,192,192) 1px solid; border-right:rgb(192,192,192) 1px solid; padding-top:3px"> <span style="line-height:23px; font-size:13px">1毫秒/14位<br style="line-height:23px"><br style="line-height:23px"></span> </td> </tr> </tbody></table> </div> <div style=""> <div style="line-height:25px">postgres=# select current_timestamp; --可以看到精度6位，显示时区+8，和oracle的timestamp with time zone类型是一致的</div> <div style="line-height:25px"> now </div> <div style="line-height:25px">-------------------------------</div> <div style="line-height:25px">2012-09-06 14:04:51.363932+08</div> </div> <div style=""><br style="line-height:25px"></div> <div style=""> <div style="line-height:25px">postgres=# create table test_t (time_col time,date_col date,timestamp_col timestamp);类型区别</div> <div style="line-height:25px">CREATE TABLE</div> <div style="line-height:25px">postgres=# insert into test_t values(now(),now(),now());</div> <div style="line-height:25px">INSERT 0 1</div> <div style="line-height:25px">postgres=# select * from test_t;</div> <div style="line-height:25px"> time_col | date_col | timestamp_col </div> <div style="line-height:25px">-----------------+------------+----------------------------</div> <div style="line-height:25px">14:19:24.277477 | 2012-09-06 | 2012-09-06 14:19:24.277477</div> <div style="line-height:25px">(1 row)</div> </div> <div style=""><br style="line-height:25px"></div> <div style="">精度和时区的控制：</div> <div style=""> <div style="line-height:25px">postgres=# create table test_t1 (time_col time,date_col date,timestamp_col timestamp,timestamp_col0 timestamp(0) without time zone);</div> <div style="line-height:25px">CREATE TABLE</div> <div style="line-height:25px">postgres=# insert into test_t1 values(now(),now(),now(),now());</div> <div style="line-height:25px">INSERT 0 1</div> <div style="line-height:25px">postgres=# select * from test_t1;</div> <div style="line-height:25px"> time_col | date_col | timestamp_col | timestamp_col0 </div> <div style="line-height:25px">-----------------+------------+----------------------------+---------------------</div> <div style="line-height:25px">14:34:59.840947 | 2012-09-06 | 2012-09-06 14:34:59.840947 | 2012-09-06 14:35:00</div> </div> <div style=""> <div style="line-height:25px">postgres=# alter table test_t1 add column timestamp_col1 timestamp(0) with time zone;</div> <div style="line-height:25px">ALTER TABLE</div> <div style="line-height:25px">postgres=# insert into test_t1 values(now(),now(),now(),now(),now());</div> <div style="line-height:25px">INSERT 0 1</div> <div style="line-height:25px">postgres=# select * from test_t1;</div> <div style="line-height:25px"> time_col | date_col | timestamp_col | timestamp_col0 | timestamp_col1 </div> <div style="line-height:25px">-----------------+------------+----------------------------+---------------------+------------------------</div> <div style="line-height:25px">14:34:59.840947 | 2012-09-06 | 2012-09-06 14:34:59.840947 | 2012-09-06 14:35:00 |</div> <div style="line-height:25px">14:36:31.265579 | 2012-09-06 | 2012-09-06 14:36:31.265579 | 2012-09-06 14:36:31 | 2012-09-06 14:36:31+08</div> </div> <div style=""><br style="line-height:25px"></div> <div style=""><br style="line-height:25px"></div> <div style="">由此可见，timestamp本身不带时区，但是带精度，如果需要带时区，那么加上with time zone即可</div> <div style=""> <div style="line-height:25px">postgres=# select now()::timestamp(0) without time zone;</div> <div style="line-height:25px"> now </div> <div style="line-height:25px">---------------------</div> <div style="line-height:25px">2012-09-06 14:42:12</div> </div> </wbr>