欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

INTERVAL 用法 mysql

程序员文章站 2023-04-04 20:26:13
原文:https://blog.csdn.net/sqlquan/article/details/82699237 做个例子描述吧,也许更易于理解。 准备: 1.建表 create table INTERVAL_TEST (   t_id     I ......

原文:

做个例子描述吧,也许更易于理解。

准备:

1.建表

create table interval_test
(
  t_id     integer not null,  --纯id
  t_date   varchar2(40) not null,--时间,如果此处类型为date最好,不过现在很多应用都喜欢把时间建成varchar2
  t_flagchar(40)  --没什么业务含义,只是个标记
)

2.造数据

001        2011-01-01 12:00:00        1                                       
001        2011-01-02 12:00:00        1                                       
001        2011-01-03 12:00:00        1                                       
002        2011-01-01 12:00:00        1                                       
002        2011-01-03 12:00:00        1                                       
002        2011-01-05 12:00:00        1

3.写sql:同一id,如果时间连续两天flag为1,则把第二天...第n天的flag修改为0。

update interval_test
   set interval_test.flag= '0'
 where exists
 (select *
          from interval_test i
         where interval_test.t_id = i.t_id
           and to_date(interval_test.t_date, 'yyyy-mm-dd hh24:mi:ss') =
               to_date(i.t_date, 'yyyy-mm-dd hh24:mi:ss') + interval '1' day)

--因为建表时时间字段为varchar2,所以这里需要to_date一下。

 

commit后,查询结果:

1 2011-01-01 12:00:00 1                                       
1 2011-01-02 12:00:00 0                                       
1 2011-01-03 12:00:00 0                                       
2 2011-01-01 12:00:00 1                                       
2 2011-01-03 12:00:00 1                                       
2 2011-01-05 12:00:00 1                                       

结论,interval为取间隔的含义,在这个sql中,子查询的where条件中红色部分含义说白了就是:当a表时间 = b表时间 + 1天

也可以扩展使用为间隔多少小时:a.t_date = b.t_date+ interval '1' hour——即 a表时间 = b表时间 + 1小时,也可以为分钟、秒。

 

oracle里有interval day to second类型,在建表时可以定义,只提供下表作为释义。

具体类型的使用方式参考:

 

时间间隔字面量

说明

interval ‘3’ day

时间间隔为3天

interval ‘2’ hour

时间间隔为2小时

interval ‘25’ minute

时间间隔为25分钟

interval ‘45’ second

时间间隔为45秒

interval ‘3 2’ day to hour

时间间隔为3天零2小时

interval ‘3 2:25’ day to minute

时间间隔为3天零2小时25分

interval ‘3 2:25:45’ day to second

时间间隔为3天零2小时25分45秒

interval ‘123 2:25:45.12’ day(3)

to second(2)

时间间隔为123天零2小时25分45.12秒; 天的精度是3位数字,秒的小数部分的精度是2位数字

interval ‘3 2:00:45’ day to second

时间间隔为3天2小时0分45秒

interval ‘-3 2:25:45’ day to second

时间间隔为负数,值为3天零2小时25分45秒

interval ‘1234 2:25:45’ day(3)

to second

时间间隔无效,因为天的位数超过了指定的精度3

interval ‘123 2:25:45.123’ day

to second(2)

时间间隔无效,因为秒的小数部分的位数超过了指定的精度2

 

 

interval year to month数据类型 

oracle语法: 
interval 'integer [- integer]' {year | month} [(precision)][to {year | month}] 

该数据类型常用来表示一段时间差, 注意时间差只精确到年和月. precision为年或月的精确域, 有效范围是0到9, 默认值为2. 

eg: 
interval '123-2' year(3) to month     
表示: 123年2个月, "year(3)" 表示年的精度为3, 可见"123"刚好为3为有效数值, 如果该处year(n), n<3就会出错, 注意默认是2. 

interval '123' year(3) 
表示: 123年0个月 

interval '300' month(3) 
表示: 300个月, 注意该处month的精度是3啊. 

interval '4' year     
表示: 4年, 同 interval '4-0' year to month 是一样的 

interval '50' month     
表示: 50个月, 同 interval '4-2' year to month 是一样 

interval '123' year     
表示: 该处表示有错误, 123精度是3了, 但系统默认是2, 所以该处应该写成 interval '123' year(3) 或"3"改成大于3小于等于9的数值都可以的 

interval '5-3' year to month + interval '20' month = 
interval '6-11' year to month 
表示: 5年3个月 + 20个月 = 6年11个月 

与该类型相关的函数: 
numtodsinterval(n, 'interval_unit') 
将n转换成interval_unit所指定的值, interval_unit可以为: day, hour, minute, second 
注意该函数不可以转换成year和month的. 

numtoyminterval(n, 'interval_unit') 
interval_unit可以为: year, month 

eg: (oracle version 9204, redhat linux 9.0) 
sql> select numtodsinterval(100,'day') from dual; 

numtodsinterval(100,'day')                                                      
---------------------------------------------------------------------------     
+000000100 00:00:00.000000000                                                   

sql> c/day/second 
  1* select numtodsinterval(100,'second') from dual 
sql> / 

numtodsinterval(100,'second')                                                   
---------------------------------------------------------------------------     
+000000000 00:01:40.000000000                                                   

sql> c/second/minute 
  1* select numtodsinterval(100,'minute') from dual 
sql> / 

numtodsinterval(100,'minute')                                                   
---------------------------------------------------------------------------     
+000000000 01:40:00.000000000                                                   

sql> c/minute/hour 
  1* select numtodsinterval(100,'hour') from dual 
sql> / 

numtodsinterval(100,'hour')                                                     
---------------------------------------------------------------------------     
+000000004 04:00:00.000000000                                                   

sql> c/hour/year 
  1* select numtodsinterval(100,'year') from dual 
sql> / 
select numtodsinterval(100,'year') from dual 
                           * 
error at line 1: 
ora-01760: illegal argument for function 

sql> select numtoyminterval(100,'year') from dual; 

numtoyminterval(100,'year')                                                     
---------------------------------------------------------------------------     
+000000100-00                                                                   

sql> c/year/month 
  1* select numtoyminterval(100,'month') from dual 
sql> / 

numtoyminterval(100,'month')                                                    
---------------------------------------------------------------------------     
+000000008-04                                                                   


时间的计算: 
sql> select to_date('1999-12-12','yyyy-mm-dd') - to_date('1999-12-01','yyyy-mm-dd') from dual; 

to_date('1999-12-12','yyyy-mm-dd')-to_date('1999-12-01','yyyy-mm-dd')           
---------------------------------------------------------------------           
                                                                   11           
-- 可以相减的结果为天. 

sql> c/1999-12-12/1999-01-12 
  1* select to_date('1999-01-12','yyyy-mm-dd') - to_date('1999-12-01','yyyy-mm-dd') from dual 
sql> / 

to_date('1999-01-12','yyyy-mm-dd')-to_date('1999-12-01','yyyy-mm-dd')           
---------------------------------------------------------------------           
                                                                 -323           
-- 也可以为负数的 

sql> c/1999-01-12/2999-10-12 
  1* select to_date('2999-10-12','yyyy-mm-dd') - to_date('1999-12-01','yyyy-mm-dd') from dual 
sql> / 

to_date('2999-10-12','yyyy-mm-dd')-to_date('1999-12-01','yyyy-mm-dd')           
---------------------------------------------------------------------           
                                                               365193           

下面看看interval year to month怎么用. 
sql> create table bb(a date, b date, c interval year(9) to month); 

table created. 

sql> desc bb; 
name                                      null?    type 
----------------------------------------- -------- ---------------------------- 
a                                                  date 
b                                                  date 
c                                                  interval year(9) to month 

sql> insert into bb values(to_date('1985-12-12', 'yyyy-mm-dd'), to_date('1984-12-01','yyyy-mm-dd'), null) 

1 row created. 

sql> select * from bb; 

a         b                                                                     
--------- ---------                                                             
c                                                                               
---------------------------------------------------------------------------     
12-dec-85 01-dec-84                                                             
                                                                                
                                                                                
sql> update bb set c = numtoyminterval(a-b, 'year'); 

1 row updated. 

sql> select * from bb; 

a         b                                                                     
--------- ---------                                                             
c                                                                               
---------------------------------------------------------------------------     
12-dec-85 01-dec-84                                                             
+000000376-00                                                                   
                                                                                
-- 直接将相减的天变成年了, 因为我指定变成年的 
sql> select a-b, c from bb; 

       a-b                                                                      
----------                                                                      
c                                                                               
---------------------------------------------------------------------------     
       376                                                                      
+000000376-00                                                                   
                                                                                

sql> insert into bb values(null,null,numtoyminterval(376,'month')); 

1 row created. 

sql> select * from bb; 

a         b             c                                                        
--------- ---------    --------------------------------------------     
12-dec-85 01-dec-84    +000000376-00                                                                   
                         +000000031-04                                          

sql> insert into bb values ( null,null, numtoyminterval(999999999,'year')); 

1 row created. 

sql> select * from bb; 

a           b            c                                 
---------   ---------     ---------------------------------------------------------------------     
12-dec-85   01-dec-84   +000000376-00                                                                   
                          +000000031-04 
                          +999999999-00                                                                 

======================== 
今天来添加点新的东西![2008-07-26] 这部分东东来源:http://www.oraclefans.cn/forum/showblog.jsp?rootid=139 

interval year to month类型2个timestamp类型的时间差别。内部类型是182,长度是5。其中4个字节存储年份差异,存储的时候在差异上加了一个0x80000000的偏移量。一个字节存储月份的差异,这个差异加了60的偏移量。 

sql> alter table testtimestamp add e interval year to month; 
sql> update testtimestamp set e=(select interval '5' year + interval '10' month year  from dual); 

已更新3行。 

sql> commit; 
提交完成。 

sql> select dump(e,16) from testtimestamp; 

dump(e,16) 
--------------------------------------------- 
typ=182 len=5: 80,0,0,5,46 
typ=182 len=5: 80,0,0,5,46 
typ=182 len=5: 80,0,0,5,46 

年:0x80000005-0x80000000=5 
月:0x46-60=10 

interval day to second数据类型 

oracle语法: 
interval '{ integer | integer time_expr | time_expr }' 
{ { day | hour | minute } [ ( leading_precision ) ] 
| second [ ( leading_precision [, fractional_seconds_precision ] ) ] } 
[ to { day | hour | minute | second [ (fractional_seconds_precision) ] } ] 

leading_precision值的范围是0到9, 默认是2. time_expr的格式为:hh[:mi[:ss[.n]]] or mi[:ss[.n]] or ss[.n], n表示微秒. 
该类型与interval year to month有很多相似的地方,建议先看interval year to month再看该文. 

范围值: 
hour:    0 to 23 
minute: 0 to 59 
second: 0 to 59.999999999 

eg: 
interval '4 5:12:10.222' day to second(3) 
表示: 4天5小时12分10.222秒 

interval '4 5:12' day to minute 
表示: 4天5小时12分 

interval '400 5' day(3) to hour 
表示: 400天5小时, 400为3为精度,所以"day(3)", 注意默认值为2. 

interval '400' day(3) 
表示: 400天 

interval '11:12:10.2222222' hour to second(7) 
表示: 11小时12分10.2222222秒 

interval '11:20' hour to minute 
表示: 11小时20分 

interval '10' hour 
表示: 10小时 

interval '10:22' minute to second 
表示: 10分22秒 

interval '10' minute 
表示: 10分 

interval '4' day 
表示: 4天 

interval '25' hour 
表示: 25小时 

interval '40' minute 
表示: 40分 

interval '120' hour(3) 
表示: 120小时 

interval '30.12345' second(2,4)     
表示: 30.1235秒, 因为该地方秒的后面精度设置为4, 要进行四舍五入. 

interval '20' day - interval '240' hour = interval '10-0' day to second 
表示: 20天 - 240小时 = 10天0秒 

================== 
该部分来源:http://www.oraclefans.cn/forum/showblog.jsp?rootid=140 
interval day to second类型存储两个timestamp之间的时间差异,用日期、小时、分钟、秒钟形式表示。该数据类型的内部代码是183,长度位11字节: 

l         4个字节表示天数(增加0x80000000偏移量) 
l         小时、分钟、秒钟各用一个字节表示(增加60偏移量) 
l         4个字节表示秒钟的小时差异(增加0x80000000偏移量) 

以下是一个例子: 

sql> alter table testtimestamp add f interval day to second ; 

表已更改。 

sql> update testtimestamp set f=(select interval '5' day + interval '10' second from dual); 

已更新3行。 

sql> commit; 

提交完成。 

sql> select dump(f,16) from testtimestamp; 

dump(f,16) 

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

typ=183 len=11: 80,0,0,5,3c,3c,46,80,0,0,0 
typ=183 len=11: 80,0,0,5,3c,3c,46,80,0,0,0 
typ=183 len=11: 80,0,0,5,3c,3c,46,80,0,0,0 

日期:0x80000005-0x80000000=5 

小时:60-60=0 
分钟:60-60=0 
秒钟:70-60=10 
秒钟小数部分:0x80000000-0x80000000=0