Archive

Posts Tagged ‘date’

Extraction of consecutive periods (without regard to the OverLap)

Thursday, 18 August, 2011 Leave a comment

kikanT
ID StaD EndD
-- ---------- ----------
10 2010-06-01 2010-06-12
10 2010-06-13 2010-06-14
10 2010-06-15 null
20 2010-06-01 2010-06-11
20 2010-06-13 2010-06-15
50 2010-11-11 2010-11-13
50 2010-11-14 2010-11-20
50 2010-12-22 2010-12-30

Summarized in consecutive periods every ID.

ID StaD EndD
-- ---------- ----------
10 2010-06-01 null
20 2010-06-01 2010-06-11
20 2010-06-13 2010-06-15
50 2010-11-11 2010-11-20
50 2010-12-22 2010-12-30

Data creation script

create table kikanT(ID,StaD,EndD) as
select 10,date '2010-06-01',date '2010-06-12' from dual union
select 10,date '2010-06-13',date '2010-06-14' from dual union
select 10,date '2010-06-15',null              from dual union
select 20,date '2010-06-01',date '2010-06-11' from dual union
select 20,date '2010-06-13',date '2010-06-15' from dual union
select 50,date '2010-11-11',date '2010-11-13' from dual union
select 50,date '2010-11-14',date '2010-11-20' from dual union
select 50,date '2010-12-22',date '2010-12-30' from dual;

Solution1
How to use hierarchical queries (> 10g)

select ID,min(connect_by_root StaD) as StaD,EndD
  from kikanT
 where connect_by_IsLeaf = 1
connect by prior ID=ID
       and prior EndD+1=StaD
group by ID,EndD
order by ID,EndD;

Solution2
Analysis of the relevant number

select ID,min(StaD) as StaD,
max(EndD) Keep(Dense_Rank Last order by EndD) as EndD
  from (select ID,StaD,EndD,
        sum(willSum) over(partition by ID order by StaD) as GID
        from (select ID,StaD,EndD,
              case when StaD = 1+Lag(EndD) over(partition by ID order by StaD)
                   then 0 else 1 end as willSum
                from kikanT))
group by ID,GID
order by ID,GID;
Advertisements
Categories: PL/SQL Tags: , , ,

Summarizes the start and end dates (or how to eleminate overlaps in dates)

Thursday, 18 August, 2011 1 comment

TimeSheet table
StartDate EndDate
---------- ----------
2005-01-01 2005-01-03
2005-01-02 2005-01-04
2005-01-04 2005-01-05
2005-01-06 2005-01-09
2005-01-09 2005-01-09
2005-01-12 2005-01-15
2005-01-13 2005-01-14
2005-01-14 2005-01-14
2005-01-17 2005-01-17

result
StartDate EndDate
---------- ----------
2005-01-01 2005-01-05
2005-01-06 2005-01-09
2005-01-12 2005-01-15
2005-01-17 2005-01-17

Data creation script

create table TimeSheets(
StartDate Date,
EndDate   Date,
primary key (StartDate));

insert into TimeSheets values(to_date('20050101','yyyymmdd'),to_date('20050103','yyyymmdd'));
insert into TimeSheets values(to_date('20050102','yyyymmdd'),to_date('20050104','yyyymmdd'));
insert into TimeSheets values(to_date('20050104','yyyymmdd'),to_date('20050105','yyyymmdd'));
insert into TimeSheets values(to_date('20050106','yyyymmdd'),to_date('20050109','yyyymmdd'));
insert into TimeSheets values(to_date('20050109','yyyymmdd'),to_date('20050109','yyyymmdd'));
insert into TimeSheets values(to_date('20050112','yyyymmdd'),to_date('20050115','yyyymmdd'));
insert into TimeSheets values(to_date('20050113','yyyymmdd'),to_date('20050114','yyyymmdd'));
insert into TimeSheets values(to_date('20050114','yyyymmdd'),to_date('20050114','yyyymmdd'));
insert into TimeSheets values(to_date('20050117','yyyymmdd'),to_date('20050117','yyyymmdd'));
commit;

SQL

Solution 1
How to use an inline view

select StartDate,min(EndDate) as EndDate
from
(select a.StartDate,b.EndDate
   from TimeSheets a,TimeSheets b
  where a.EndDate     and not exists(select 1 from TimeSheets c
                    where (c.StartDate <  a.StartDate and a.StartDate                        or (c.StartDate group by StartDate
order by StartDate;

Solution 2
How to use an inline view

select a.StartDate,min(b.EndDate) as EndDate
   from TimeSheets a,TimeSheets b
  where a.EndDate     and not exists(select 1 from TimeSheets c
                    where (c.StartDate <  a.StartDate and a.StartDate                        or (c.StartDate group by a.StartDate
order by a.StartDate;

Solution 3
How to use an inline view

SELECT startdate, MIN(enddate) AS enddate
  FROM (SELECT T1.startdate, T2.enddate
          FROM Timesheets T1, Timesheets T2
         WHERE T1.enddate  T3.startdate AND T1.startdate = T3.startdate AND T2.enddate < T3.enddate)))
 GROUP BY startdate;

Solution 4
How to use an inline view

SELECT startdate, MIN(enddate) AS enddate
  FROM (SELECT T1.startdate AS startdate, T2.enddate AS enddate
          FROM Timesheets T1, Timesheets T2, Timesheets T3
         WHERE T1.enddate  T3.startdate AND T1.startdate = T3.startdate AND T2.enddate < T3.enddate) THEN 1 END) = 0)
 GROUP BY startdate;

Solution 5
How to use a hierarchical queries (> 10g)

select min(connect_by_root StartDate) as StartDate,EndDate
  from TimeSheets
 where connect_by_IsLeaf = 1
connect by nocycle prior EndDate between StartDate and EndDate
group by EndDate
order by StartDate;

Solution 6
How to use hierarchical queries (> 10g)

select connect_by_root StartDate as StartDate,EndDate
  from TimeSheets a
 where connect_by_IsLeaf = 1
start with not exists(select 1 from TimeSheets b
                       where a.RowID != b.RowID
                         and a.StartDate between b.StartDate and b.EndDate)
connect by nocycle prior EndDate between StartDate and EndDate
order by StartDate;

Solution 7
How to use analysis of the relevant number range

select min(StartDate) as StartDate,max(EndDate) as EndDate
from (select StartDate,EndDate,
      sum(willSum) over(order by StartDate) as GID
      from (select StartDate,EndDate,
            case when StartDate
                                                 range between unbounded preceding
                                             and 1 preceding)
                 then 0 else 1 end as willSum
              from TimeSheets))
group by GID
order by StartDate;

from

Categories: PL/SQL Tags: , , ,

How does one get the time difference between two date columns?

Friday, 12 August, 2011 Leave a comment

Oracle allows two date values to be subtracted from each other returning a numeric value indicating the number of days between the two dates (may be a fraction). This example will show how to relate it back to a time value.

Let’s investigate some solutions. Test data:

CREATE TABLE dates (date1 DATE, date2 DATE);
INSERT INTO dates VALUES (SYSDATE, SYSDATE-1);
INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/24);
INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/60/24);
SELECT (date1 - date2) FROM dates;

DATE1-DATE2
-----------
1
.041666667
.000694444

Solution 1

