December 3, 2014 at 3:10 pm
Comments posted to this topic are about the item Convert datetime to bigint
December 22, 2014 at 4:37 am
I'm really missing the scope of this conversion :doze: ..
It sound likes this script is doing a large amount of CPU work for just change 8bytes into another 8byte representation.
If i would need a conversion from DateTime to BigInt why not just use something like:
select cast(cast(GETDATE() as timestamp) as bigint)
I'm pretty sure that sql will do this job in a better way than calling a lot of "datepart".
After all, i miss why the need of this conversion since a can actually put indexes on datetime columns too!
What am i wrong with? :doze:
December 22, 2014 at 6:44 am
zerbit (12/22/2014)
I'm really missing the scope of this conversion :doze: ..It sound likes this script is doing a large amount of CPU work for just change 8bytes into another 8byte representation.
If i would need a conversion from DateTime to BigInt why not just use something like:
select cast(cast(GETDATE() as timestamp) as bigint)I'm pretty sure that sql will do this job in a better way than calling a lot of "datepart".
After all, i miss why the need of this conversion since a can actually put indexes on datetime columns too!
What am i wrong with? :doze:
The original poster's code creates a human-readable date/time in a bigint format. For example, if you run the poster's code you'll get a value like 20141222133915, which you can easily look at and determine it is 22/Dec/2014 13:39:15.
The code you've provided indeed converts the datetime to a bigint, but I have no idea what date/time 180358576407463 represents 🙂
December 22, 2014 at 8:05 am
I'm not quite sure what the point of this process is. Is there a performance difference between indexing a bigint column and a datetime column? Why not use the datetime column to store dates and times?
December 22, 2014 at 8:24 am
hjelmesethe (12/22/2014)
The original poster's code creates a human-readable date/time in a bigint format.
Ok, but the point stated was to use a bigint to improve performance not to get a human readable number. I dont believe this conversion improve performance, so i don't think this function is of any use.
BTW: before someone states that timestamp and datetime are not comparable (because timestamp has nothing to do with dates), just consider that both are 8bytes of data and they can be converted into eachother.
declare @date as datetime
set @date=GETDATE()
select @date
declare @timestamp as timestamp
set @timestamp=cast(@date as timestamp)
select @timestamp
declare @bigint as bigint
select @bigint=CAST(@timestamp as bigint)
select @bigint
--DATE part
declare @dateback as datetime
select @dateback=cast((@bigint & 0xFFFFFFFF00000000) /65536/65536 as datetime)
select @dateback
--TIME part
select @bigint& 0x00000000FFFFFFFF
select dateadd(MILLISECOND, CAST((@bigint & 0x00000000FFFFFFFF) as int)/0.3,@dateback)
December 22, 2014 at 8:43 am
This:
20141222133915
is more readable than this
2014-12-22 10:42:21.693
or
2014-12-22
Really?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 22, 2014 at 8:52 am
If you're going to do this, do it much more efficiently without all the unnecessary variables:
alter function fn_generate_bigint(@datetime datetime)
returns bigint
as
begin
return (
select (
DATEPART(year, @datetime) * 10000000000 +
DATEPART(month, @datetime) * 100000000 +
DATEPART(day, @datetime) * 1000000 +
DATEPART(hour, @datetime) * 10000 +
DATEPART(minute, @datetime) * 100 +
DATEPART(second, @datetime)
) * 1000 +
DATEPART(millisecond, @datetime)
)
end
This code is shorter to write:
SELECT CONVERT(char(8), GETDATE(), 112) + REPLACE(CONVERT(char(12), GETDATE(), 114), ':', '')
but it is likely less efficient than the code above.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 22, 2014 at 8:53 am
zerbit (12/22/2014)
hjelmesethe (12/22/2014)
The original poster's code creates a human-readable date/time in a bigint format.Ok, but the point stated was to use a bigint to improve performance not to get a human readable number. I dont believe this conversion improve performance, so i don't think this function is of any use.
BTW: before someone states that timestamp and datetime are not comparable (because timestamp has nothing to do with dates), just consider that both are 8bytes of data and they can be converted into eachother.
declare @date as datetime
set @date=GETDATE()
select @date
declare @timestamp as timestamp
set @timestamp=cast(@date as timestamp)
select @timestamp
declare @bigint as bigint
select @bigint=CAST(@timestamp as bigint)
select @bigint
--DATE part
declare @dateback as datetime
select @dateback=cast((@bigint & 0xFFFFFFFF00000000) /65536/65536 as datetime)
select @dateback
--TIME part
select @bigint& 0x00000000FFFFFFFF
select dateadd(MILLISECOND, CAST((@bigint & 0x00000000FFFFFFFF) as int)/0.3,@dateback)
As stated above, timestamp and datetime are not compatible. Timestamp has nothing to do with date time, it's simply the equivalent of a binary counter.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 22, 2014 at 9:20 am
ScottPletcher (12/22/2014)
As stated above, timestamp and datetime are not compatible. Timestamp has nothing to do with date time, it's simply the equivalent of a binary counter.
Sure, i agree they are different but both are 8 bytes of data. If you cast a datetime into a timestamp you get EXACTLY those 8 bytes the datetime has. There is actually no conversion at all; that's why and only I use this cast to get a bigint out of a datetime, if you ever and really need to do such an odd thing.
In my opinion, there is no need at all to convert datetimes in any other format, ether for performance purpose nor (and especially) for readability. 😎
December 22, 2014 at 9:40 am
I work on a data warehouse that uses a technique like this for dates only (not the time portion). This was in the design from back when SQL Server didn't provide a native date-only type to use. I think that's 2005 and prior. Just as in this submission, we take an INT and put human-readable numbers in it to look like a date. It's like string concatenation. Example: DATETIME '2014-12-22 11:14:55.000' becomes INT '20141222'. I put quotes around it, but it's really a number and keeps all the beneficial properties of sorting, indexing, etc. The INT is only 4 bytes compared to using an 8 byte DATETIME type. There are drawbacks to doing this (see below), but on a big data warehouse the performance and storage benefits outweigh them. Back then, even Microsoft recommended doing this.
Now, however, this project is running on a version of SQL Server that supports the DATE type. This is only 3 bytes and requires no ugly conversion. I have tried to read up on it and never found a reason why a new project (without the legacy code) would still want to use the date-formatted INT versus the native DATE type.
So regarding this submission, I don't understand why it's beneficial to make a DATETIME into a BIGINT and keep the time portion. There's no upside and the following downsides:
• A conversion is required to store it
• The code will be less readable at any point that touches it
• It isn't compatible with the built-in date functions.
• Exporting this data requires a reverse-conversion because other applications won't understand it. For example, it makes it harder to do reports in SSRS, ETL with SSIS, or even to copy/paste results into Excel.
If there are any articles or studies that can show the performance benefits in doing this I would like to see them.
December 22, 2014 at 1:13 pm
I think we see a common misunderstanding illustrated here.
We think of and represent DateTime as a string and SSMS (and most other providers) obligingly display the value as a string. However, internally DateTime is Float or Real value consisting of an integer representing "Days since Epoch" and a factional part representing duration since midnight.
As several others have mentioned, I don't see that the change from Datetime to BigInt will make any difference in table access performance or table size. There maybe some utility in integers in the YYYYMMDDHHSS structure in some cases but if you have dealt with MSDB much you know that form can be very frustrating.
That said, the Cast to TimeStamp to BigInt is neat but you need to be careful. If you cast a Datetime to TimeStamp then BigInt and then reverse the process you will get the same Datetime value. However, a TimeStamp is not a datetime, it has no direct relationship to Datetime, and there is no guarantee about the underlying data other than the values are ever increasing up to the Max value of a BigInt. From BOL
The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type
Several other casts are also useful. I often cast to Float and then work on the integer and fractional parts. If you cast to Decimal(38,12), multiply by 1x10^12 and then cast to BigInt you get a value that retains all of the original precision.
Cast(cast(@now as Decimal(38, 12)) * 1000000000000 as BigInt)
There are lots of "tricks" one can perform with casting but be sure you have thoroughly studied BOL before you commit any of them to production. 🙂
December 22, 2014 at 2:55 pm
Ray Herring (12/22/2014)
I think we see a common misunderstanding illustrated here.We think of and represent DateTime as a string and SSMS (and most other providers) obligingly display the value as a string. However, internally DateTime is Float or Real value consisting of an integer representing "Days since Epoch" and a factional part representing duration since midnight.
As several others have mentioned, I don't see that the change from Datetime to BigInt will make any difference in table access performance or table size. There maybe some utility in integers in the YYYYMMDDHHSS structure in some cases but if you have dealt with MSDB much you know that form can be very frustrating.
That said, the Cast to TimeStamp to BigInt is neat but you need to be careful. If you cast a Datetime to TimeStamp then BigInt and then reverse the process you will get the same Datetime value. However, a TimeStamp is not a datetime, it has no direct relationship to Datetime, and there is no guarantee about the underlying data other than the values are ever increasing up to the Max value of a BigInt. From BOL
The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type
Several other casts are also useful. I often cast to Float and then work on the integer and fractional parts. If you cast to Decimal(38,12), multiply by 1x10^12 and then cast to BigInt you get a value that retains all of the original precision.
Cast(cast(@now as Decimal(38, 12)) * 1000000000000 as BigInt)There are lots of "tricks" one can perform with casting but be sure you have thoroughly studied BOL before you commit any of them to production. 🙂
😉
Actually, you're repeating a common misunderstanding.
Datetime is not stored as float or real [which, after all, are only approximate values]. Both date and time are stored as integer values. This is fully documented in BOL.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 22, 2014 at 3:33 pm
You are correct. I was, at best, imprecise in my language. I did say that the Date and Time are integers but I certainly confused things by mentioning Float/Real in the same thoughts.
December 22, 2014 at 4:22 pm
Ray Herring (12/22/2014)
I did say that the Date and Time are integers
I missed that I think, where was it?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 22, 2014 at 5:13 pm
I am such a big fan of ISO 8601 that I write all my dates as YYYYMMDD. The folks at my bank never blinked twice. I have also talked to an organization that hosts international conferences and demonstrated the wisdom of stating their dates as YYYY/MM/DD. This not only to promote understanding but to give all the attendees a level of "shared pain".
But so what? The original poster never made any claims for readability but as a performance booster. Since only the encoding function was presented I assumed that this was to be used in an index. I did not think that you could index on the result of a function. That might be cool if you could. I thought that SQL Server could only index on a column (or more than one).
Vignesh do you have any tests that show the performance increase? I would be interested in looking at that.
The only other reason that I can see for something like this is since the Datetime format uses a singed integer for the date portion that there might be some issues with dates before 1900 being viewed as higher than dates after that as the earlier dates would be negative. That might cause something if your dates are both sides of 1900. I guess that since that nobody relies on the sequence of data coming out of an index that all such issues are well hidden by ORDER BY.
As to DATEPART I can see where that might get slowed down by repeated calls. Might a better way be to convert the Datetime to an ISO 8601 string and return that as an integer? Two functions, one line, and no variables at all. 😉
ATBCharles Kincaid
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply