June 19, 2015 at 8:11 am
I am trying to calculate the time difference between the value in the row and the min value in the table. So say the min value in the table is 2014-05-29 14:44:17.713. (This is the start time of the test.) Now say the test ends at 2014-05-29 17:10:17.010. There are many rows recorded during that start and end time, for each row created a time stamp is created. I am trying to calculate the elapsed time and have it as a row in the results.
min(timestamp) - timestamp(value in row) = elapsed time for that test
where Channel = '273'
Here is the table DDL
CREATE DATABASE SpecTest;
USE SpecTest
GO
CREATE TABLE [dbo].[Spec1](
[Spec1ID] [int] IDENTITY(1,1) NOT NULL,
[Channel] [int] NOT NULL,
[Timestamp] [datetime] NOT NULL,
[Lambda] [decimal](8, 2) NOT NULL,
[Power] [decimal](8, 2) NOT NULL,
CONSTRAINT [PK_Spec1] PRIMARY KEY CLUSTERED
(
[Spec1ID] ASC
));
Here is some dummy data to use
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 14:44:17.713', 800, '-64.91');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 15:05:09.507', 800, '-59.11');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 15:26:00.520', 800, '-54.36');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 16:28:34.213', 800, '-57.24');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 16:49:25.853', 800, '-59.79');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 17:10:17.010', 800, '-59.63');
Example desired results (I hope the formatting works)
Channel | Timestamp | Lambda | Power | Elapsed_Time
______________________________________________________________
273 | '2014-05-29 14:44:17.713', | 800, | '-64.91' | 0
273 | '2014-05-29 15:05:09.507', | 800, | '-64.91' | 00:20:51
273 | '2014-05-29 15:26:00.520', | 800, | '-64.91' | 00:41:42
273 | '2014-05-29 16:28:34.213', | 800, | '-64.91' | 01:44:16
273 | '2014-05-29 16:49:25.853', | 800, | '-64.91' | 02:05:08
273 | '2014-05-29 17:10:17.010', | 800, | '-64.91' | 02:25:59
Thanks!
June 19, 2015 at 8:25 am
Try:
with CTE_Min as
(
select MIN([Timestamp]) as [Timestamp] from Spec1
)
select
s.*,
DATEADD(MILLISECOND,
DATEDIFF(MILLISECOND, m.[Timestamp], s.[Timestamp]),
0) as Elapsed_Time
from Spec1 as s
cross join CTE_Min as m
Hope this helps.
June 19, 2015 at 8:49 am
Suggestion removed; I spotted a fault.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 19, 2015 at 9:02 am
With SQL 2012, the query can actually be simplified:
DECLARE @Spec1 AS TABLE (
Spec1ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
Channel int NOT NULL,
[Timestamp] datetime NOT NULL,
Lambda decimal(8, 2) NOT NULL,
[Power] decimal(8, 2) NOT NULL
);
INSERT INTO @Spec1 (Channel, [Timestamp], Lambda, [Power]) VALUES(273, '2014-05-29 14:44:17.713', 800, '-64.91');
INSERT INTO @Spec1 (Channel, [Timestamp], Lambda, [Power]) VALUES(273, '2014-05-29 15:05:09.507', 800, '-59.11');
INSERT INTO @Spec1 (Channel, [Timestamp], Lambda, [Power]) VALUES(273, '2014-05-29 15:26:00.520', 800, '-54.36');
INSERT INTO @Spec1 (Channel, [Timestamp], Lambda, [Power]) VALUES(273, '2014-05-29 16:28:34.213', 800, '-57.24');
INSERT INTO @Spec1 (Channel, [Timestamp], Lambda, [Power]) VALUES(273, '2014-05-29 16:49:25.853', 800, '-59.79');
INSERT INTO @Spec1 (Channel, [Timestamp], Lambda, [Power]) VALUES(273, '2014-05-29 17:10:17.010', 800, '-59.63');
SELECT *, DATEADD(ms, DATEDIFF(ms, MIN(S.[Timestamp]) OVER(), S.[Timestamp]), 0) as Elapsed_Time
FROM Spec1 as S;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 19, 2015 at 9:30 am
sgmunson (6/19/2015)
With SQL 2012, the query can actually be simplified:
DECLARE @Spec1 AS TABLE (
Spec1ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
Channel int NOT NULL,
[Timestamp] datetime NOT NULL,
Lambda decimal(8, 2) NOT NULL,
[Power] decimal(8, 2) NOT NULL
);
INSERT INTO @Spec1 (Channel, [Timestamp], Lambda, [Power]) VALUES(273, '2014-05-29 14:44:17.713', 800, '-64.91');
INSERT INTO @Spec1 (Channel, [Timestamp], Lambda, [Power]) VALUES(273, '2014-05-29 15:05:09.507', 800, '-59.11');
INSERT INTO @Spec1 (Channel, [Timestamp], Lambda, [Power]) VALUES(273, '2014-05-29 15:26:00.520', 800, '-54.36');
INSERT INTO @Spec1 (Channel, [Timestamp], Lambda, [Power]) VALUES(273, '2014-05-29 16:28:34.213', 800, '-57.24');
INSERT INTO @Spec1 (Channel, [Timestamp], Lambda, [Power]) VALUES(273, '2014-05-29 16:49:25.853', 800, '-59.79');
INSERT INTO @Spec1 (Channel, [Timestamp], Lambda, [Power]) VALUES(273, '2014-05-29 17:10:17.010', 800, '-59.63');
SELECT *, DATEADD(ms, DATEDIFF(ms, MIN(S.[Timestamp]) OVER(), S.[Timestamp]), 0) as Elapsed_Time
FROM Spec1 as S;
The same solution would work on 2008.
June 19, 2015 at 9:32 am
Imex, thank you for the quick response. I attempted to use your method in my query and it didn't seem to gel well. It works good on its own I was hoping to wrap it in another select statement.
sgmunson,
I know I am being a big pain, but is there a way to get the date to accurately display as well? I found that we have tests that run anywhere from a day to 2 months. So to be able to do this over a large span would be really helpful, unless there's a way to use a day counter.
ex: 01:14:22:34.207
(days, hours, minutes, seconds, milliseconds) elapsed.
Thanks!
June 19, 2015 at 9:54 am
cstg85 (6/19/2015)
Imex, thank you for the quick response. I attempted to use your method in my query and it didn't seem to gel well. It works good on its own I was hoping to wrap it in another select statement.sgmunson,
I know I am being a big pain, but is there a way to get the date to accurately display as well? I found that we have tests that run anywhere from a day to 2 months. So to be able to do this over a large span would be really helpful, unless there's a way to use a day counter.
ex: 01:14:22:34.207
(days, hours, minutes, seconds, milliseconds) elapsed.
Thanks!
Does this help?
SELECT *,
DATEADD(ms, DATEDIFF(ms, MIN(S.[Timestamp]) OVER(), S.[Timestamp]), 0),
CAST( DATEPART(DY, DATEADD(ms, DATEDIFF(ms, MIN(S.[Timestamp]) OVER(), S.[Timestamp]), 0)) - 1 AS varchar(3)) + ':'
+ CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, MIN(S.[Timestamp]) OVER(), S.[Timestamp]), 0), 14)
FROM @Spec1 as S;
June 19, 2015 at 2:01 pm
It does, thank you very much Luis. Can you explain how this works? If not that's fine!
June 21, 2015 at 4:18 pm
Luis' code will work mighty fine, in most cases. However, if it comes across a span of time that's just a little too big to be handled at the millisecond level, you'll get the following error.
[font="Courier New"]Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
[/font]
The code I'm getting ready to show is NOT portable to other database engines. Of course, I don't believe in the myth of portability, anyway, so that's not a problem for me. Just wanted to warn you. 😉 I'll also warn you that my code also has a limit of 3.3 milliseconds less than 1 million days. 😛 If we're both still around for such an elapsed time, I'll buy you a beer and apologize. :w00t:
First, let's build a few more rows of data so we can really test things out and your might be able to figure out an extra index or two if you need it. I generally like a million rows and the following will generate just such a table for you.
--===== Create the test table, as before
CREATE TABLE [dbo].[Spec1]
(
[Spec1ID] [int] IDENTITY(1,1),
[Channel] [int] NOT NULL,
[Timestamp] [datetime] NOT NULL,
[Lambda] [decimal](8,2) NOT NULL,
[Power] [decimal](8,2) NOT NULL,
CONSTRAINT [PK_Spec1] PRIMARY KEY CLUSTERED([Spec1ID])
)
;
--===== Simulate a million rows of test data across 16 years (2000 thru 2015)
WITH cteRandomData AS
( --=== Not to worry about the million rows. Only takes about 4 seconds or so.
SELECT TOP (1000000)
Channel = ABS(CHECKSUM(NEWID()))%100+200 -- 200 <= Channel <= 299
,[TimeStamp] = RAND(CHECKSUM(NEWID()))*(DATEDIFF(dd,'2000','2016'))+CONVERT(DATETIME,'2000') -- 2000-01-01 <= TimeStamp < 2016-01-01
,Lambda = ABS(CHECKSUM(NEWID()))%101+750 -- 750 <= Lamda <= 850
,[Power] = RAND(CHECKSUM(NEWID()))*201.0-100 -- -100 <= Power < 100
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
INSERT INTO Spec1 WITH (TABLOCK)
(Channel, Timestamp, Lambda, Power)
SELECT Channel, Timestamp, Lambda, Power
FROM cteRandomData
ORDER BY [TimeStamp]
;
Then, we can use a feature of the DATETIME datatype that MS didn't seem to understand the importance of when they made the newer datatypes and that's called "Direct Date Math". It's one of the things that makes temporal calculations so easy in Excel and I don't know what type of ANSI-ONLY crack the MS Dev Team was on when they created the newer date and time datatypes.
You'll be amazed at how simple the code can be because of the "Direct Date Math".
I also took it upon myself to make the "day" portion of the result contain leading zeros so that everything lines up real Purdy like. 😛 Here's the code.
WITH cteDur AS
(SELECT *,Dur = [TimeStamp]-MIN([TimeStamp]) OVER (PARTITION BY CHANNEL) FROM dbo.Spec1)
SELECT Spec1ID, Channel, [Timestamp], Lambda, [Power]
,Elapsed = RIGHT(DATEDIFF(dd,0,Dur)+1000000,6)+':'+CONVERT(CHAR(12),Dur,114)
FROM cteDur
--WHERE Channel = 273
ORDER BY Channel,[TimeStamp]
;
Of course, you can uncomment the WHERE clause if you only want 1 Channel to be returned. If you convert it to an iTVF (inline table valued function), the "273" in the code should be a variable. If you want it to be a view, remove both the WHERE and the ORDER BY.
For the original data that you were good enough to provide in such a nice readily consumable format, here's what the code returns.
Spec1ID Channel Timestamp Lambda Power Elapsed
------- ------- ----------------------- ------ ------ -------------------
1 273 2014-05-29 14:44:17.713 800.00 -64.91 000000:00:00:00:000
2 273 2014-05-29 15:05:09.507 800.00 -59.11 000000:00:20:51:793
3 273 2014-05-29 15:26:00.520 800.00 -54.36 000000:00:41:42:807
4 273 2014-05-29 16:28:34.213 800.00 -57.24 000000:01:44:16:500
5 273 2014-05-29 16:49:25.853 800.00 -59.79 000000:02:05:08:140
6 273 2014-05-29 17:10:17.010 800.00 -59.63 000000:02:25:59:297
(6 row(s) affected)
And it all works in all versions from 2005 and up.
{EDIT} Added milliseconds to the output.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2015 at 4:44 pm
For those that are interested and why I hate what they've done to the newer date and time datatypes (namely making "Direct Date Math" impossible), the maximum number of milliseconds that the DATE functions can generally handle is the same number as an INT and that only amounts to 25:20:31:23.647 (dd:hh:mi:ss.mil). Doing similar for seconds is only a little over 68 years and 20 days.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2015 at 8:25 am
Jeff,
Your post couldn't have come in a more timely manner, the time calculation is actually apart of a bigger query which in fact returned the error you mentioned. Is it possible to use this to calculate the elapsed while joining two other tables?
Thanks!
June 23, 2015 at 8:46 am
cstg85 (6/23/2015)
Jeff,Your post couldn't have come in a more timely manner, the time calculation is actually apart of a bigger query which in fact returned the error you mentioned. Is it possible to use this to calculate the elapsed while joining two other tables?
Thanks!
As with all else, "It Depends" but I don't see why not.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply