oracle数值类型--DATE、TIMESTAMP和INTERVAL类型

最新推荐文章于 2024-04-18 15:57:18 发布
最新推荐文章于 2024-04-18 15:57:18 发布
阅读量811 收藏 1
点赞数
文章标签： 数据库 python
原文链接：https://my.oschina.net/u/1862478/blog/1933621
版权

2019独角兽企业重金招聘Python工程师标准>>> hot3.png

Oracle固有数据类型DATE、TIMESTAMP和INTERVAL是紧密相关的。DATE和TIMESTAMP类型存储精度可变的固定日期/时间。INTERVAL类型可以很容易地存储一个时间量，如“8个小时”或“30天”。
许多新应用都在使用TIMESTAMP类型，这有两个原因：一方面它支持小数秒，而DATE类型不支持；另一方面TIMESTAMP类型支持时区，这也是DATE类型力所不能及的。

1格式

以某种格式对数据库中的数据进行格式化，以满足你的要求。
告诉数据库如何将一个输入串转换为DATE、TIMESTAMP或INTERVAL。

格式对数据如何存储根本没有任何影响。格式只是用于将存储DATE所用的二进制格式转换为一个串，或者将一个串转换为用于存储DATE的二进制格式。对于TIMESTAMP和INTERVAL也是如此。

将一个表示DATE、TIMESTAMP或INTERVAL的 串发送到数据库时就可以使用格式。不要依赖于默认日期格式，默认格式会（而且很可能）在将来每个时刻被另外每个人所修改。如果你依赖于一个默认日期格式， 而这个默认格式有了变化，你的应用可能就会受到影响。如果无法转换日期，应用可能会向最终用户返回一个错误；或者更糟糕的是，它可能会悄悄地插入错误的数 据。考虑以下INSERT语句，它依赖于一个默认的日期掩码：

Insert into t ( date_column ) values ( '01/02/03' );

假设应用依赖于必须有默认日期掩码DD/MM/YY。这就表示2003年2月1日。现在，假设有人认为正确而且适当的日期格式应该是MM/DD/YY。突然之间，原来的日期就会变成2003年1月2日。或者有人认为YY/MM/DD才对，现在的日期就会变成2001年2月3日。简单地说，如果日期串没有带相应的日期格式，就会有多种解释方法。这个INSERT语句最好写作：

Insert into t ( date_column ) values ( to_date( '01/02/2003', 'DD/MM/YYYY' ) );

使用一个4字符的年份。
从数据库取出的数据也同样存在上述问题。如果你执行SELECT DATE_COLUMN FROM T，并在应用中把这一列获取到一个串中，就应该对其应用一个显示的日期格式。不论你的应用期望何种格式，都应该在这里显式指定。否则，如果将来某个时刻有人修改了默认日期格式，你的应用就可能会崩溃，或者有异样的表现。

2 DATE类型

DATE类型是一个7字节的定宽日期/时间数据类型。它总是包含7个属性，包括：世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。Oracle使用一种内部格式来表示这个信息。

scott@ORCL>create table t ( x date );

表已创建。

scott@ORCL>alter session set nls_date_language='american' ;

会话已更改。

scott@ORCL>insert into t (x) values
  2  ( to_date( '25-jun-2005 12:01:00',
  3  'dd-mon-yyyy hh24:mi:ss' ) );

已创建 1 行。

scott@ORCL>select x, dump(x) d from t;

X           D
----------- ---------------------- 
25-JUN-05   Typ=12 Len=7: 120,105,6,25,13,2,1

世纪和年份字节（DUMP输出中的120,105）采用一种“加100”（excess-100）表示法来存储。必须将其减去100来确定正确的世纪和年份。之所以采用加100表示法，这是为了支持BC和AD日期。如果从世纪字节减去100得到一个负数，则是一个BC日期，例如：

scott@ORCL>insert into t (x) values
  2  ( to_date( '01-jan-4712bc',
  3  'dd-mon-yyyybc hh24:mi:ss' ) );

已创建 1 行。

scott@ORCL>select x, dump(x) d from t;

X            D
---------    ----------------------------------
25-JUN-05    Typ=12 Len=7: 120,105,6,25,13,2,1
01-JAN-12    Typ=12 Len=7: 53,88,1,1,1,1,1

因此，插入01-JAN-4712BC时，世纪字节是53，而53-100=-47，这才是我们插入的真实世纪。由于这是一个负数，所以我们知道它是一个BC日期。这种存储格式还允许日期以一种二进制方式自然地排序。由于4712 BC小于4710 BC，我们希望能有一种支持这种顺序的二进制表示。通过转储这两个日期，可以看到01-JAN-4710BC比4712 BC中的同一天“更大”，所以它们确实能正确地排序，并很好地进行比较：

scott@ORCL>insert into t (x) values
  2  ( to_date( '01-jan-4710bc',
  3  'dd-mon-yyyybc hh24:mi:ss' ) );

已创建 1 行。

scott@ORCL>select x, dump(x) d from t;

X            D
------------ ----------------------------------
25-JUN-05    Typ=12 Len=7: 120,105,6,25,13,2,1
01-JAN-12    Typ=12 Len=7: 53,88,1,1,1,1,1
01-JAN-10    Typ=12 Len=7: 53,90,1,1,1,1,1

接下来两个字段是月份和日字节，它们会自然地存储。因此，6月25日的月份字节就是6，日字节是25。小时、分钟和秒字段采用“加1”（excess-1）表示法存储，这说明必须将各个部分减1，才能得到实际的时间。因此，午夜0点在日期字段中就表示为12.12.1。

这种7字节格式能自然地排序。这一个7字节字段，可以采用一种二进制方式按从小到大（或从大到小）的顺序非常高效地进行排序。另外，这种结构允许很容易地进行截断，而无需把日期转换为另外某种格式。例如，要截断刚才存储的日期（25-JUN-2005 12.:01:00）来得到日信息（去掉小时、分钟和秒字段），这相当简单。只需将尾部的3个字节设置为12.12.1，就能很好地清除时间分量。考虑一个全新的表T，并执行以下插入：

scott@ORCL>create table t ( what varchar2(12), x date );

表已创建。

scott@ORCL>insert into t (what, x) values
  2  ( 'orig', to_date( '25-jun-2005 12:01:00','dd-mon-yyyy hh24:mi:ss' ) );

已创建 1 行。

scott@ORCL>insert into t (what, x)
  2  select 'minute', trunc(x,'mi') from t
  3  union all
  4  select 'day', trunc(x,'dd') from t
  5  union all
  6  select 'month', trunc(x,'mm') from t
  7  union all
  8  select 'year', trunc(x,'y') from t
  9  /

已创建4行。

scott@ORCL>select what, x, dump(x,12) d from t;

WHAT                     X
------------------------ ------------
D
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
orig                     25-JUN-05
Typ=12 Len=7: 120,105,6,25,13,2,1

minute                   25-JUN-05
Typ=12 Len=7: 120,105,6,25,13,2,1

day                      25-JUN-05
Typ=12 Len=7: 120,105,6,25,1,1,1

month                    01-JUN-05
Typ=12 Len=7: 120,105,6,1,1,1,1

year                     01-JAN-05
Typ=12 Len=7: 120,105,1,1,1,1,1

要把这个日期截断，只取到年份，数据库所要做的只是在后5个字节上置1，这是一个非常快速的操作。现在我们就有一个可排序、可比较的DATE字段，它能截断到年份级，而且我们可以尽可能高效地做到这一点。不过，许多人并不是使用TRUNC，而是在TO_CHAR函数中使用一个日期格式。例如，他们会这样用：

Where to_char(date_column,'yyyy') = '2005'

而不是

Where trunc(date_column,'y') = to_date('01-jan-2005','dd-mon-yyyy')

后者不仅有更出色的表现，而且占有的资源更少。如果建立ALL_OBJECTS的一个副本，另存储其中的CREATED列：

scott@ORCL>create table t
  2  as
  3  select created from all_objects;

表已创建。

scott@ORCL>exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL 过程已成功完成。

然后，启用SQL_TRACE，我们反复使用上述两种技术查询这个表，可以看到以下结果：

select count(*)
from
t where to_char(created,'yyyy') = '2005'

select count(*)
from
t where trunc(created,'y') = to_date('01-jan-2005','dd-mon-yyyy')

可以看到存在明显的差异。与使用TRUNC相比，使用TO_CHAR所用的CPU时间与前者相差一个数量级（即相差12倍）。这是因为TO_CHAR必须把日期转换为一个串，这要使用一个更大的代码路径，并利用当前的所有NLS来完成这个工作。然后必须执行一个串与串的比较。另一方面，TRUNC只需把后5个字节设置为1。然后将两个7字节的二进制数进行比较，就大功告成了。因此，如果只是要截断一个DATE列，应该避免使用TO_CHAR。
另外，甚至要尽可能完全避免对DATE列应用函数。把前面的例子 可以看到其目标是获取2005年的所有数据。那好，如果CREATED上有一个索引，而且表中只有很少一部分CREATED值是2005年的时间，会怎么样呢？我们可能希望能够使用这个索引，为此要使用一个简单的谓词来避免在数据库列上应用函数：

select count(*) from t
where created >= to_date('01-jan-2005','dd-mon-yyyy')
and created < to_date('01-jan-2006','dd-mon-yyyy');

这样有两个好处：
这样一来就可以考虑使用CREATED上的索引了。
根本无需调用TRUNC函数，这就完全消除了相应的开销。

这里使用的是区间比较而不是TRUNC或TO_CHAR，这种技术同样适用于TIMESTAMP类型。如果能在查询中避免对一个数据库列应用函数，就应该全力这样做。一般来讲，避免使用函数会有更好的性能，而且允许优化器在更多的访问路径中做出选择。

1. 向DATE增加或减去时间
常用的技术有3种：
向DATE增加一个NUMBER。把DATE加1是增加1天的一种方法。因此，向DATE增加12.24就是增加1个小时，依此类推。
使用INTERVAL类型来增加时间单位。INTERVAL类型支持两种粒度：年和月，或日/小时/分钟/秒。
使用内置的ADD_MONTHS函数增加月。

总的来讲，使用Oracle DATE类型时 建议：
使用NUMTODSINTERVAL内置函数来增加小时、分钟和秒。
加一个简单的数来增加天。
使用ADD_MONTHS内置函数来增加月和年。
我建议不要使用NUMTOYMINTERVAL函数。其原因与这个函数如何处理月末日期有关。

ADD_MONTHS函数专门处理月末日期。它实际上会为我们完成日期的“舍入”。

scott@ORCL>alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

会话已更改。


scott@ORCL>select to_date('29-feb-2000','dd-mon-yyyy') dt from dual;
select to_date('29-feb-2000','dd-mon-yyyy') dt from dual
               *
第 1 行出现错误:
ORA-01843: 无效的月份


scott@ORCL>alter session set nls_date_language='american' ;

会话已更改。

scott@ORCL>select dt, add_months(dt,1)
  2  from (select to_date('29-feb-2000','dd-mon-yyyy') dt from dual )
  3  /

DT                      ADD_MONTHS(DT,1)
----------------------- -----------------------
29-feb-2000 00:00:00    31-mar-2000 00:00:00

scott@ORCL>select dt, add_months(dt,1)
  2  from (select to_date('28-feb-2001','dd-mon-yyyy') dt from dual )
  3  /

DT                      ADD_MONTHS(DT,1)
----------------------- -----------------------
28-feb-2001 00:00:00    31-mar-2001 00:00:00

scott@ORCL>select dt, add_months(dt,1)
  2  from (select to_date('30-jan-2001','dd-mon-yyyy') dt from dual )
  3  /

DT                      ADD_MONTHS(DT,1)
----------------------- -----------------------
30-jan-2001 00:00:00    28-feb-2001 00:00:00

scott@ORCL>select dt, add_months(dt,1)
  2  from (select to_date('30-jan-2000','dd-mon-yyyy') dt from dual )
  3  /

DT                      ADD_MONTHS(DT,1)
----------------------- -----------------------
30-jan-2000 00:00:00    29-feb-2000 00:00:00

向2000年2月29日增加1个月，得到的是2000年3月31日。2月29日是该月的最后一天，所以ADD_MONTHS返回了下一个月的最后一天。注意向2000年和2001年的1月30日增加1个月时，会分别得到2000年和2001年2月的最后一天（分别是2月29日和2月28日）。

如果与增加一个间隔 相比较，会看到完全不同的结果：

scott@ORCL>select dt, dt+numtoyminterval(1,'month')
  2  from (select to_date('29-feb-2000','dd-mon-yyyy') dt from dual )
  3  /

DT                      DT+NUMTOYMINTERVAL(1,'M
----------------------- -----------------------
29-feb-2000 00:00:00    29-mar-2000 00:00:00

scott@ORCL>select dt, dt+numtoyminterval(1,'month')
  2  from (select to_date('28-feb-2001','dd-mon-yyyy') dt from dual )
  3  /

DT                      DT+NUMTOYMINTERVAL(1,'M
----------------------- -----------------------
28-feb-2001 00:00:00    28-mar-2001 00:00:00

得到的日期并不是下一个月的最后一天，而只是下一个月的同一天。

scott@ORCL>select dt, dt+numtoyminterval(1,'month')
  2  from (select to_date('30-jan-2001','dd-mon-yyyy') dt from dual )
  3  /
select dt, dt+numtoyminterval(1,'month')
             *
第 1 行出现错误:
ORA-01839: 指定月份的日期无效


scott@ORCL>select dt, dt+numtoyminterval(1,'month')
  2  from (select to_date('30-jan-2000','dd-mon-yyyy') dt from dual )
  3  /
select dt, dt+numtoyminterval(1,'month')
             *
第 1 行出现错误:
ORA-01839: 指定月份的日期无效
2. 得到两个日期之差

MONTHS_BETWEEN，它会返回表示两个日期相隔月数的一个数（包括月小数）。

利用INTERVAL类型，用另一个方法来查看两个日期之间的逝去时间。

scott@ORCL>select dt2-dt1 ,
  2  months_between(dt2,dt1) months_btwn,
  3  numtodsinterval(dt2-dt1,'day') days,
  4  numtoyminterval(months_between(dt2,dt1),'month') months
  5  from (select to_date('29-feb-2000 01:02:03','dd-mon-yyyy hh24:mi:ss') dt1,
  6                                             to_date('12-mar-2001 12:22:33','
dd-mon-yyyy hh24:mi:ss') dt2
  7                     from dual );

   DT2-DT1 MONTHS_BTWN DAYS
                   MONTHS
---------- ----------- ---------------------------------------------------------
------------------ -------------------------------------------------------------
--------------
377.472569  12.4668571 +000000377 11:20:30.000000000
                   +000000001-00

这些都是“正确”的值，但是对我们来说都没有大用。大多数应用都更愿意显示日期之间相隔的年数、月数、天数、小时数、分钟数和秒数。通过使用前述函数的一个组合，就可以实现这个目标。我们将选出两个间隔：一个是年和月间隔，另一个是日/小时/分钟/秒间隔。我们使用MONTHS_BETWEEN内置函数来确定两个日期之间相隔的月数（包括小数），然后使用NUMTOYMINTERVAL内置函数将这个数转换为年数和月数。另外，使用TRUNC得到两个日期相隔月数中的整数部分，再使用ADD_MONTHS内置函数将dt1增加12个月（这会得到‘28-feb-2001 01:02:03），再从两个日期中的较大者（dt2）减去这个计算得到的日期，从而得到两个日期之间的天数和小时数：

scott@ORCL>select numtoyminterval(months_between(dt2,dt1),'month') years_months,

  2  numtodsinterval(dt2-add_months( dt1, trunc(months_between(dt2,dt1)) ),'day'
 ) days_hours
  3  from (select to_date('29-feb-2000 01:02:03','dd-mon-yyyy hh24:mi:ss') dt1,
  4  to_date('12-mar-2001 12:22:33','dd-mon-yyyy hh24:mi:ss') dt2
  5  from dual ) ;

YEARS_MONTHS    DAYS_HOURS
------------    -----------------------------
+000000001-00   +000000012  11:20:30.000000000

现在就很清楚了，这两个日期之间相隔1年、12天、11小时、20分钟和30秒。

3 TIMESTAMP类型

TIMESTAMP类型与DATE非常类似，只不过另外还支持小数秒和时区。

1. TIMESTAMP

TIMESTAMP(n)

这里N是可选的，用于指定TIMESTAMP中秒分量的小数位数，可以取值为0～9.如果指定0，TIMESTAMP在功能上则与DATE等价，它们实际上会以同样的方式存储相同的值：

scott@ORCL>create table t
  2  ( dt date,
  3  ts timestamp(0)
  4  )
  5  /

表已创建。

scott@ORCL>insert into t values ( sysdate, systimestamp );

已创建 1 行。


scott@ORCL>select dump(dt,12) dump, dump(ts,12) dump from t;

DUMP
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
DUMP
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
Typ=12 Len=7: 120,118,8,29,16,30,6
Typ=180 Len=7: 120,118,8,29,16,30,6

这两个数据类型是不同的（由TYP=字段可知），但是它们采用了相同的方式存储数据。

如果指定要保留几位秒小数，TIMESTAMP数据类型与DATE类型的长度将会不同，例如：

scott@ORCL>create table t
  2  ( dt date,
  3  ts timestamp(9)
  4  )
  5  /

表已创建。

scott@ORCL>insert into t values ( sysdate, systimestamp );

已创建 1 行。

scott@ORCL>select dump(dt,12) dump, dump(ts,12) dump from t;

DUMP
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
DUMP
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
Typ=12 Len=7: 120,118,8,29,16,32,51
Typ=180 Len=11: 120,118,8,29,16,32,51,52,39,112,192

现在TIMESTAMP占用12 字节的存储空间，最后额外的4个字节包含着小数秒，通过查看所存储的时间就能看出：

scott@ORCL>alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
会话已更改。

scott@ORCL>alter session set nls_date_language='american' ;
会话已更改。

scott@ORCL>select * from t;
DT                      TS
----------------------- --------------------------------------------------------
29-aug-2018 15:31:50    29-AUG-18 03.31.50.875000000 PM


scott@ORCL>select dump(ts) dump from t;
DUMP
----------------------------------------
Typ=180 Len=11: 120,118,8,29,16,32,51,52,39,112,192

存储的小数秒都在最后4个字节中。

2. 向TIMESTAMP增加或减去时间

DATE执行日期算术运算所用的技术同样适用于TIMESTAMP，但是在很多情况下，TIMESTAMP会转换为一个DATE，例如：

scott@ORCL>alter session set nls_date_language='american' ;

会话已更改。

scott@ORCL>alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

会话已更改。

scott@ORCL>select systimestamp ts, systimestamp+1 dt
  2  from dual;

TS                                  DT
----------------------------------- -----------------------
29-AUG-18 04.01.17.096000 PM +08:00 30-aug-2018 16:01:17

这里加1实际上将SYSTIMESTAMP推进了1天，但是小数秒没有了，另外时区信息也没有了。

这里使用 INTERVAL 更有意义：

scott@ORCL>select systimestamp ts, systimestamp +numtodsinterval(1,'day') dt
  2  from dual;

TS                                  DT
----------------------------------- ---------------------------------------------------------
29-AUG-18 04.02.33.960000 PM +08:00 30-AUG-18 04.02.33.960000000 PM +08:00

使用返回一个INTERVAL类型的函数能保持TIMESTAMP的真实度。使用TIMESTAMP时要特别当心，以避免这种隐式转换。
向TIMESTAMP增加月间隔或年间隔时存在相关的警告。如果所得到的“日期”不是一个合法日期，这个操作就会失败。

3. 得到两个TIMESTAMP之差

将DATE相减的结果是一个NUMBER，但TIMESTAMP相减的结果却是一个INTERVAL：

scott@ORCL>select dt2-dt1
  2  from (select to_timestamp('29-feb-2000 01:02:03.122000',
  3     'dd-mon-yyyy hh24:mi:ss.ff') dt1,
  4     to_timestamp('12-mar-2001 12:22:33.000000',
  5     'dd-mon-yyyy hh24:mi:ss.ff') dt2
  6  from dual )
  7  /

DT2-DT1
---------------------------------------------------------------------------
+000000377 11:20:29.878000000

两个TIMESTAMP值之差是一个INTERVAL，而且这里显示了二者之间相隔的天数已经小时/分钟/秒数。如果想得到二者之间相差的年数和月数，可以使用以下查询（这个查询类似于先前用于日期的查询）：

scott@ORCL>select numtoyminterval
  2  (months_between(dt2,dt1),'month')
  3  years_months,
  4  dt2-add_months(dt1,trunc(months_between(dt2,dt1)))
  5  days_hours
  6  from (select to_timestamp('29-feb-2000 01:02:03.122000',
  7     'dd-mon-yyyy hh24:mi:ss.ff') dt1,
  8     to_timestamp('12-mar-2001 12:22:33.000000',
  9     'dd-mon-yyyy hh24:mi:ss.ff') dt2
 10  from dual )
 11  /

YEARS_MONTHS              DAYS_HOURS
------------------------- ---------------------
+000000001-00             +000000012 11:20:30.000000000

在这种情况下，由于使用了ADD_MONTHS，DT1会隐式转换为一个DATE类型，这样就丢失了小数秒。为了保住小数秒，我们必须编写更多的代码。

4. TIMESTAMP WITH TIME ZONE类型

TIMESTAMP WITH TIME ZONE类型继承了TIMESTAMP类型的所有特点，并增加了时区支持。TIMESTAMP WITH TIME ZONE类型占12字节的存储空间，在此有额外的2个字节用于保留时区信息。它在结构上与TIMESTAMP的差别只是增加了这2个字节：

scott@ORCL>create table t
  2  (
  3  ts timestamp,
  4  ts_tz timestamp with time zone
  5  )
  6  /

表已创建。

scott@ORCL>insert into t ( ts, ts_tz )
  2  values ( systimestamp, systimestamp );

已创建 1 行。

scott@ORCL>select * from t;

TS                            TS_TZ
----------------------------- ------------------------------
29-AUG-18 05.54.07.695000 PM  29-AUG-18 05.54.07.695000 PM +08:00

scott@ORCL>select dump(ts), dump(ts_tz) from t;

DUMP(TS)
----------------------------------------
DUMP(TS_TZ)
----------------------------------------
Typ=180 Len=11: 120,118,8,29,18,55,8,41,108,219,192
Typ=181 Len=13: 120,118,8,29,10,55,8,41,108,219,192,28,60

获取数据时，默认的TIMESTAMP WITH TIME ZONE格式包括有时区信息。

存储数据时，TIMESTAMP WITH TIME ZONE会在数据中存储指定的时区。时区成为数据本身的一部分。注意TIMESTAMP WITH TIME ZONE字段如何存储小时、分钟和秒，这里采用了加1表示法。TIMESTAMP WITH TIME ZONE为它增加了4个小时，从而存储为GWT（也称为UTC）时间。获取时，会使用尾部的2个字节适当地调整TIMESTAMP值。

数据库能存储多个时区的数据：

scott@ORCL>create table t
  2  ( ts1 timestamp with time zone,
  3  ts2 timestamp with time zone
  4  )
  5  /

表已创建。

scott@ORCL>insert into t (ts1, ts2)
  2  values ( timestamp'2005-06-05 12:02:32.212 US/Eastern',
  3           timestamp'2005-06-05 12:02:32.212 US/Pacific' );


已创建 1 行。

并对这些数据执行正确的TIMESTAMP运算：

scott@ORCL>select ts1-ts2 from t;

TS1-TS2
---------------------------------------------------------------------------
-000000000 03:00:00.000000

因为这两个时区之间有3个小时的时差，尽管它们显示的是“同样的时间”——12:02:32:212，但是从报告的时间间隔来看确实存在3小时的时差。在TIMESTAMP WITH TIME ZONE类型上执行TIMESTAMP运算时，Oracle会自动地把两个类型首先转换为UTC时间，然后执行运算。

5. TIMESTAMP WITH LOCAL TIME ZONE类型

这种类型与TIMESTAMP类型的工作是类似的。这是一个7字节或12字节的字段（取决于TIMESTAMP的精度），但是会进行规范化，在其中存入数据库的时区。

首先，创建一个包括3列的表，这3列分别是一个DATE列、一个TIMESTAMP WITH TIME ZONE列和一个TIMESTAMP WITH LOCAL TIME ZONE列，然后向这3列插入相同的值：

scott@ORCL>create table t
  2  ( dt date,
  3  ts1 timestamp with time zone,
  4  ts2 timestamp with local time zone
  5  )
  6  /

表已创建。

scott@ORCL>insert into t (dt, ts1, ts2)
  2  values ( timestamp'2005-06-05 12:02:32.212 US/Pacific',
  3  timestamp'2005-06-05 12:02:32.212 US/Pacific',
  4  timestamp'2005-06-05 12:02:32.212 US/Pacific' );

已创建 1 行。

scott@ORCL>select dbtimezone from dual;

DBTIMEZONE
------------
+00:00

现在，将这些值转储如下：

scott@ORCL>select dump(dt), dump(ts1), dump(ts2) from t;

DUMP(DT)
----------------------------------------
DUMP(TS1)
----------------------------------------
DUMP(TS2)
----------------------------------------
Typ=12 Len=7: 120,105,6,5,13,3,33
Typ=181 Len=13: 120,105,6,5,20,3,33,12,162,221,0,137,156
Typ=231 Len=11: 120,105,6,5,20,3,33,12,162,221,0

可以看到，在这个例子中，会存储3种完全不同的日期/时间表示：
DT：这一列存储了日期/时间。时区和小数秒没有了，因为使用的是DATE类型。这里根本不会执行时区转换。
TS1：这一列保留了TIME ZONE信息，并规范化为该TIME ZONE相应的UTC时间。
TS2：这里认为这个列的时区就是数据库时区。这里采用了加1表示法：取得实际时间时要记住减1。

由于TS1列在最后2字节保留了原来的时区，获取时我们会看到以下结果：

scott@ORCL>select ts1, ts2 from t;

TS1                                       TS2
---------------------------------------   ----------------------------------------------------------
05-JUN-05 12.02.32.212000 PM US/PACIFIC   06-JUN-05 03.02.32.212000 AM

数据库应该能显示这个信息，但是有LOCAL TIME ZONE（数据库时区）的TS2列只显示了数据库时区的时间，并认为这就是这一列的时区（实际上，这个数据库中有LOCAL TIME ZONE的所有列的时区都是数据库时区）。

如果不需要记住源时区，只需要这样一种数据类型，要求能对日期/时间类型提供一致的全球性处理，那么TIMESTAMP WITH LOCAL TIME ZONE对大多数应用来说已经能提供足够的支持。另外，TIMESTAMP(0) WITH LOCAL TIME ZONE是与DATE类型等价但提供了时区支持的一种类型；它占用7字节存储空间，允许存储按UTC形式“规范化”的日期。

关于TIMESTAMP WITH LOCAL TIME ZONE类型有一个警告，一旦创建有这个列的表，将不能修改数据库的时区。

scott@ORCL>alter database set time_zone = 'PST';
alter database set time_zone = 'PST'
*
第 1 行出现错误:
ORA-30079: 当数据库有 TIMESTAMP WITH LOCAL TIME ZONE 列时不能变更数据库时区

4 INTERVAL类型

这是表示一段时间或一个时间间隔的一种方法。

YEAR TO MONTH类型，它能存储按年和月指定的一个时段；

DATE TO SECOND类型，它能存储按天、小时、分钟和秒（包括小数秒）指定的时段。

EXTRACT内置函数 可以处理TIMESTAMP和INTERVAL，并从中返回各部分信息，如从TIMESTAMP返回时区，从INTERVAL返回小时/天/分钟：

scott@ORCL>select dt2-dt1
  2  from (select to_timestamp('29-feb-2000 01:02:03.122000',
  3     'dd-mon-yyyy hh24:mi:ss.ff') dt1,
  4     to_timestamp('12-mar-2001 12:22:33.000000',
  5     'dd-mon-yyyy hh24:mi:ss.ff') dt2
  6     from dual )
  7  /

DT2-DT1
---------------------------------------------------------------------------
+000000377 11:20:29.878000000

可以使用EXTRACT来查看，它能很轻松地取出其中的各部分信息：

scott@ORCL>select extract( day from dt2-dt1 ) day,
  2     extract( hour from dt2-dt1 ) hour,
  3     extract( minute from dt2-dt1 ) minute,
  4     extract( second from dt2-dt1 ) second
  5  from (select to_timestamp('29-feb-2000 01:02:03.122000',
  6     'dd-mon-yyyy hh24:mi:ss.ff') dt1,
  7     to_timestamp('12-mar-2001 12:22:33.000000',
  8     'dd-mon-yyyy hh24:mi:ss.ff') dt2
  9     from dual )
 10  /

       DAY       HOUR     MINUTE     SECOND
---------- ---------- ---------- ----------
       377         11         20     29.878

YEAR TO MONTH和DAY TO SECOND间隔时所用的NUMTOYMINTERVAL和NUMTODSINTERVAL,是创建INTERVAL类型实例最容易的方法，远远胜于串转换函数。
INTERVAL类型不只是可以用于存储时段，还可以以某种方式存储“时间”。例如，如果希望存储一个特定的日期时间，可以使用DATE或TIMESTAMP类型。但是如果只想存储上午8:00这个时间呢？INTERVAL类型就很方便（尤其是INTERVAL DAY TO SECOND类型）。

1. INTERVAL YEAR TO MONTH
INTERVAL YEAR(n) TO MONTH

在此N是一个可选的位数（用以支持年数），可取值为0～9，默认为2（表示年数可为0～99）。这就允许你存储任意大小的年数（最多可达9位）和月数。也可以用 NUMTOYMINTERVAL 函数来创建这种类型的INTERVAL实例。

例如，要创建一个5年2个月的时间间隔，可以使用以下命令：

scott@ORCL>select numtoyminterval(5,'year')+numtoyminterval(2,'month')
  2  from dual;

NUMTOYMINTERVAL(5,'YEAR')+NUMTOYMINTERVAL(2,'MONTH')
---------------------------------------------------------------------------
+000000005-02

或者，利用1年有12个月这个事实，可以使用一个调用，并使用以下命令：

scott@ORCL>select numtoyminterval(5*12+2,'month')
  2  from dual;

NUMTOYMINTERVAL(5*12+2,'MONTH')
---------------------------------------------------------------------------
+000000005-02

可以用另一个函数TO_YMINTERVAL将一个串转换为一个年/月INTERVAL类型：

scott@ORCL>select to_yminterval( '5-2' ) from dual;

TO_YMINTERVAL('5-2')
---------------------------------------------------------------------------
+000000005-02

还可以直接在SQL中使用INTERVAL类型，而不用这些函数：

scott@ORCL>select interval '5-2' year to month from dual;

INTERVAL'5-2'YEARTOMONTH
---------------------------------------------------------------------------
+05-02
2. INTERVAL DAY TO SECOND
INTERVAL DAY(n) TO SECOND(m)

在此N是一个可选的位数，支持天数分量，取值为0～9，默认为2。M是秒字段小时部分中保留的位数，其中为0～9，默认为6 同样，可以用 NUMTODSINTERVAL 函数来创建这种类型的INTERVAL实例：

scott@ORCL>select numtodsinterval( 12, 'day' )+
  2  numtodsinterval( 2, 'hour' )+
  3  numtodsinterval( 3, 'minute' )+
  4  numtodsinterval( 2.3312, 'second' )
  5  from dual;

NUMTODSINTERVAL(12,'DAY')+NUMTODSINTERVAL(2,'HOUR')+NUMTODSINTERVAL(3,'MINU
---------------------------------------------------------------------------
+000000012 02:03:02.331200000

或者只是：

scott@ORCL>select numtodsinterval( 12*86400+2*3600+3*60+2.3312, 'second' )
  2  from dual;

NUMTODSINTERVAL(12*86400+2*3600+3*60+2.3312,'SECOND')
---------------------------------------------------------------------------
+000000012 02:03:02.331200000

这里利用了一天有89,400秒，一小时有3,600秒等事实

可以使用TO_DSINTERVAL函数将一个串转换为一个DAY TO SECOND间隔：

scott@ORCL>select to_dsinterval( '12 02:03:02.3312' )
  2  from dual;

TO_DSINTERVAL('1202:03:02.3312')
---------------------------------------------------------------------------
+000000012 02:03:02.331200000

或者只是在SQL本身中使用INTERVAL变量：

scott@ORCL>select interval '12 02:03:02.3312' day to second
  2  from dual;

INTERVAL'1202:03:02.3312'DAYTOSECOND
---------------------------------------------------------------------------
+12 02:03:02.331200

 

转载于:https://my.oschina.net/u/1862478/blog/1933621

weixin_33862514
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle的数据类型date,oracle数值类型--DATE、TIMESTAMP和INTERVAL类型
weixin_42097819的博客
04-15 945
Oracle固有数据类型DATE、TIMESTAMP和INTERVAL是紧密相关的。DATE和TIMESTAMP类型存储精度可变的固定日期/时间。INTERVAL类型可以很容易地存储一个时间量，如“8个小时”或“30天”。许多新应用都在使用TIMESTAMP类型，这有两个原因：一方面它支持小数秒，而DATE类型不支持；另一方面TIMESTAMP类型支持时区，这也是DATE类型力所不能及的。1格式以...
SYSTIMESTAMP INTERVAL Processing
allway2的博客
08-14 868
This is just a quick post to try to encourage the use of the INTERVAL function when adjusting (sys)timestamps (or dates). I thought this would be better expressed through a quick script with comments ...
Oracle中的时间戳转换与使用
最新发布
lxz458912769的博客
04-18 2062
在Oracle数据库中，时间戳是一个非常重要的数据类型，用于记录和管理时间信息。Oracle提供了多种时间戳数据类型，以及丰富的函数和操作符，用于时间戳的转换、计算和比较。本文将介绍Oracle中各种时间戳的转换与使用，帮助读者更好地理解和应用这些功能。总之，Oracle提供了丰富的时间戳数据类型和转换功能，使得时间戳的管理和使用变得更加灵活和高效。在Oracle中，时间戳常用于各种场景，如日期计算、时间范围查询、时区转换等。在Oracle中，可以使用多种函数和操作符进行时间戳的转换。
oracle数据库interval使用,Oracle数据库中时间间隔型数据分析
weixin_30915487的博客
04-05 760
在9i 版本以前，Oracle 没有内置的方式来记录时间的流逝。DATE型数据用来记录单独的时间点；但是要表达一个时间量(也就是一个间隔)，数据库的设计者就必须把时间间隔转换成原始单位秒，然后用一个NUMBER列来保存它。虽然NUMBER这个数据类型可以以秒为单位准确地表示时间，但是它使得时间的计算变得很困难。比如，60秒是1分钟，60分钟是1个小时，24个小时等于1天——这些数字在以十进制为基础...
postgresql 时间运算
dbsjack的博客
11-26 5214
在PostgreSQL中可以直接对时间进行加减运算： SELECT now()::timestamp + '1 year';  --当前时间加1年 SELECT now()::timestamp + '1 month';  --当前时间加一个月 SELECT now()::timestamp + '1 day';  --当前时间加一天 SELECT now()::timestamp + '1 ho...
oracle sysdate - interval '7',Subtracting Dates in Oracle - Number or Interval Datatype?
weixin_42520909的博客
04-13 109
You get the syntax error because the date math does not return a NUMBER, but it returns an INTERVAL:SQL> SELECT DUMP(SYSDATE - start_date) from test;DUMP(SYSDATE-START_DATE)------------------------...
oracle扫盲-第一讲-创建和管理表PPT课件.ppt
11-23
Oracle数据库中有多种数据类型，包括VARCHAR2、CHAR、NUMBER、DATE、LONG、CLOB、ROWID、TIMESTAMP、INTERVAL YEAR TO MONTH、INTERVAL DAY TO SECOND等。每种数据类型都有其特点和使用场景。 知识点2：CREATE ...
Oracle的to-date函数.pdf
12-12
在Oracle数据库中，日期和时间的表示方式有很多种，`to_date`函数允许我们根据特定的格式将文本字符串转化为日期类型，以便进行进一步的日期运算和分析。 `to_date`函数的基本语法是： ```sql to_date(string, ...
Oracle的to-date函数.docx
12-12
Oracle的`to_date`函数是数据库系统中用于将字符串转换为日期类型的函数，它在处理日期和时间数据时非常有用。在Oracle SQL中，`to_date`函数接收两个参数：要转换的字符串和一个可选的日期格式模型，用于指示字符串...
oracle-DB2.rar_oracle
09-21
3. 日期时间类型：DATE、TIME和TIMESTAMP在DB2中的使用方式与Oracle类似，但DB2还提供INTERVAL类型，用于表示时间间隔。 4. BLOB和CLOB同样存在，但DB2还提供了LONG VARCHAR FOR BIT DATA和LONG VARGRAPHIC，用于...
Oracle Date常用方法封装
07-28
在Oracle数据库中，Date类型是用于存储日期和时间值的关键数据类型。Oracle Date常用方法封装通常涉及对日期的计算、格式化以及与时间戳的转换等操作。在Oracle环境中，我们可以通过PL/SQL来实现这些功能，将常用的...
oracle日期时间数据类型与时间间隔数据类型讲解
04-06
对oracle date，timestamp,interval数据类型的详细讲解。
oracle中时间转换的问题
weixin_34006965的博客
12-29 227
http://www.cnblogs.com/hanyun/archive/2009/05/14/1456772.html1）.在使用Oracle的to_date函数来做日期转换时，可能会直觉地采用“yyyy-MM-dd HH:mm:ss”的格式作为格式进行转换，但是在Oracle中会引起错误：“ORA 01810 格式代码出现两次”。如：select to_date('20...
mysql date interval_mysql 中 DATE_ADD(date,INTERVAL expr type)
weixin_32576389的博客
01-19 892
在Hiredmyway中:SELECT if(LENGTH(company_name) > 30,concat(SUBSTRING(company_name, 1, 27), '...'),company_name)AS org_name,count(id) AS num,org_id AS value,latest_modify_dateFROM v_job_searchWHERE...
oracle 中的INTERVAL 函数详解
热门推荐
wkc168的专栏
04-30 3万+
INTERVAL YEAR TO MONTH数据类型 Oracle语法: INTERVAL integer [- integer] {YEAR | MONTH} [(precision)][TO {YEAR | MONTH}] 该数据类型常用来表示一段时间差, 注意时间差只精确到年和月. precision为年或月的精确域, 有效范围是0到9, 默认值为2. eg: INTERVAL 123
date_interval_format() 函数
冷月醉雪的博客
02-26 683
查看更多       https://www.yuque.com/docs/share/adb33207-1e74-410d-9db9-d3a37151c542
oracle interval 每天,oracle interval type
weixin_33735281的博客
04-06 448
interval：时间间隔数据类型，有INTERVAL YEAR TO MONTH和INTERVAL DAY TO SECOND两种类型。1) INTERVAL YEAR TOMONTH使用该数据类型来保存以月为精度的时间间隔信息语法：INTERVAL YEAR(percision) TO MONTH;percision的取值范围为0-4.默认是2SQL> create table test...
PHP最佳实践之DateTime、DateInterval和DateTimeZone
萧潇的技术博客
07-14 6000
1).自己处理很容易出错，建议使用从PHP5.2之后新增的DateTime、DateInterval和DateTimeZone类。 2).使用date_default_timezone_set(‘Asia/Shanghai’);设置默认时区为中国时区，或者你也可以在php.ini文件中配置。 3).使用DateTime管理时间和日期：<?php //没有传入参数返回当前日期和时间的实例 $dat
Oracle中TO_DATE格式
weixin_34037977的博客
03-25 7029
TO_DATE格式(以时间:2007-11-02 13:45:25为例) Year: yy two digits 两位年 显示值:07 yyy three digits 三位年 显示值:007 yyyy four digits 四位年...
REDSHIFT TIMESTAMP 类型转oracle datetime类型
05-30
在 Oracle 中，日期时间类型的数据类型为 `DATE`。如果要将 Redshift 中的 `TIMESTAMP` 类型转换为 Oracle 中的 `DATE` 类型，可以使用以下 SQL 语句： ```sql SELECT TO_DATE('1970-01-01', 'YYYY-MM-DD') + TIMESTAMP 'epoch' + your_timestamp_column * INTERVAL '1 second' AS date_column FROM your_table; ``` 其中，`your_timestamp_column` 是 Redshift 表中的 `TIMESTAMP` 类型的列名，`your_table` 是 Redshift 表名。 这条 SQL 语句的作用是将 Redshift 中的 `TIMESTAMP` 类型转换为 Unix 时间戳（秒），然后加上 Unix 时间戳与 Oracle 中 `DATE` 类型时间的差值，即可得到 Oracle 中的 `DATE` 类型时间。 注意，上述 SQL 语句中的 `epoch` 是固定值，代表 Unix 时间戳起始时间，不需要修改。另外，如果你的 Redshift 表中的 `TIMESTAMP` 类型存储的是毫秒级别的时间戳，需要将 `1 second` 改为 `1 millisecond`。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值