論壇 › MS SQL Server › 計算時間間隔

bestlong 發表於 2006-8-8 12:19

計算時間間隔

登入時間紀錄
[code]
ID         LoginTime
-----------------------------------
a1006      2004-07-29 12:24:15.000
a1006      2004-07-29 13:53:43.000
a1006      2004-07-29 22:42:15.000
a1006      2004-07-30 10:23:12.000
a1006      2004-07-30 19:03:23.000
a1006      2004-07-30 19:42:56.000
a1006      2004-07-30 20:22:51.000
a1006      2004-08-02 20:31:36.000
freedom545 2004-07-30 02:30:29.000
freedom545 2004-07-31 01:06:16.000
freedom545 2004-08-01 00:29:41.000
freedom545 2004-08-01 01:23:10.000
freedom545 2004-08-03 14:50:45.000
freedom545 2004-08-03 14:54:07.000
[/code]
理想中的萃取結果為：
[code]
ID           InterLoginTime
----------------------------
a1006        1 -------------2004-07-29 (13) - 2004-07-29 (12)
a1006        9 -------------2004-07-29 (22) - 2004-07-29 (13)
a1006       12 -------------2004-07-30 (10) - 2004-07-29 (22)
a1006        9 -------------2004-07-30 (19) - 2004-07-30 (10)
a1006        1 -------------2004-07-30 (20) - 2004-07-30 (19)
a1006       72 -------------2004-08-02 (20) - 2004-07-30 (20)
freedom545  23 -------------2004-07-31 (01) - 2004-07-30 (02)
freedom545  23 -------------2004-08-01 (00) - 2004-07-31 (01)
freedom545   1 -------------2004-08-01 (01) - 2004-08-01 (00)
freedom545  61 -------------2004-08-03 (14) - 2004-08-01 (01)
[/code]

準備測試環境
[code]
CREATE TABLE DemoA
(
         ID        VARCHAR(20)
        ,Login        DATETIME
)
GO

INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-07-29 12:24:15.000')
INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-07-29 13:53:43.000')
INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-07-29 22:42:15.000')
INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-07-30 10:23:12.000')
INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-07-30 19:03:23.000')
INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-07-30 19:42:56.000')
INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-07-30 20:22:51.000')
INSERT INTO DemoA(ID,Login) VALUES('a1006','2004-08-02 20:31:36.000')

INSERT INTO DemoA(ID,Login) VALUES('freedom545','2004-07-30 02:30:29.000')
INSERT INTO DemoA(ID,Login) VALUES('freedom545','2004-07-31 01:06:16.000')
INSERT INTO DemoA(ID,Login) VALUES('freedom545','2004-08-01 00:29:41.000')
INSERT INTO DemoA(ID,Login) VALUES('freedom545','2004-08-01 01:23:10.000')
INSERT INTO DemoA(ID,Login) VALUES('freedom545','2004-08-03 14:50:45.000')
INSERT INTO DemoA(ID,Login) VALUES('freedom545','2004-08-03 14:54:07.000')
GO
[/code]

萃取資料
[code]
select distinct ID
, LT=convert(datetime,convert(char(13),login,120) + ':00:00',110)
into #T1
from demoa
select * from
(select A.ID, A.LT
,Hr=(select top 1 datediff(hour,A.LT,B.LT) from #T1 B where A.ID=B.ID and B.LT > A.LT order by B.LT)
from #T1 A) as VT1
where Hr is not null
order by ID, LT

drop TABLE #T1
[/code]

得到結果
[code]
ID            LT                         Hr
------------- -------------------------- -----
a1006         2004-07-29 12:00:00.000    1
a1006         2004-07-29 13:00:00.000    9
a1006         2004-07-29 22:00:00.000    12
a1006         2004-07-30 10:00:00.000    9
a1006         2004-07-30 19:00:00.000    1
a1006         2004-07-30 20:00:00.000    72
freedom545    2004-07-30 02:00:00.000    23
freedom545    2004-07-31 01:00:00.000    23
freedom545    2004-08-01 00:00:00.000    1
freedom545    2004-08-01 01:00:00.000    61
[/code]

bestlong 發表於 2006-8-10 09:37

若是希望將 24 小時區分為四大時段，並將查詢結果的時間校正為區間的開始時間

凌晨：00:00:00 ~ 05:59:59
上午：06:00:00 ~ 11:59:59
下午：12:00:00 ~ 17:59:59
夜晚：18:00:00 ~ 23:59:59

[code]
SELECT ID,login
, CASE
    WHEN DATEPART(hour, LOGIN)>=18 THEN CONVERT(DATETIME, CONVERT(CHAR(10), LOGIN, 120) + ' 18:00:00.000')
    WHEN DATEPART(hour, LOGIN)>=12 THEN CONVERT(DATETIME, CONVERT(CHAR(10), LOGIN, 120) + ' 12:00:00.000')
    WHEN DATEPART(hour, LOGIN)>=6  THEN CONVERT(DATETIME, CONVERT(CHAR(10), LOGIN, 120) + ' 06:00:00.000')
    ELSE CONVERT(DATETIME, CONVERT(CHAR(10), LOGIN, 120) + ' 00:00:00.000')
  END as LT
from demoa
[/code]
得到結果
[code]
ID           login                     LT
------------ ------------------------- -----------------------
a1006        2004-07-29 12:24:15.000   2004-07-29 12:00:00.000
a1006        2004-07-29 13:53:43.000   2004-07-29 12:00:00.000
a1006        2004-07-29 22:42:15.000   2004-07-29 18:00:00.000
a1006        2004-07-30 10:23:12.000   2004-07-30 06:00:00.000
a1006        2004-07-30 19:03:23.000   2004-07-30 18:00:00.000
a1006        2004-07-30 19:42:56.000   2004-07-30 18:00:00.000
a1006        2004-07-30 20:22:51.000   2004-07-30 18:00:00.000
a1006        2004-08-02 20:31:36.000   2004-08-02 18:00:00.000
freedom545   2004-07-30 02:30:29.000   2004-07-30 00:00:00.000
freedom545   2004-07-31 01:06:16.000   2004-07-31 00:00:00.000
freedom545   2004-08-01 00:29:41.000   2004-08-01 00:00:00.000
freedom545   2004-08-01 01:23:10.000   2004-08-01 00:00:00.000
freedom545   2004-08-03 14:50:45.000   2004-08-03 12:00:00.000
freedom545   2004-08-03 14:54:07.000   2004-08-03 12:00:00.000
[/code]

查看完整版本: 計算時間間隔