I have a table that contains a column that collects discreet data (1 for on
and 0 for off, just those two values) and a date column which is a timestamp
containing a date and time. There are other columns in this table but they
are unimportant in this discussion.
A process inserts rows into this table about every second or so, depending
on the state of an object.
It has been requested of me that I prepare a report. Among other items,
this report must show the total time an object is turned on. In other
words, for every row that is set to 1, I need to add up the time and let the
users know how long that item was set to on for a given period of time, say
24 hours i.e. how long was that object in an on state during the last 24
hours.
I consider myself to be pretty handy at SQL but I must confess I'm at a loss
to come up with a query for this.
Any ideas and examples would be greatly appreciated?
Thanks in advance,
Chris SmithOh I neglected one important piece...
Select ObjectID, Sum(DateDiff(ms, On.LogDT, Off.LogDT)) OnTime
From LogTable On
Join LogTable Off
On Off.ObjectID = On.ObjectID
And Off.LogDT =
(Select Min(LofDT)
From LogTable
Where ObjectID = On.ObjectID
And LogDT > On.LogDT)
Where On.OnFlag = 1
And Off.OnFlag = 0
Group By ObjectID
"someone" wrote:
> I have a table that contains a column that collects discreet data (1 for o
n
> and 0 for off, just those two values) and a date column which is a timesta
mp
> containing a date and time. There are other columns in this table but the
y
> are unimportant in this discussion.
> A process inserts rows into this table about every second or so, depending
> on the state of an object.
> It has been requested of me that I prepare a report. Among other items,
> this report must show the total time an object is turned on. In other
> words, for every row that is set to 1, I need to add up the time and let t
he
> users know how long that item was set to on for a given period of time, sa
y
> 24 hours i.e. how long was that object in an on state during the last 24
> hours.
> I consider myself to be pretty handy at SQL but I must confess I'm at a lo
ss
> to come up with a query for this.
> Any ideas and examples would be greatly appreciated?
> Thanks in advance,
> Chris Smith
>
>|||jeez, typo... And this produces total Millliseconds, Change the ms inside
the datediff function to whatever you want it to produce if milliseconds is
not what you want:
sec, minute, hour, day, etc...
Select ObjectID, Sum(DateDiff(ms, On.LogDT, Off.LogDT)) OnTimeMs
From LogTable On
Join LogTable Off
On Off.ObjectID = On.ObjectID
And Off.LogDT =
(Select Min(LogDT)
From LogTable
Where ObjectID = On.ObjectID
And LogDT > On.LogDT)
Where On.OnFlag = 1
And Off.OnFlag = 0
Group By ObjectID
"someone" wrote:
> I have a table that contains a column that collects discreet data (1 for o
n
> and 0 for off, just those two values) and a date column which is a timesta
mp
> containing a date and time. There are other columns in this table but the
y
> are unimportant in this discussion.
> A process inserts rows into this table about every second or so, depending
> on the state of an object.
> It has been requested of me that I prepare a report. Among other items,
> this report must show the total time an object is turned on. In other
> words, for every row that is set to 1, I need to add up the time and let t
he
> users know how long that item was set to on for a given period of time, sa
y
> 24 hours i.e. how long was that object in an on state during the last 24
> hours.
> I consider myself to be pretty handy at SQL but I must confess I'm at a lo
ss
> to come up with a query for this.
> Any ideas and examples would be greatly appreciated?
> Thanks in advance,
> Chris Smith
>
>|||I assume that the table also contains an ObjecID - to identofy WHICH Object
is being Tuened On Or OFF...
Select ObjectID, Sum(DateDiff(ms, On.LogDT, Off.LogDT)) OnTime
From LogTable On
Join LogTable Off
On Off.ObjectID = On.ObjectID
And Off.LogDT =
(Select Min(LofDT)
From LogTable
Where ObjectID = On.ObjectID
And LogDT > On.LogDT)
Group By ObjectID
"someone" wrote:
> I have a table that contains a column that collects discreet data (1 for o
n
> and 0 for off, just those two values) and a date column which is a timesta
mp
> containing a date and time. There are other columns in this table but the
y
> are unimportant in this discussion.
> A process inserts rows into this table about every second or so, depending
> on the state of an object.
> It has been requested of me that I prepare a report. Among other items,
> this report must show the total time an object is turned on. In other
> words, for every row that is set to 1, I need to add up the time and let t
he
> users know how long that item was set to on for a given period of time, sa
y
> 24 hours i.e. how long was that object in an on state during the last 24
> hours.
> I consider myself to be pretty handy at SQL but I must confess I'm at a lo
ss
> to come up with a query for this.
> Any ideas and examples would be greatly appreciated?
> Thanks in advance,
> Chris Smith
>
>|||It might be helpful for you to provide sample data, especially about
this timestamp field. When a row is inserted with the on/off flag set
to ON, what value is in the timestamp column? The way I see it, there
are 3 options: -
1. The time the object entered the ON state only
2. The time the object left the ON state only
3. Both the time the objected entered the ON state + the time it left
or the duration it was in this state.
The 3rd option is easy because all you need to do is sum the duration
(or calculate it from the Left - Entered Time and sum) for each state
in a given time period.
The 1st and 2nd options are a bit tough since you have to form a sort
of JOIN to find the related state from which the object toggled from or
to. e.g. if I store the time the object entered the ON state, to find
out how long it stayed in this state, I need to find the immediate NEXT
time it entered the OFF state. Likewise, if it is the end time that is
stored, I need to find the immediate PREVIOUS time it left the OFF
state.
Another thing you will have to consider, though this has to do with
data integrity, how do u ensure no overlaps occur in the records (such
that your data says at a given point in time, the object was both ON
and OFF)? Do you allow time gaps in which you can't tell whether the
object was ON or OFF? if you don't how do u ensure there are no gaps?
If you do, how do you interpret the time where there are no records?
Please clarify...|||It might be helpful for you to provide sample data, especially about
this timestamp field. When a row is inserted with the on/off flag set
to ON, what value is in the timestamp column? The way I see it, there
are 3 options: -
1. The time the object entered the ON state only
2. The time the object left the ON state only
3. Both the time the objected entered the ON state + the time it left
or the duration it was in this state.
The 3rd option is easy because all you need to do is sum the duration
(or calculate it from the Left - Entered Time and sum) for each state
in a given time period.
The 1st and 2nd options are a bit tough since you have to form a sort
of join to find the related state from which the object toggled from or
to. e.g. if I store the time the object entered the ON state, to find
out how long it stayed in this state, I need to find the NEXT time it
entered the OFF state. Likewise, if it is the end time that is stored,
I need to find the PREVIOUS time it left the OFF state.
Another thing you will have to consider, though this has to do with
integrity, how do u ensure no overlaps occur in the records? Do you
allow time gaps in which you can't tell whether the object was ON or
OFF? if you don't how do u ensure there are no gaps? If you do, how do
you interpret the time where there are no records?
Please clarify...|||Hi Sienko,
I'll try to answer your questions as best as I can.
First of all, it should be noted this isn't a typical database. This is
actually a database accessed through MS SQL 2000 called InSQL. InSQL allows
you to store real time data more efficiently than SQL Server while allowing
for more transactions per second and storing the data in a manner that
allows for smaller files on the harddrive than what SQL Server itself would
allow. InSQL itself is actually made up of extension tables to SQL Server
2000. Along with this, InSQL provides other things such as providing a
discreete data type which allows you to store values that tells you if a
device is on or off where 1=on/open and 0=off/close. This probably isn't
important for you to know but it might be. For the most part, T-SQL is
still valid so any solution you can help me find should still work.
A typical row will include a column for a tagname, a discreete value column
(contains a value of 1 or 0, that is all as far as I know of), and a
timestamp which is just a datetime column (this column is equal to the time
the discreete value was retrieved from a device, not the time the row was
created). There are other columns but they aren't important to this
discussion as far as I know of. An example row might look like:
tagname_here -- discreete value -- datetime
There are rows inserted for each tag about every second or so. We'll say a
row is inserted each second to keep this simple. The rows will be the same,
just the fact the discreete value is different along with the time. For
example, a valve maybe open at the time a sensor takes a reading so this
data is retrieved (the state of the valve along with the datetime the
reading was taken) and inserted into a table (discreetehistory is the name
of the table I think, we'll use that for the sake of this conversation). As
long as the valve is open, a row will be inserted into this table where that
row will have a 1 in the discreete column and the datetime that value was
taken. When the valve is closed, rows will be inserted into the table where
the discreete value is now 0 and it has a datetime with it. Again, as long
as the valve is closed, rows will be inserted into the table with a
discreete value of 0 along with it's datetime. In other words, we have a
process that is looking at equipment 24/7/365. This process determines the
state of devices and inserts rows into a table every second or so.
Everytime a row is written, that row represents the state of the device and
a datetime is stored in that row to let us know when the sensor reading was
taken.
I don't think the table will allow null values nor do I believe a null value
will ever occur but am not certain. There is a transition period between
opening and closing valves. After all, a valve doesn't instantly open or
close. Depending on the size of size valve, it might take 1 to 5 seconds to
switch states. But, I don't believe this transition state is being
recorded. I wish I was at work answering this. I could tell you for sure
then.
Again, the problem is I need a query where I can report how long a valve was
left open in the last two hours. In other words, for a time from 12:00PM to
2:00 PM, the valve might be open at first but close at 12:30. Then, the
valve might open at 12:40. Then, the valve might be close at 1:00PM and
then open again at 1:10 PM. In other words, the valve was opened and
closeded multiple times during this two hour block. In total, the valve was
open 1hr and 40 minutes during this two hour block with the valve being
closed for 20 minutes. I need a query that will read this table and
everytime the valve is open which is represented with a discreete value of 1
(where 1 = open), then it will sum up all the times and report this to me.
I thought I was pretty good with T-SQL but I've never done anything like
this before when it comes to time. I must admit I don't have the slightest
idea how to proceed with this one.
Any thoughts would be greatly appreciated. If you need more information,
please let me know.
Thanks for trying to help me out. I apologize for taking a while to respond
to you but I was busy and simply forgot about this.
Thanks again!
Chris Smith
"sienko" <sienko@.gmail.com> wrote in message
news:1112174062.024672.131620@.g14g2000cwa.googlegroups.com...
> It might be helpful for you to provide sample data, especially about
> this timestamp field. When a row is inserted with the on/off flag set
> to ON, what value is in the timestamp column? The way I see it, there
> are 3 options: -
> 1. The time the object entered the ON state only
> 2. The time the object left the ON state only
> 3. Both the time the objected entered the ON state + the time it left
> or the duration it was in this state.
> The 3rd option is easy because all you need to do is sum the duration
> (or calculate it from the Left - Entered Time and sum) for each state
> in a given time period.
> The 1st and 2nd options are a bit tough since you have to form a sort
> of JOIN to find the related state from which the object toggled from or
> to. e.g. if I store the time the object entered the ON state, to find
> out how long it stayed in this state, I need to find the immediate NEXT
> time it entered the OFF state. Likewise, if it is the end time that is
> stored, I need to find the immediate PREVIOUS time it left the OFF
> state.
> Another thing you will have to consider, though this has to do with
> data integrity, how do u ensure no overlaps occur in the records (such
> that your data says at a given point in time, the object was both ON
> and OFF)? Do you allow time gaps in which you can't tell whether the
> object was ON or OFF? if you don't how do u ensure there are no gaps?
> If you do, how do you interpret the time where there are no records?
> Please clarify...
>|||You usually model time as durations, so you would have
CREATE TABLE Events
(event_id CHAR(10) NOT NULL,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_time DATETIME, -- null means current
. );|||On Sat, 2 Apr 2005 12:49:01 -0500, someone wrote:
-- Crosspost to non-existant group removed --
(snip description)
>I thought I was pretty good with T-SQL but I've never done anything like
>this before when it comes to time. I must admit I don't have the slightest
>idea how to proceed with this one.
>Any thoughts would be greatly appreciated. If you need more information,
>please let me know.
Hi Chris,
I think the best way to approach this problem is to start with a view to
reduce the load of imported data to just the relevant things: periods
without status change, with beginning and ending datetime.
CREATE VIEW Periods
AS
SELECT start.Status,
start.TheDatetime AS StartDT,
MAX(sameperiod.TheDatetime) AS EndDT
FROM RawData AS start
INNER JOIN RawData AS sameperiod
ON sameperiod.Status = start.Status
AND sameperiod.TheDatetime >= start.TheDatetime
AND NOT EXISTS
(SELECT *
FROM RawDate AS beetween -- deliberate misspelling: reserved word
WHERE beetween.TheDatetime > start.TheDatetime
AND beetween.TheDatetime < sameperiod.TheDatetime
AND beetween.Status <> start.Status)
WHERE NOT EXISTS
(SELECT *
FROM RawDate AS previous
WHERE previous.TheDatetime =
(SELECT MAX(TheDatetime)
FROM RawData
WHERE TheDatetime < start.TheDatetime)
AND previous.Status = start.Status)
GROUP BY start.Status, start.TheDatetime
With this query, your report becomes easy:
DECLARE @.StartReport smalldatetime
DECLARE @.EndReport smalldatetime
SET @.StartReport = '2005-04-02T12:00:00'
SET @.EndReport = '2005-04-02T14:00:00'
SELECT SUM(DATEDIFF(minute,
CASE WHEN StartDT < @.StartReport
THEN @.StartReport ELSE StartDT END,
CASE WHEN EndDT < @.EndReport
THEN @.EndReport ELSE EndDT END))
FROM Periods
WHERE StartDT < @.EndReport
END EndDT > @.BeginReport
I was not able to test the query and view above, since you have not
posted the CREATE TABLE and INSERT statements needed to create a test
database on my server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The thing is I'm not creating a table with multiple columns for dates. A
table is already present with one datecolumn only. I need to find all rows
within a timespan that have a value equal to 1 in the discreete value
column. Once I find all the rows, I need to add them all somehow such that
it will tell me the total time all the rows were set to a 1 in this column.
Any ideas on that?
Thanks!
Chris Smith
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112464694.412138.243320@.o13g2000cwo.googlegroups.com...
> You usually model time as durations, so you would have
> CREATE TABLE Events
> (event_id CHAR(10) NOT NULL,
> start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> end_time DATETIME, -- null means current
> .. );
>
