本文是小编为大家收集整理的关于计算两行之间的时间差的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
我有一个包含以下内容的表:
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 . . . . . .
我需要将值列乘以当前和上一行之间的时间差，并在整天进行总和.
我当前有每10秒钟的数据设置，这使查询中的简单转换很简单:
SELECT Sum((Value/6) FROM History WHERE DataDate BETWEEN @startDate and @endDate
@startdate和@enddate今天的日期是00:00:00和11:59:59.
在我设置每10秒收集的数据之前，每当值更改时收集它.没有任何重复的条目，最小的时间差为1秒.
当我不知道读数之间的时间间隔时，如何设置查询以在行之间获得经过的时间?
我正在使用SQL Server2005.
推荐答案
WITH rows AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY DataDate) AS rn
FROM mytable
)
SELECT DATEDIFF(second, mc.DataDate, mp.DataDate)
FROM rows mc
JOIN rows mp
ON mc.rn = mp.rn - 1
在SQL Server 2012+:
中SELECT DATEDIFF(second, pDataDate, dataDate)
FROM (
SELECT *,
LAG(dataDate) OVER (ORDER BY dataDate) pDataDate
FROM rows
) q
WHERE pDataDate IS NOT NULL
其他推荐答案
如果您不喜欢使用子选择，则在Quassnoi的查询上进行一些调整是:
SELECT
DATEDIFF(second, LAG(dataDate) OVER (ORDER BY dataDate), dataDate)
FROM rows
WHERE LAG(dataDate) OVER (ORDER BY dataDate) IS NOT NULL
问题描述
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
)
SELECT DATEDIFF(second, mc.DataDate, mp.DataDate)
FROM rows mc
JOIN rows mp
ON mc.rn = mp.rn - 1
In SQL Server 2012+:
SELECT DATEDIFF(second, pDataDate, dataDate)
FROM (
SELECT *,
LAG(dataDate) OVER (ORDER BY dataDate) pDataDate
FROM rows
) q
WHERE pDataDate IS NOT NULL
其他推荐答案
A little tweak on Quassnoi's query if you prefer not to use a Subselect would be:
SELECT
DATEDIFF(second, LAG(dataDate) OVER (ORDER BY dataDate), dataDate)
FROM rows
WHERE LAG(dataDate) OVER (ORDER BY dataDate) IS NOT NULL
更多相关问答
联系站长