列中DATEDIFF中的时间差

Question

我有一个包含终端连接日志的表。该表中有“EventTime”、“TerminalID”和“Detail”列。

我当前的查询如下所示：

select 
   C_EventTime, L_TID,
   CASE L_Detail
      when '11' then 'Terminal Disconnected'
      when '12' then 'Terminal Connected'
   END as 'Detail'
from 
   dbo.tTerminalStateLog
where 
   L_Detail in (11,12) and C_EventTime >= '20140310000000'
order by 
   L_TID, C_EventTime 

输出如下所示：

C_EventTime     L_TID  Detail
----------------------------------------------
20140310110637  5000     Terminal Connected
20140312142909  5000     Terminal Disconnected 
20140313173850  5000     Terminal Connected
20140313233512  5000     Terminal Disconnected 
20140313233610  5000     Terminal Connected
20140310013506  5001     Terminal Disconnected
20140310013517  5001     Terminal Connected
20140310222519  5001     Terminal Disconnected 
20140310222532  5001     Terminal Connected
20140312165526  5001     Terminal Disconnected 
20140312165536  5001     Terminal Connected
20140310121415  12300    Terminal Connected
20140310122031  12300    Terminal Disconnected 
20140311083532  12300    Terminal Connected
20140311084218  12300    Terminal Disconnected 
20140311085141  12300    Terminal Connected
20140311085212  12300    Terminal Disconnected 
20140322115023  13100    Terminal Disconnected 
20140322115543  13100    Terminal Connected
20140322142655  13100    Terminal Disconnected 
20140322144834  13100    Terminal Connected
20140327192448  13100    Terminal Disconnected 

我想要计算每个断开连接和连接的终端状态之间的持续时间，计算每个L_TID上的每个离线连接持续时间，并给出Rows = TerminalID和Columns 24小时、168小时(周)和720小时(月)的输出。

我是否应该首先将varchar分解为更具可读性的格式：

    DECLARE @x VARCHAR(14)
    SET @x = '20040102102425'
    SELECT  @x,
    STUFF(STUFF(STUFF(@x, 9, 0, ' '), 12, 0, ':'), 15, 0, ':'),
    CAST(STUFF(STUFF(STUFF(@x, 9, 0, ' '), 12, 0, ':'), 15, 0, ':') AS DATETIME)

对于输出：

    (No column name)    (No column name)    (No column name)
    20040102102425      20040102 10:24:25   2004-01-02 10:24:25.000

然后在我运行以下命令后，计算datetime connection_states作为总时间值：

    select datediff(ss, '2010-01-22 15:29:55' , '2010-01-22 15:30:09')

如何运行此命令来查看每个终端ID的总断开状态？

下面是我想要的例子：

    L_TID       24 hrs        1 Week        30 days
    -----------------------------------------------------------------------
    5000       00:42:24     04:21:07       16:49:46      
    5001       00:09:38     01:38:01       05:32:19
    123000     01:05:59     09:29:15       11:58:19

希望有人能给点建议。

Answer 1

如果是Oracle，那么您可以尝试类似这样的方法

SELECT l_tid, 
       c_eventtime - Lag(c_eventtime, 2, Trunc(c_eventtime)) 
                       over ( 
                         ORDER BY c_eventtime) AS PriorRowEventTime, 
       details 
FROM   (SELECT l_tid, 
               To_date(c_eventtime, 'YYYYMMDDHH24MISS') c_eventtime, 
               details 
        FROM   table1);