Accurary in calculating time diff

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

General SQL Server Forums

New to SQL Server Programming

Accurary in calculating time diff

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-07 : 02:06:42

i need to calculate the pending time of a job which is starttime - runtime , both are datetime fields,

the syntax i have been trying was
pend_time_min = datediff(minute,runtime,starttime), which would give me the diff only in minutes , i need to classify the pending time in below categories , could any one please help me in calculating the pending time to fall in below categories?
how would i get pend time with more accuary in minutes ?

For eg if the pend_time is 3 secs the pend_time_min has to be displayed as 0.05 min...

PENDING_CODE PENDING_CATEGORY CATEGORY_RANK
1 0 - 5 sec 1
2 5 - 30 sec 2
3 30 sec - 1 min 3
4 1 - 2 min 4
5 2 - 5 min 5
6 5 - 10 min 6
7 10 - 15 min 7
8 15 - 30 min 8
9 30 - 45 min 9
10 45 min - 1 hr 10
11 1 - 2 hr 11
12 2 - 4 hr 12
13 4 - 12 hr 13
14 12 hr - 1 day 14
15 One day or more 15

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-07 : 04:05:14

quote:
how would i get pend time with more accuary in minutes ?

calculate the time different in seconds

datediff(second ,runtime, starttime)

you should have known this . . . (just in case)
and to convert time different from seconds to minute just divide by 60
and to convert time different from seconds to hour just divide by 3600

KH
[spoiler]Time is always against us[/spoiler]

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-07 : 04:24:50

could you please let me know how to write that in a select statement , for eg a jobs pend time is 3secs and i need to enter that in my table as 0.05min?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-07 : 10:23:32

something like

UPDATE yourtable
SET pend_time_min = datediff(minute,runtime,starttime)/60.0

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-07 : 15:46:50

That gives in hours right ? not sure if am getting this right ,

for eg i have :

start_time : 2009-11-06 07:31:43.000
runnable_time : 2009-11-06 05:11:01.000

if i do the diff between above values i get 02:20:42.000,

if i do datediff for minute , i get 140 minutes

if i do datediff(minute, runnable_time, start_time)/60 i get 2 ..

but i want the actual value in minutes like ...

140.42(140 min 42 secs)...

example 2 : If the pending time is 5 secs then display it like 0.083 min...

so i need more accuarate time in minutes basically

Please help

quote:
Originally posted by visakh16

something like
UPDATE yourtable
SET pend_time_min = datediff(minute,runtime,starttime)/60.0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 04:33:12

then make it

datediff(second,runtime,starttime)/60.0

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-02-08 : 09:04:44

rds, just so you know

140 Min 42 Sec =/= 140.42

You are not using a base 10 system when you are talking about time units.

Visakh's last response will give you want you want. Just realize 3.50 does NOT equal 3 Minutes 50 Seconds, its 3 minutes 30 seconds.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-08 : 09:12:04

quote:
but i want the actual value in minutes like ...

140.42(140 min 42 secs)...


declare	@start_time	datetime,
	@runnable_time	datetime

select	@start_time	= '2009-11-06 07:31:43.000',
	@runnable_time 	= '2009-11-06 05:11:01.000'

select	datediff(minute, @runnable_time, @start_time) +
	(datediff(second, @runnable_time, @start_time) % 60) / 100.0

KH
[spoiler]Time is always against us[/spoiler]

rds207
Posting Yak Master

198 Posts

Posted - 2010-02-08 : 18:44:47

I still dont get in decimals , what should be the datatype of the column pending_minutes ?

i guess i am messing with dataype of the column ..

currently i have
[PENDING_MINUTES] [int] NULL,

what should i have in order to get in decimals?

quote:
Originally posted by visakh16

then make it

datediff(second,runtime,starttime)/60.0

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-02-08 : 19:10:24

If the difference is time is less then 24 days, this will work OK:

declare	@start_time	datetime,
	@runnable_time	datetime

select	@start_time	= '2009-11-06 07:31:43.113',
	@runnable_time 	= '2009-11-06 05:11:01.447'

select 
	[Minutes] = 
	convert(numeric(12,2),round(datediff(ms,@runnable_time, @start_time)/(60000.00),2))

Results:

Minutes        
-------------- 
140.69

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources