Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convertsionsproblem between timestamp and bigint Expand / Collapse
Author
Message
Posted Thursday, August 6, 2009 7:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:18 AM
Points: 132, Visits: 530
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.
Post #766182
Posted Thursday, August 6, 2009 7:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,521, Visits: 3,039
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.

Post #766640
Posted Thursday, August 6, 2009 7:58 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:33 PM
Points: 2,844, Visits: 1,153
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).



Post #766652
Posted Friday, August 7, 2009 1:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:18 AM
Points: 132, Visits: 530
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.

Post #766728
Posted Tuesday, April 9, 2013 3:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 28, 2014 8:56 PM
Points: 3, Visits: 118
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.
Post #1440589
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse