日期时间的差异值 [英] Diff values in datetime

问题描述

我有id日期时间值





i在日期时间需要差值



表示当我查询被解雇时它将给出结果如



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需要像



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







请anybdy帮帮我

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

推荐答案

好的，首先是一些测试数据。

我们强调使用非重要且可能是非有序的关键字段，因为我们想要显示您不需要引入额外的排序/索引字段。

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.

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))

< br $> b $ b

-- Show the raw data.
select * from sheila

现在计算每个事件与之前事件之间的时间差（以秒为单位）。

Now work out the time difference in seconds between each event and the one that precedes it.

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...

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:

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

警告。假设您从未拥有带有_identical_时间戳的事件。



如果不明显它是如何工作的......

第一步是找出如何比当前活动更早地获得所有活动，

所以我们有自我加入。



所以每个事件我们现在可以显示所有先前事件的时间。但是我们只想要最新的。前一个的最大值（imum）所以我们要求使用max（）函数。然后我们可以使用datediff函数进行算术运算。



这里的名字是特定于T-SQL的，但我希望mysql提供类似的东西。



如果你想扔掉第一个事件（即diff为零），你会使用内部连接而不是左连接。



看看没有最大值时会发生什么（）和分组。

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.

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

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

合并功能用于为我们的第一个提供合理的输出t并且压制

我们原本会看到的在总体上抑制的空值警告。

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.

我认为你应该对Day有效，而不是毫秒


你可以试试。

I think you should valid to Day, not to milisecond

You may try.

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

现在，您可以将其与其他日期时间进行比较。

And now, you can compare it width other datetime.

这篇关于日期时间的差异值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！