Convertsionsproblem between timestamp and bigint

  • 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.

  • 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.

  • 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).

  • 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.

  • 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.

  • 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

  • I also tried this. It is working @dvo

    • This reply was modified 1 year, 11 months ago by  nurarun271.

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

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