On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Thursday, July 3, 2008

Elapsed Time 2


If you need to make a decision based on the number of minutes, remember that a day is '1'. Hence a minute is 1/24/60 or .000694.

In this example, I want records where the number of minutes is greater than 120.

To set up the tables and data, run this:

Drop Table Elapsed;

Create Table Elapsed
(Starttime Date,
Endtime Date);

Insert Into Elapsed
Values -- A Day
(To_Date('01-Jul-0801:01', 'dd-Mon-Yyhh:Mi'),
To_Date('02-Jul-0801:01', 'dd-Mon-Yyhh:Mi')
);
Insert Into Elapsed
Values -- An Hour
(To_Date('02-Jul-0801:00', 'dd-Mon-Yyhh:Mi'),
To_Date('02-Jul-0802:00', 'dd-Mon-Yyhh:Mi')
);
This is the query that evaluates which records to display:
Column StartTime      Heading Start|Time
Column EndTime Heading End|Time
Column ElapsedMinutes Heading Elapsed|Minutes

Select To_Char(Starttime, 'mm/Dd/Yy Hh:Mi')
As
StartTime,
To_Char(Endtime , 'mm/Dd/Yy Hh:Mi')
As
EndTime,
(Endtime-Starttime)/(1/24/60)
As
ElapsedMinutes
From Elapsed
Where (Endtime-Starttime)/(1/24/60)>120;
See Oracle documentation for further detail:
Dates

No comments:

Post a Comment