SELECT floor(((date1-date2)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600 -
(floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference
FROM dates;

TIME_DIFFERENCE
--------------------------------------------------------------------------------
24 HOURS 0 MINUTES 0 SECS
1 HOURS 0 MINUTES 0 SECS
0 HOURS 1 MINUTES 0 SECS

An enhancement to solution 1

SELECT floor((date1-date2)*24)
|| ' HOURS ' ||
mod(floor((date1-date2)*24*60),60)
|| ' MINUTES ' ||
mod(floor((date1-date2)*24*60*60),60)
|| ' SECS ' time_difference
FROM dates;</code>

TIME_DIFFERENCE
--------------------------------------------------------------------------------
24 HOURS 0 MINUTES 0 SECS
1 HOURS 0 MINUTES 0 SECS
0 HOURS 1 MINUTES 0 SECS

Solution 2

If you don’t want to go through the floor and ceiling math, try this method (contributed by Erik Wile):

SELECT to_number( to_char(to_date('1','J') +
(date1 - date2), 'J') - 1) days,
to_char(to_date('00:00:00','HH24:MI:SS') +
(date1 - date2), 'HH24:MI:SS') time
FROM dates;

DAYS TIME
---------- --------
1 00:00:00
0 01:00:00
0 00:01:00

Solution 3: If u want an easier method, use numtodsinterval()

  • Submitted by Shilpa Petrim
  • NUMTODSINTERVAL: This function is new to Oracle 9i. It takes two arguments numtodsinterval(x,c) where x is a number and c is a character string denoting the units of x. Valid units are ‘DAY’, ‘HOUR’, ‘MINUTE’ and ‘SECOND’.

    This function converts the number x into an INTERVAL DAY TO SECOND datatype.

    select numtodsinterval(date1-date2,'day') time_difference from dates;
    TIME_DIFFERENCE
    ----------------------------------------------------------------
    +000000001 00:00:00.000000000
    +000000000 01:00:00.000000000
    +000000000 00:01:00.000000000
    

    from

    Categories: PL/SQL Tags: , , , ,

    Till New Year

    Thursday, 2 July, 2009 Leave a comment
    SELECT 'Till the New Year ' ||
    to_char(extract(YEAR FROM SYSDATE) + 1) || ' - ' ||
               trunc(months_between(to_date('01.01.' ||
    to_char(extract(YEAR FROM SYSDATE) + 1), 'dd.mm.yyyy'), SYSDATE)) 
               || ' months ' ||
    to_char(LAST_DAY(SYSDATE) - SYSDATE) || ' day ' ||
    to_char(23 - to_number(substr(to_char(SYSDATE, 'hh24:mi:ss'), 1, 2))) 
               || ' hh ' ||
    to_char(59 - to_number(substr(to_char(SYSDATE, 'hh24:mi:ss'), 4, 2))) 
               || ' mi ' ||
    to_char(59 - to_number(substr(to_char(SYSDATE, 'hh24:mi:ss'), 7, 2))) 
               || ' ss ' || '[today:' ||
    to_char(SYSDATE, 'mm:dd:yyyy hh24:mi:ss') || ']' AS "Happy New Year!!!"
    FROM dual;
    
    Categories: PL/SQL Tags: , ,

    [PLSQL] Convert seconds to Day hour : min : sec

    Thursday, 2 July, 2009 Leave a comment
    variable vSec number;
    exec :vSec := 68400;
    SELECT
    to_char(trunc(:vSec / 60 / 60 / 24), '09') || ' [day]' ||
    to_char(trunc(MOD(:vSec / 60 / 60, 24)), '09') ||' [hh24]' ||
    to_char(trunc(MOD(:vSec, 3600) / 60), '09') || ' [mi]' ||
    to_char(MOD(MOD(:vSec, 3600), 60), '09') || ' [ss]'
    FROM dual;
    
    Categories: PL/SQL Tags: , ,

    [PL\SQL] Convert seconds to Day hour : min : sec

    Thursday, 2 July, 2009 Leave a comment
    variable vSec number;
    exec :vSec := 68400;
    SELECT
    to_char(trunc(:vSec / 60 / 60 / 24), '09') || ' [day]' ||
    to_char(trunc(MOD(:vSec / 60 / 60, 24)), '09') ||' [hh24]' ||
    to_char(trunc(MOD(:vSec, 3600) / 60), '09') || ' [mi]' ||
    to_char(MOD(MOD(:vSec, 3600), 60), '09') || ' [ss]'
    FROM dual;
    
    Categories: PL/SQL Tags: , ,