根据SQL时间戳确定用户的访问时间 [英] Determining the length of a user's visit based on SQL timestamps

问题描述

我被分为使用C＃实现和纯SQL 2005/2008实现。我需要根据上次操作的时间戳确定使用时间。我的表包含 friendID（uniqueIdentifier）和 lastAction日期时间。下面是一个示例：

I am divided between using a C# implementation and a pure SQL 2005/2008 implementation. I need to determine the length of usage based on timestamps of their last actions. My table contains "friendID" (uniqueIdentifier) and "lastAction" datetime. Here is an example:

Bob, 1:00 PM 
Bob, 1:01 PM 
Bob, 1:20 PM 
Bob, 1:25 PM 
Jack, 5:00 PM
Bob, 11:20 PM

如果用户有60分钟的不活动状态，我想使他们超时。如果您的建议正确运作，则上面的数据应该有3个单独的会话。 Bob的下午1点开始的会议与11:20 PM开始的会议之间有一段很长的休息时间。

I want to "time out" a user if they had 60 minutes of inactivity. If your suggestion works correctly, there should be 3 separate sessions from the data above. There is a long break between Bob's session that started at 1 PM and the one that started at 11:20 PM.

正确的结果应该是：Bob（持续时间25分钟） ，杰克（持续时间0分钟），鲍勃（持续时间0分钟）

Correct results should be: Bob (duration 25 minutes), Jack (duration 0 minutes), Bob (duration 0 minutes)

如何使用纯SQL或C＃返回这3行？

非常感谢您的帮助，在这里确实很需要。

Thank you very much for your help, it is really needed here.

推荐答案

这听起来像是一个简单的问题，除非我误解了这个问题：

It sounds like a simple problem, unless I'm misunderstanding the question:

select friendId, max(lastAction) 'lastAction'
  from myTable
  where lastAction >= dateadd(day, -1, getdate())
      -- don't analyze data over 24 hours old
  group by friendId
  having max(lastAction) < dateadd(minute, -60, getdate())

这将返回在最近24小时，但不是最近60分钟。

This returns all friends who have had activity in the last 24 hours, but not in the last 60 minutes.

这篇关于根据SQL时间戳确定用户的访问时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！