# 计算两行之间的时间差[英] Calculate Time Difference Between Two Rows

### 问题描述

```DataDate                 Value
2010-03-01 08:31:32.000  100
2010-03-01 08:31:40.000  110
2010-03-01 08:31:42.000  95
2010-03-01 08:31:45.000  101
.                        .
.                        .
.                        .
```

```SELECT Sum((Value/6) FROM History WHERE DataDate BETWEEN @startDate and @endDate
```

@startdate和@enddate今天的日期是00:00:00和11:59:59.

## 推荐答案

```WITH    rows AS
(
SELECT  *, ROW_NUMBER() OVER (ORDER BY DataDate) AS rn
FROM    mytable
)
FROM    rows mc
JOIN    rows mp
ON      mc.rn = mp.rn - 1
```

```SELECT  DATEDIFF(second, pDataDate, dataDate)
FROM    (
SELECT  *,
FROM    rows
) q
```

## 其他推荐答案

```SELECT
FROM  rows
```

### 问题描述

I have a table that contains the following:

```DataDate                 Value
2010-03-01 08:31:32.000  100
2010-03-01 08:31:40.000  110
2010-03-01 08:31:42.000  95
2010-03-01 08:31:45.000  101
.                        .
.                        .
.                        .
```

I need to multiply the value column by the difference in time between the current and previous rows and sum that for the entire day.

I currently have the data set up to come in every 10 seconds which makes for a simple conversion in the query:

```SELECT Sum((Value/6) FROM History WHERE DataDate BETWEEN @startDate and @endDate
```

Where @startDate and @endDate are today's date at 00:00:00 and 11:59:59.

Before I set the data to be collected every 10 seconds it was collected whenever the Value changed. There aren't any duplicate entries in terms of time, the minimum time difference is 1 second.

How can I set up a query to get the elapsed time between rows for the case when I don't know the time interval between readings?

I am using SQL Server 2005.

## 推荐答案

```WITH    rows AS
(
SELECT  *, ROW_NUMBER() OVER (ORDER BY DataDate) AS rn
FROM    mytable
)
FROM    rows mc
JOIN    rows mp
ON      mc.rn = mp.rn - 1
```

In SQL Server 2012+:

```SELECT  DATEDIFF(second, pDataDate, dataDate)
FROM    (
SELECT  *,
FROM    rows
) q
```SELECT