MySQL Forums
Forum List  »  Newbie

Complex Query - subtract two event times
Posted by: Scott Critchley
Date: March 13, 2006 10:33AM

Hiya guys,

I am trying to formulate a complex query to get some good info straight from SQL, rather than post-processing in XL.

I have a table as follows:
EventID Integer (key)
ActionID Integer, 1 = event on, 2 = event off
dtStamp Date

I want to get the duration of each Event.

I.e. I need, for each Event:
dtStamp(actionID = 2) - dtStamp(actionID = 1)

I was going to try:

>create view EventOnTime as select EventID, dtStamp as EventOnDtStamp from EventLog where ActionID = 1;

>create view EventOffTime as select EventID, dtStamp as EventOffDtStamp from EventLog where ActionID = 2;

>select eventOnTime.EventID, eventOnTime.EventOnDtStamp - eventOffTime.EventOffDtStamp as EventDuration where eventOnTime.EventID = eventOffTime.EventID;

... but I can't create views with this version of SQL (server version: 4.0.20-standard-log)


Options: ReplyQuote

Written By
Complex Query - subtract two event times
March 13, 2006 10:33AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.