(8.0.29)
drop table tab1;
create table tab1(col1 bigint auto_increment primary key,col2 datetime(6) );
insert into tab1(col2) values(current_timestamp(6) );
select * from tab1;
select col1, col2, lag(col2) over(order by col1) col2_lag, col2 - lag(col2) over(order by col1) delta
from tab1
order by col1
;
★引き算不可。エラーにならないが、値がおかしい
timediff関数を使用する
select col1, col2, lag(col2) over(order by col1) col2_lag, timediff(col2 , lag(col2) over(order by col1) ) delta
from tab1
order by col1
;
(19c)
drop table tab1 purge;
create table tab1(col1 int generated always as identity ,col2 timestamp(6) );
alter table tab1 add constraint tab1pk primary key(col1);
insert into tab1(col2) values(systimestamp );
commit;
select * from tab1;
col col2 for a30
col col2_lag for a30
col delta for a30
select col1, col2, lag(col2) over(order by col1) col2_lag, col2 - lag(col2) over(order by col1) delta
from tab1
order by col1
;
引き算可能
(14)
drop table tab1;
create table tab1(col1 bigint generated always as identity,col2 timestamp(6) );
alter table tab1 add constraint tab1pk primary key(col1);
insert into tab1(col2) values(clock_timestamp() );
select * from tab1;
select col1, col2, lag(col2) over(order by col1) col2_lag, col2 - lag(col2) over(order by col1) delta
from tab1
order by col1
;
引き算可能
(2019)
drop table tab1;
create table tab1(col1 bigint not null identity(1,1) ,col2 datetime2(6) );
alter table tab1 add constraint tab1pk primary key(col1);
insert into tab1(col2) values(getdate() );
select * from tab1;
select col1, col2, lag(col2) over(order by col1) col2_lag, col2 - lag(col2) over(order by col1) delta
from tab1
order by col1
;
★引き算不可。datediff関数を使用する
select col1, col2, lag(col2) over(order by col1) col2_lag , datediff(millisecond, lag(col2) over(order by col1),col2 ) delta
from tab1
order by col1
;
