Convertsionsproblem between timestamp and bigint

Question

Convertsionsproblem between timestamp and bigint

w.lengenfelder

SSC Eights!

Points: 884
More actions
August 6, 2009 at 7:28 am

#137119

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

john.arnott SSChampion Points: 11882 More actions · Answer 1

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 One Orange Chip Points: 27452 More actions · Answer 2

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 SSC Eights! Points: 884 More actions · Answer 3

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.

Blade69 SSC Enthusiast Points: 140 More actions · Answer 4

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 Valued Member Points: 59 More actions · Answer 5

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

nurarun271 SSC Journeyman Points: 93 More actions · Answer 6

I also tried this. It is working @dvo

This reply was modified 2 years, 6 months ago by nurarun271.