Click here to Skip to main content
15,847,077 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have id date time value


i need diff value in datetime

means when i query fired it will give result like

12345 2013-11-15 17:59:24
12345 2013-11-15 17:59:27
12345 2013-11-15 17:59:35
12345 2013-11-15 17:59:35
12345 2013-11-15 17:59:39

i need like

id datetime diff

12345 2013-11-15 17:59:24 00:00:00
12345 2013-11-15 17:59:27 00:00:03
12345 2013-11-15 17:59:35 00:00:08
12345 2013-11-15 17:59:35 00:00:00
12345 2013-11-15 17:59:39 00:00:04



Please anybdy help me
Posted

Ok, some test data first.
We make a point of using a non-significant and possibly non-ordered key field because we want to show that you don't need to introduce an additional ordering/indexing field.

SQL
create table sheila
(
  id int,
  eventTime datetime
)

set nocount on
declare @startPoint datetime 
set @startPoint = getdate()
insert into sheila(id, eventTime) values (120, @startPoint)
insert into sheila(id, eventTime) values (125, dateadd(mi, 45, @startPoint))
insert into sheila(id, eventTime) values (131, dateadd(hh, 6, @startPoint))
insert into sheila(id, eventTime) values (133, dateadd(dd, 1, @startPoint))
insert into sheila(id, eventTime) values (165, dateadd(dd, 2, @startPoint))
insert into sheila(id, eventTime) values (765, dateadd(dd, 6, @startPoint))



SQL
-- Show the raw data.
select * from sheila


Now work out the time difference in seconds between each event and the one that precedes it.
SQL
select 
  sheila.id, 
  sheila.eventTime as [curr],
  max(coalesce(prev.eventTime, sheila.eventTime)) as [prev], 
  datediff(ss, 
           max(coalesce(prev.eventTime ,sheila.eventTime)), 
           sheila.eventTime) as [diffSeconds]
from sheila
left join sheila as prev 
      on prev.eventTime < sheila.eventTime
group by sheila.id, sheila.eventTime


The raw data looks like this...
SQL
id          eventTime
----------- -----------------------
120         2013-11-16 10:55:51.423
125         2013-11-16 11:40:51.423
131         2013-11-16 16:55:51.423
133         2013-11-17 10:55:51.423
165         2013-11-18 10:55:51.423
765         2013-11-22 10:55:51.423


...and our result like this:
SQL
id          curr                    prev                    diffSeconds
----------- ----------------------- ----------------------- -----------
120         2013-11-16 10:55:51.423 2013-11-16 10:55:51.423 0
125         2013-11-16 11:40:51.423 2013-11-16 10:55:51.423 2700
131         2013-11-16 16:55:51.423 2013-11-16 11:40:51.423 18900
133         2013-11-17 10:55:51.423 2013-11-16 16:55:51.423 64800
165         2013-11-18 10:55:51.423 2013-11-17 10:55:51.423 86400
765         2013-11-22 10:55:51.423 2013-11-18 10:55:51.423 345600


A word of warning. It is assumed that you never have events with _identical_ timestamps.

If it's not obvious how it works...
The first step is to work out how to get all the events earlier than the current event,
so we have the self-join.

So for every event we can now display the times of all previous events. However we want only the latest. The max(imum) of the previous so we ask for exactly that using the max() function. We can then do the arithmetic using the datediff function.

The names here are T-SQL specific but I would expect mysql to offer something similar.

If you wanted to throw away the very first event (ie diff is zero) you would use an inner join rather than a left join.

See what happens without the max() and grouping.

SQL
select 
  sheila.id, 
  sheila.eventTime as [curr],
  prev.eventTime as [earlier]
from sheila
left join sheila as prev 
      on prev.eventTime < sheila.eventTime
order by sheila.eventTime


SQL
id          curr                    earlier
----------- ----------------------- -----------------------
120         2013-11-16 10:55:51.423 NULL
125         2013-11-16 11:40:51.423 2013-11-16 10:55:51.423
131         2013-11-16 16:55:51.423 2013-11-16 10:55:51.423
131         2013-11-16 16:55:51.423 2013-11-16 11:40:51.423
133         2013-11-17 10:55:51.423 2013-11-16 10:55:51.423
133         2013-11-17 10:55:51.423 2013-11-16 11:40:51.423
133         2013-11-17 10:55:51.423 2013-11-16 16:55:51.423
165         2013-11-18 10:55:51.423 2013-11-16 10:55:51.423
165         2013-11-18 10:55:51.423 2013-11-16 11:40:51.423
165         2013-11-18 10:55:51.423 2013-11-16 16:55:51.423
165         2013-11-18 10:55:51.423 2013-11-17 10:55:51.423
765         2013-11-22 10:55:51.423 2013-11-16 10:55:51.423
765         2013-11-22 10:55:51.423 2013-11-16 11:40:51.423
765         2013-11-22 10:55:51.423 2013-11-16 16:55:51.423
765         2013-11-22 10:55:51.423 2013-11-17 10:55:51.423
765         2013-11-22 10:55:51.423 2013-11-18 10:55:51.423


The coalesce function is used to give sensible output for our first event and to suppress
the "null suppressed in aggregate" warnings we would otherwise see.
 
Share this answer
 
v3
I think you should valid to Day, not to milisecond

You may try.

C#
DateTime now = DateTime.Now;
DateTime toValid = new DateTime(now.Year, now.Month, now.Day);


And now, you can compare it width other datetime.
 
Share this answer
 
Comments
AnnuBhai 16-Nov-13 3:06am    
i should compare that below datetime

means 1 row datetime should compare with 2 row datetime and result should be at front of 2nd row

thanx for replay
AnnuBhai 16-Nov-13 3:06am    
in mysql

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900