Time Difference
-
Hi,
I have a TimeStamp field and from it I am creating Min(TimeStamp) and Max(Timestamp)
And now I want the difference in Time (hrs or minutes) between them.
I tried datediff(mi,Min(TimeStamp),Max(TimeStamp)) but to no avail.
Any ideas please?
-
You can't do that. You need a date column. Timestamp, contrary to his name, has no association with date nor time.
-
Ninja is correct. You can convert the value to an integer and attempt seeing the difference in that fashion.
Here is a query showing a 24 second difference and the different actual values you will return:
DECLARE @TimeStamp AS timestamp
DECLARE @Time TABLE( TimeStampValue timestamp,
Counter integer)
DECLARE @Counter integer
SET @Counter = 1
WHILE @Counter <= 25
BEGIN
INSERT INTO @Time( Counter) SELECT @Counter
WAITFOR DELAY '000:00:01'
SET @Counter = @Counter + 1
END
SELECT MIN( TimeStampValue), MAX( TimeStampValue) FROM @Time
SELECT MIN( CONVERT( datetime, TimeStampValue)), MAX( CONVERT( datetime, TimeStampValue)) FROM @Time
SELECT MAX( CONVERT( integer, TimeStampValue)) - MIN( CONVERT( integer, TimeStampValue)) FROM @Time
I wasn't born stupid - I had to study.
-
If you use a column with date values in it, you can do the query you are trying to do:
Declare
@test-2 Table (MyID int identity primary key not null, MyDate datetime)
Insert
Into @test-2 (MyDate) Select '1/1/2000 4:51:00 AM'
Insert
Into @test-2 (MyDate) Select '1/1/2000 4:52:00 AM'
Insert
Into @test-2 (MyDate) Select '1/1/2000 4:53:00 AM'
Select
Min(MyDate) as MyMinDate, Max(MyDate) As MyMaxDate, DateDiff(mi, Min(MyDate), Max(MyDate)) As MyDateDifference
From
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
John,
What is the data type of the column you are calling "TimeStamp"?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Have a look at "Timestamp data type" in books online.
In part, this says...
timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
-
A timestamp is not simply a randomly generated number. It has to be based upon some value or point in time - otherwise it would be like a GUID or a RAN() with a seed that is based upon some value or point in time...
The following query does show that the timestamp value, when converted to an integer, can give the user seconds, (which obviously can be converted to another time measurement).
I have not been able to find how SQL Server actually generates the timestamp value. Can someone answer this? TIA
DECLARE @Time TABLE( TimeStampValue timestamp,
Counter integer)
DECLARE @Counter integer
SET @Counter = 1
WHILE @Counter <= 5
BEGIN
INSERT INTO @Time( Counter) SELECT @Counter
WAITFOR DELAY '000:00:01'
SELECT TimeStampValue,
CONVERT( integer, TimeStampValue) AS 'TimeStamp',
GETDATE() AS 'GETDATE()'
FROM @Time WHERE Counter = @Counter
SET @Counter = @Counter + 1
END
I wasn't born stupid - I had to study.
-
That query only appears to represent secodns because you put a 1 second delay in there. Comment out the delay and you will see that the timestamp increments each time whereas the date value does not.
A timestamp is nothing more than a number that increments by 1 every time you use it. That's it. No date or time values can be derived directly from a timestamp value!!!
Unlike an identity column, there is only 1 timestamp entity per database and you can therefore be guaranteed that a timestamp value will always be unique within a database.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Thanks! I just could not get it through my thick head...
I wasn't born stupid - I had to study.
-
Nicely done, Robert.
I'm still waiting for the original poster to definitely describe the datatype of the column he named "TimeStamp" (instead of guessing
).--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi everybody,
blimey, thanks for your help. It's all getting a bit beyond me now. I'm gonna have to spend a bit of time digesting all this.
Jeff, the datatype is a set as Datetime. Thanks
Many thanks to you all
John
-
Just tried this and it seems to work fine. Here is my query:
select
datediff(mi,Min(TimeStamp),Max(TimeStamp)) from GrowthDetails
Timestamp column is in this case a datetime field.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply