Datediff function

select DATEDIFF(second, min (time_stamp), max(time_stamp)) AS responsetime
FROM table1.
Now, on this table1, time_stamp column is defined in datetime format.
Case1
When the min(time_stamp) is 2/17/2005 8:34:55AM, and the max(time_stamp) is
2/17/2005 8:35:12AM ... the responsetime is shown as 0 seconds.
Case2
When the min(time_stamp) is 2/17/2005 12:00:57 PM, and the max(time_stamp)
is 2/17/2005 12:01:01 PM ... the responsetime is shown as 1 seconds.
Iam only interested in finding the difference in seconds. Can sbdy explain
me, if there is a way out ?select DATEDIFF(second, '2/17/2005 8:34:55AM', '2/17/2005 8:35:12AM')
AS responsetime1
select DATEDIFF(second, '2/17/2005 12:00:57 PM', '2/17/2005 12:01:01
PM') AS responsetime2
gives results of 17 and 4. The results you say you get, 0 and 1
are certainly not the right ones, but I've never seen DATEDIFF
give wrong results. Can you post a reproducible script that
gives these wrong results?
It looks like the min and max time_stamp values are not what you think
they are.
Steve Kass
Drew University
SQLWiz wrote:

>select DATEDIFF(second, min (time_stamp), max(time_stamp)) AS responsetime
>FROM table1.
>Now, on this table1, time_stamp column is defined in datetime format.
>Case1
>When the min(time_stamp) is 2/17/2005 8:34:55AM, and the max(time_stamp) is
>2/17/2005 8:35:12AM ... the responsetime is shown as 0 seconds.
>Case2
>When the min(time_stamp) is 2/17/2005 12:00:57 PM, and the max(time_stamp)
>is 2/17/2005 12:01:01 PM ... the responsetime is shown as 1 seconds.
>Iam only interested in finding the difference in seconds. Can sbdy explain
>me, if there is a way out ?
>|||Hi
Iam using this query:
Select log_id, track_id, DATEDIFF(second, min (time_stamp),
max(time_stamp)) AS responsetime, convert(char, min(time_stamp), 110) as
Datevalue, convert(char, min(time_stamp), 108) as timevalue
FROM xmllog_cog where time_stamp > '2005-02-16 00:00:00.000' and
track_id like 'TN-%'and log_id in(
Select distinct log_id from xmllog_cog )group by log_id, track_id
For every log_id, there can be multiple sequence ids ..(like 1,2,3 until x..
where x can vary). Each sequence id has a timestamp attached to it. Iam
interested in finding .. for every log_id what is the time difference betwee
n
1) the timestamp @. max of sequence id (lets say 11)
2) the timestamp @. min of sequence id (will always be 1).
Can you pls suggest what could be done ?
"SQLWiz" wrote:

> select DATEDIFF(second, min (time_stamp), max(time_stamp)) AS responsetim
e
> FROM table1.
> Now, on this table1, time_stamp column is defined in datetime format.
> Case1
> When the min(time_stamp) is 2/17/2005 8:34:55AM, and the max(time_stamp) i
s
> 2/17/2005 8:35:12AM ... the responsetime is shown as 0 seconds.
> Case2
> When the min(time_stamp) is 2/17/2005 12:00:57 PM, and the max(time_stamp)
> is 2/17/2005 12:01:01 PM ... the responsetime is shown as 1 seconds.
> Iam only interested in finding the difference in seconds. Can sbdy explain
> me, if there is a way out ?|||SQLWiz wrote:
> Hi
> Iam using this query:
> Select log_id, track_id, DATEDIFF(second, min (time_stamp),
> max(time_stamp)) AS responsetime, convert(char, min(time_stamp),
> 110) as Datevalue, convert(char, min(time_stamp), 108) as timevalue
> FROM xmllog_cog where time_stamp > '2005-02-16 00:00:00.000' and
> track_id like 'TN-%'and log_id in(
> Select distinct log_id from xmllog_cog )group by log_id, track_id
> For every log_id, there can be multiple sequence ids ..(like 1,2,3
> until x.. where x can vary). Each sequence id has a timestamp
> attached to it. Iam interested in finding .. for every log_id what is
> the time difference between 1) the timestamp @. max of sequence id
> (lets say 11) 2) the timestamp @. min of sequence id (will always be
> 1).
> Can you pls suggest what could be done ?
>
>
> "SQLWiz" wrote:
>
What happens when you run that query without the datediff and use MIN
and MAX as separate columns on the datetime. What values are returned?
David Gugick
Imceda Software
www.imceda.com|||See if these work - they should be equivalent,
but I didn't check them for typos:
select
T1.log_id,
T1.track_id,
datediff(second, min(T1.time_stamp), max(T1.time_stamp)) as timeDiff
from table1 as T1
where T1.sequence_id = (
select min(sequence_id)
from table1 as T2
where T2.log_id = T1.log_id
and T2.track_id = T1.track_id
)
or
T1.sequence_id = (
select max(sequence_id)
from table1 as T2
where T2.log_id = T1.log_id
and T2.track_id = T1.track_id
)
group by T1.log_id, T1.track_id
-- another way to write this that might be faster:
select
T1.log_id,
T1.track_id,
datediff(second, min(T1.time_stamp), max(T1.time_stamp)) as timeDiff
from table1 as T1
where T1.sequence_id in (
select
case when i = 1 then min(sequence_id) else max(sequence_id) end
from table1 as T2
cross join (select 1 as i union all select 2) as OneTwo
where T2.log_id = T1.log_id
and T2.track_id = T1.track_id
)
group by T1.log_id, T1.track_id
-- or this
select
T1.log_id,
T1.track_id,
datediff(second, min(T1.time_stamp), max(T1.time_stamp)) as timeDiff
from table1 as T1
join (
-- a table containing only the min and max
-- sequence_id values along with every
-- (log_id, track_id) pair you need info for
select
T.log_id,
T.track_id,
min(T.sequence_id) as min_or_max_sequence_id
from table1 as T
join xmllog_cog as X
on X.log_id = T.log_id
where <condition>
group by
T.log_id,
T.track_id
union all
select
T.log_id,
T.track_id,
max(T.sequence_id) as min_or_max_sequence_id
from table1 as T
join xmllog_cog as X
on X.log_id = T.log_id
where <condition>
group by
T.log_id,
T.track_id
) as T2
on T2.log_id = T1.log_id
and T2.track_id = T1.track_id
and T2.min_or_max_sequence_id = T1.sequence_id
-- the last condition guarantees you only get rows with
-- smallest or largest sequence_id
group by T1.log_id, T1.track_id
-- or this
select
T1.log_id,
T1.track_id,
datediff(second, min(T1.time_stamp), max(T1.time_stamp)) as timeDiff
from table1 as T1
join (
select
T.log_id,
T.track_id,
case when i = 1 then min(T.sequence_id)
else max(T.sequence_id) end
as min_or_max_sequence_id
from table1 as T
join xmllog_cog as X
on X.log_id = T.log_id
cross join (select 1 as i union all select 2) as OneTwo
where <condition>
group by
T.log_id,
T.track_id
) as T2
on T2.log_id = T1.log_id
and T2.track_id = T1.track_id
and T2.min_or_max_sequence_id = T1.sequence_id
-- the last condition guarantees you only get rows with
-- smallest or largest sequence_id
group by T1.log_id, T1.track_id
-- SK
SQLWiz wrote:
>Hi
>Iam using this query:
>Select log_id, track_id, DATEDIFF(second, min (time_stamp),
>max(time_stamp)) AS responsetime, convert(char, min(time_stamp), 110) as
>Datevalue, convert(char, min(time_stamp), 108) as timevalue
>FROM xmllog_cog where time_stamp > '2005-02-16 00:00:00.000' and
>track_id like 'TN-%'and log_id in(
>Select distinct log_id from xmllog_cog )group by log_id, track_id
>For every log_id, there can be multiple sequence ids ..(like 1,2,3 until x.
.
>where x can vary). Each sequence id has a timestamp attached to it. Iam
>interested in finding .. for every log_id what is the time difference betwe
en
>1) the timestamp @. max of sequence id (lets say 11)
>2) the timestamp @. min of sequence id (will always be 1).
>Can you pls suggest what could be done ?
>
>
>"SQLWiz" wrote:
>
>