zOs/SQL/EXTST

with g2 as
(
  select loadTs, max(updateStatsTime) stats
    , days(max(updateStatsTime)) - days(loadTs) d1
    from oa1p.tqz006gbgrTsStats
    where rz = 'RR2' -- and dbSys = 'DP2G'
      -- and loadTs > current timestamp - 1 month
    group by rz, dbSys, loadTs
)
, g3 as
(
  select g2.*
    , case when stats <= loadTs then 0
           when stats - (d1-1) days <= loadTs       then d1-1
           when stats - (d1  ) days <= loadTs       then d1
           else d1+1 end di
    from g2
)
, g as
(
  select g3.*
    , stats - di days stMod
    from g3
)
select *
    from g
    where stMod is null or loadTs is null or stats is null
        or stMod > loadTs or stMod <= loadTs - 1 day
        or d1 <> di
    order by 1 desc
;x;
with t (t) as
(
  select current timestamp from sysibm.sysDummy1
  union all select timestamp('1956-04-01-06.00.00')from sysibm.sysDummy1
  union all select timestamp('2015-01-01-01.00.00')from sysibm.sysDummy1
)
, j2 (f, t) as
(
  select f.t, t.t
    from t f, t t
    where f.t <= t.t
)
, j3 as
(
select j2.*
        , days(t) - days(f) di
        , days(t) - days(f)
           + (midnight_seconds(t) - midnight_seconds(f)
             + float(microsecond(t) - microsecond(f)) * 1e-6
             ) / 86400 dd
    from j2
)
, j as
(
  select j3.*
    , case when t - di days     between f - 1 day and f then di
           when t - (di-1) days between f - 1 day and f then di - 1
           when t - (di+1) days between f - 1 day and f then di + 1
      end dc
    from j3
)
select days(f), midnight_seconds(f), f
        , di, dc, t - dc days
        , t
        , decimal(dd, 20, 12)
        , decimal(mod(dd, 1), 20, 12)
        , dec(mod(dd, 1) * 86400, 12, 6)
    from j
;x;
(days(t)-days(b)) * 86400 "days*86400",
(days(t)-days(b)) * 86400
  + midnight_seconds(t) -  midnight_seconds(b) "days* midnight",
from sysibm.sysDummy1
select timestamp('2012-02-29-12:13:14   ')
    from sysibm.sysDummy1
;
declare global temporary table tst (tst timestamp);
insert into session.tst values (current timestamp);
insert into session.tst values ('2012-02-29 12:13:14   ');
insert into session.tst values ('2012-02-29-12:13:14   ');
select * from session.tst;
                 -- timestamp format: : statt . funktioniert nur <= v9|