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

来源：互联网发布：c语言中temp 编辑：程序博客网时间：2024/06/01 08:22

oracle

date 包含时间和日期类型，包括年月日，小时，分钟，秒。这个经常用，相信大家都熟悉

timestamp 这个类型很精确，是精确到微妙的时间单位。指定精度的小数位，最多为9位，默认6位

timestamp with time zone 这个除了包含了timestamp的外，又有了时区。

timstamp with local time zone 这个类型不包含时区偏移量，由用户当地会话时区确定。

interval year to month

interval day to second

timestamp可以通过to_timestamp_t转换到timestamp with time zone

tz_offset可以查看时区的差位移

查看数据库时区：

SELECT dbtimezone FROM dual;

select sessiontimezone from dual;

SQL> select extract(year from sysdate) from dual;--获取年份

EXTRACT(YEARFROMSYSDATE)

------------------------

2012

SQL> select extract(month from sysdate) from dual;--获取月份

EXTRACT(MONTHFROMSYSDATE)

-------------------------

SQL> select extract(day from sysdate) from dual;--获取日

EXTRACT(DAYFROMSYSDATE)

-----------------------

SQL> select from_tz(timestamp '2012-09-06 09:00:00','8:00') from dual; --timestamp转timestamp with time zone

FROM_TZ(TIMESTAMP'2012-09-0609:00:00','8:00')

---------------------------------------------------------------------------

06-SEP-12 09.00.00.000000000 AM +08:00

SQL> create table test_zone

2 (timestamp_dt TIMESTAMP,

3 zone_dt TIMESTAMP WITH TIME ZONE,

4 local_zone_dt TIMESTAMP WITH LOCAL TIME ZONE);

Table created.

SQL> insert into test_zone values (sysdate,sysdate,sysdate);

1 row created.

SQL> col TIMESTAMP_DT for a35

SQL> col ZONE_DT for a35

SQL> col LOCAL_ZONE_DT for a35

SQL> select * from test_zone;

TIMESTAMP_DT ZONE_DT LOCAL_ZONE_DT

----------------------------------- ----------------------------------- -----------------------------------

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

SQL> insert into test_zone values(to_date('20090101','yyyymmdd'),to_timestamp('20090101','yyyymmdd'),to_timestamp_tz('20090101 010101 -8:00','yyyymmdd hh24miss TZH:TZM'));

1 row created.

SQL> select * from test_zone;

TIMESTAMP_DT ZONE_DT LOCAL_ZONE_DT

----------------------------------- ----------------------------------- -----------------------------------

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

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

SQL> alter session set time_zone='+05:00';

Session altered.

SQL> select * from test_zone;

TIMESTAMP_DT ZONE_DT LOCAL_ZONE_DT (提前了三个小时)

----------------------------------- ----------------------------------- -----------------------------------

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

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

SQL> select * from v$timezone_names where rownum<10; --查看时区名称

TZNAME TZABBREV

---------------------------------------------------------------- ----------------------------------------------------------------

Africa/Abidjan LMT

Africa/Abidjan GMT

Africa/Accra LMT

Africa/Accra GMT

Africa/Accra GHST

Africa/Addis_Ababa LMT

Africa/Addis_Ababa ADMT

Africa/Addis_Ababa EAT

Africa/Algiers LMT

SQL> select tz_offset('Africa/Accra') from dual;

TZ_OFFS

-------

+00:00

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';--设置会话日期显示格式

SQL> select to_timestamp('20120909 00:01:02.123456789','yyyymmdd hh24:mi:ss.ff') from dual; --to_timestamp函数使用

TO_TIMESTAMP('2012090900:01:02.123456789','YYYYMMDDHH24:MI:SS.FF')

---------------------------------------------------------------------------

09-SEP-12 12.01.02.123456789 AM

SQL> select to_timestamp_tz('20120909 00:01:02.123456789 0:0','yyyymmdd hh24:mi:ss.ff tzh:tzm') from dual; --to_timestamp_tz使用

TO_TIMESTAMP_TZ('2012090900:01:02.1234567890:0','YYYYMMDDHH24:MI:SS.FFTZH:T

---------------------------------------------------------------------------

09-SEP-12 12.01.02.123456789 AM +00:00

postgresql时间类型：9.1.2版本

名字存储空间描述最低值最高值分辨率timestamp[无时区]8字节包括日期和时间4713 BC5874897AD1毫秒/14位timestamp[含时区] with time zone8字节日期和时间，带时区4713 BC5874897AD1毫秒/14位interval12字节时间间隔-178000000年178000000年1毫秒/14位date4字节只用于日期4713 BC32767AD1天time[无时区]8字节只用于一日内时间00:00:0024:00:001毫秒/14位

postgres=# select current_timestamp; --可以看到精度6位，显示时区+8，和oracle的timestamp with time zone类型是一致的

now

-------------------------------

2012-09-06 14:04:51.363932+08

postgres=# create table test_t (time_col time,date_col date,timestamp_col timestamp);类型区别

CREATE TABLE

postgres=# insert into test_t values(now(),now(),now());

INSERT 0 1

postgres=# select * from test_t;

time_col | date_col | timestamp_col

-----------------+------------+----------------------------

14:19:24.277477 | 2012-09-06 | 2012-09-06 14:19:24.277477

(1 row)

精度和时区的控制：

postgres=# create table test_t1 (time_col time,date_col date,timestamp_col timestamp,timestamp_col0 timestamp(0) without time zone);

CREATE TABLE

postgres=# insert into test_t1 values(now(),now(),now(),now());

INSERT 0 1

postgres=# select * from test_t1;

time_col | date_col | timestamp_col | timestamp_col0

-----------------+------------+----------------------------+---------------------

14:34:59.840947 | 2012-09-06 | 2012-09-06 14:34:59.840947 | 2012-09-06 14:35:00

postgres=# alter table test_t1 add column timestamp_col1 timestamp(0) with time zone;

ALTER TABLE

postgres=# insert into test_t1 values(now(),now(),now(),now(),now());

INSERT 0 1

postgres=# select * from test_t1;

time_col | date_col | timestamp_col | timestamp_col0 | timestamp_col1

-----------------+------------+----------------------------+---------------------+------------------------

14:34:59.840947 | 2012-09-06 | 2012-09-06 14:34:59.840947 | 2012-09-06 14:35:00 |

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

由此可见，timestamp本身不带时区，但是带精度，如果需要带时区，那么加上with time zone即可

postgres=# select now()::timestamp(0) without time zone;

now

---------------------

2012-09-06 14:42:12