Subtracting Time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Subtracting Time

Author

Topic

Andrewra
Starting Member

17 Posts

Posted - 2009-08-25 : 14:12:42

I am trying to subtract a date/time collumn to calculate a duration period. I have tried several ways to do this. By casting the log timestamp as time to seporate it from the date aspect (the duration should never go over a 24 hour period so I don't need to worry about multiple days) I also have tried using a datediff function.Any suggestions on the most effective way to do this would be great. It appears that when I use the datediff function it is still calculating the difference using the associated date so I am getting a difference of 60000 minutes, and when I try to cast the log Timestamp as time and subract I get a Operand type clash error.

Examples

Datediff(Minute , Case when ProcCategory = 'Load' and Notes = ' Load Complete' then LogTS else 0 end - LogTS

Cast(LogTS as Time) - Case when ProcCategory = 'Load' and Notes = ' Load Complete' then LogTS else 0 end

Thanks for any suggestions
AA

Andrew Alexander
LiveLogic

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-25 : 14:45:57

Sorry - not clear on the problem. Are you trying to determine the difference (duration) between 2 different datetime values?

>>I am trying to subtract a date/time collumn to calculate a duration period
"subtract a date/time column" from what?

Looks like the only two datetimes you are dealing with is [LogTS] and "0". If you want to get the duration (in minutes) between a datetime (LogTS) and 12:00 of the same day then you can use this:
select datediff(minute, dateadd(day, datediff(day, 0, LogTS), 0), LogTS)

Be One with the Optimizer
TG

Andrewra
Starting Member

17 Posts

Posted - 2009-08-25 : 14:56:25

TG, let me try and explain it a bit more. Sorry about the confusion. There is only one timestamp on the table. But one of the records indicates that the Load process is complete, Hence the case statement to select that particular timestamp. I want to subract the timestamp from the other records from that to find a duration time based on the difference difference from the "Load Complete" Record.

Here is the basic table structure to help you see what I mean.

ProcCategory | ProcName | Notes | LogTS
Staging | Table 1 | Stage Table 1 | 2009/08/25 1:30:01
Load | Master Load | Load Complete | 2009/08/25 3:00:00

I want to calculate the duration of time between the load complete record and the staging record. This is all in the context of SSIS Logging and capturing log times from package execution from a table.

Hope that helps
AA

Andrew Alexander
LiveLogic

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-25 : 15:06:17

Can you post the actual statement you used to get the (incorrect) 60000 minutes? Because I can't tell how you associate the "load complete" row with the "staging" row.

It may be easier for me (us) if you post the table create script (DDL), some inserts for sample data (DML), and the expected results based on your sample data.

Be One with the Optimizer
TG

Andrewra
Starting Member

17 Posts

Posted - 2009-08-25 : 15:18:57

Select Distinct
ProcCategory
,ProcName
,Notes
,LogTS
, Datediff(Minute , Case when ProcCategory = 'Load' and Notes = 'Load Complete' then LogTS else 0 end, LogTS) as Duration
From ETL_Log

That returns the given information but the duration value is 57668685. Did you need more info than the two example records I listed above? Based on what the example above I am expecting to get a duration of 90 because the difference between 3:00 and 1:30 is 90 minutes.

AA

Andrew Alexander
LiveLogic

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-25 : 15:47:41

Your statement is calculating [duration] for each row using only facts contained in that row.

Here is your sample data and your statement with a rowNumber added for clarity:


declare @ETL_Log table (ProcCategory varchar(10), ProcName varchar(20), Notes varchar(20), LogTS datetime)
insert @ETL_Log
select 'Staging ', 'Table 1', 'Stage Table 1', '2009/08/25 1:30:01' union all
select 'Load', 'Master Load', 'Load Complete', '2009/08/25 3:00:00'

Select 
       row_number() over (order by LogTS) as rowNumber
       ,ProcCategory
       ,ProcName
       ,Notes
       ,LogTS
       ,Datediff(Minute , Case when ProcCategory = 'Load' and Notes = 'Load Complete' then LogTS else 0 end, LogTS) as Duration
From @ETL_Log

OUTPUT:

rowNumber            ProcCategory ProcName             Notes                LogTS                   Duration
-------------------- ------------ -------------------- -------------------- ----------------------- -----------
1                    Staging      Table 1              Stage Table 1        2009-08-25 01:30:01.000 57669210
2                    Load         Master Load          Load Complete        2009-08-25 03:00:00.000 0

RowNumber 1 is calculated based only on facts in rowNumber 1. And rowNumber 2 is calculated based only on facts in rowNumber 2. If you want to calculate the difference in time between rowNumber 2 and rowNumber 1 then we need a way to "associate" these two rows out of all the other rows in the table. I see no common attributes in the sample data to make that correlation.

Be One with the Optimizer
TG

Andrewra
Starting Member

17 Posts

Posted - 2009-08-25 : 15:51:35

TG

Thanks for the explanation. What you said makes sense. I was thinking that it might be something along those lines but was trying to find a solution.

Thanks for the help

AA

Andrew Alexander
LiveLogic

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-25 : 16:10:23

Is it possible that for every row with [Notes]='Load Complete' that you want the duration between that row and the previous row (based on chronology of LogTS)?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources