Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convertsionsproblem between timestamp and bigint


Convertsionsproblem between timestamp and bigint

Author
Message
w.lengenfelder
w.lengenfelder
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 537
Hello,

I need to store the value of a timestampvalue into a different Database to determine changes at the source. To handle the datatype more easily i convert timestamp to bigint and store the value as bigint.
To look for changes i Select in the source DB with where clause
timestamp "greater than" convert(timestamp,"bigint-value")


But here is a problem. With small timestampvalues it works fine. if these Values are quite big,
the conversion of big timespamp values wont work as i need.

Example:
SELECT 'THIS WORKS'
   , CONVERT(timestamp,1639539151) myTimeStamp
   , CONVERT(Bigint,CONVERT(timestamp,1639539151)) myBigint
   , 1639539151 OrigValue
UNION
SELECT 'DON''T WORK'
   , CONVERT(timestamp,3639539151)
   , CONVERT(Bigint,CONVERT(timestamp,3639539151))
   , 3639539151


Please help, how to handle the timestamp.
john.arnott
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1464 Visits: 3059
This was an interesting question that got me experimenting and digging a bit into BOL. First, I found that TIMESTAMP values are stored as either BINARY(8) or VarBINARY(8) depending on whether they've been defined as NOT NULLable or NULLable. From there, I found that the breaking point for your sample code of "works" or "not works" is at the 2^31 mark. That value breaks, but (2^31) - 1 works.

From there, the trail led me to this nugget in the BOL documentation of the CONVERT() function:
Do not try to construct binary values and then convert them to a data type of the numeric data type category. SQL Server does not guarantee that the result of a decimal or numeric data type conversion to binary will be the same between versions of SQL Server.


This leads me to think that you'd be better off storing the values as binary(8) columns rather than the bigint datatype in your other database table.

By the way, SQL 2005 BOL recommends using the synonym "rowversion" rather than "timestamp", and SQL 2008 BOL calls "timestamp" a deprecated synonym for rowversion.
Scott Coleman
Scott Coleman
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2942 Visits: 1415
Your problem isn't with converting timestamp to bigint, it's with using bigint literals greater than 2^31. If you used the constant 0xD8EEF5CF instead of 3639539151 it might work better (I can't test it at the moment, maybe it should be 0x00000000D8EEF5CF).

I wouldn't worry about using bigint for timestamp/rowversion, it's still going to be an incrementing numeric sequence and the internal structure is unlikely to change. They would have used bigint in the first place if it was available. The warning about casting numeric to binary didn't mean all numeric types including ints, it referred specifically to NUMERIC(p,s)/DECIMAL(p,s) types. Their internal structure is not defined by any outside standard (to my knowledge) and is subject to change at any time.

The only problem I've had with bigint is using them in SSIS. Silly me for assuming they'd show up as Int64, the correct datatype is Binary(8). Their excuse is that they couldn't guarantee the format of bigint across all data sources (which could include big-endian hardware).



w.lengenfelder
w.lengenfelder
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 537
Thank you for your reply's.

I tested some more with your informations. I think

SELECT 'THIS WORK'
, CONVERT(timestamp,2147483647)
, CONVERT(Bigint,CONVERT(timestamp,2147483647))
, 2147483647
UNION
SELECT 'DON''T WORK'
, CONVERT(timestamp,2147483648)
, CONVERT(Bigint,CONVERT(timestamp,2147483648))
, 2147483648
UNION
SELECT 'Work also with gt 2^21-1 ; -)'
, CONVERT(timestamp,CONVERT(bigint,2147483648))
, CONVERT(Bigint,CONVERT(timestamp,CONVERT(bigint,2147483648)))
, 2147483648
order by 4



should work for me.

I kow this will either no work at the 2^63-1 Mark.
ron.gordon
ron.gordon
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 133
I'm also trying to figure out how to convert from a bigint value back to a timestamp, a colleague of my did it with SAS, this is the code ...

proc sql noprint;
select PUT(put(max_row_version, IB8.),$HEX16.)
into : maxRowVersion
from (
select max(Row_version) as max_row_version
from stbetlsg.tablename(firstobs=&num_m_50M) );
%let maxRowVersion=0X&maxRowVersion;
%put maxRowVersion=&maxRowVersion;
quit;

I'm lost with this code, and he found it in the SAS.
Thought somebody could figure it out.
dvosahlik
dvosahlik
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
I also spent hours solving this. Fortunatelly solved. It is sufficient to use this:
convert(timestamp, convert(bigint, "big number"))
It seems like if the SQL Server handles all the numbers as Integers
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search