Convert two 32 bit integers to one signed 64 bit integer string

Question

I have a 64 bit unsigned integer I need to represent in PostgreSQL. I've broken it down into two 32 bit unsigned integers, high and low. To allow Postgres to accept it, I need to convert high and low to a string representing a signed 64 bit integer.

How can I go about converting two 32 bit unsigned integers to a string representing in decimal a signed 64 bit integer?

@MattBurland No. Because it's not a `1→1` mapping, and because it could exceed the 64 bit signed maximum. — Max, Dec 05 '13 at 15:15
What do the low and high parts represent? I'm not seeing why you couldn't concat the two like @MattBurland suggested — Andrew Walters, Dec 05 '13 at 15:35
@AndrewWalters The high part is the high 32 bits, the low part is the low 32 bits. Just think. `''+(Math.pow(2, 32)-1)+(Math.pow(2, 32)-1)` = "42949672954294967295". 42949672954294967295 is > than 2^63-1, the max for signed integers. — Max, Dec 05 '13 at 15:42
Something like: https://github.com/mongodb/js-bson/blob/master/lib/bson/long.js — WiredPrairie, Dec 05 '13 at 15:53
Say you have the max 64bit value: 2^63 -1, 9,223,372,036,854,775,807 I think. What would the high and low pieces and up being? — Andrew Walters, Dec 05 '13 at 16:05
@AndrewWalters high would be 2147483647, and low would be 4294967295. See http://ideone.com/G0Nd1c — Max, Dec 05 '13 at 16:15
Just brainstorming here. Have you considered using a fixed point decimal in the database (e.g. "decimal(16, 0)")? You have issues with storage and performance if you've got a lot of data points, so keep that in consideration. Or, can you make a composite type in postgres (e.g. "create type ul64 (high int, low int)") and let the application layer convert that down to the proper type? That'll raise some issues if you need to manipulate the data in the db (e.g. adding two of those long values) but should suffice for simple storage. — yieldsfalsehood, Dec 05 '13 at 16:26
After some reading I think you'd want to make use of a library like long.js: http://docs.closure-library.googlecode.com/git/closure_goog_math_long.js.html Straight javascript doesn't work well with 64bit values it seems. — Andrew Walters, Dec 05 '13 at 16:48
Please give a few examples that the code could use as test cases, e.g. hi=?, lo=?, result=? Also, are negative numbers just a positive number with a sign bit or twos complement? Maybe you could do an example with a negative number. I have some code to do what you want with 64bit unsigned numbers at the moment, so I just need a little more information about the negative numbers to get it working with signed numbers. — kybernetikos, Dec 05 '13 at 18:13

score 1 · Answer 1 · edited Apr 13 '17 at 12:38

I adapted the base conversion code from https://codegolf.stackexchange.com/questions/1620/arbitrary-base-conversion. Mistakes are mine, clevernesses are theirs.

I also had to add a bunch of code to deal with negative numbers (twos complement).

This code is ecmascript5, and will need slight reworking to work in older browsers.

function convert(hi, lo) {
  function invertBit(bit) {
    return bit == "0" ? "1" : "0";
  }

  function binaryInvert(binaryString) {
    return binaryString.split("").map(invertBit).join("");
  }

  function binaryIncrement(binaryString) {
    var idx = binaryString.lastIndexOf("0");
    return binaryString.substring(0, idx) + "1" + binaryInvert(binaryString.substring(idx + 1));
  }

  function binaryDecrement(binaryString) {
    var idx = binaryString.lastIndexOf("1");
    return binaryString.substring(0, idx) + binaryInvert(binaryString.substring(idx));
  }

  function binaryAbs(binaryString) {
    if (binaryString[0] === "1") {
      return invertBits(binaryDecrement(binaryString));
    }
    return binaryString;
  }

  function to32Bits(val) {
    var binaryString = val.toString(2);
    if (binaryString[0] === "-") {
      binaryString = Array(33 - (binaryString.length - 1)).join("1") + binaryInvert(binaryString.substr(1));
      return binaryIncrement(binaryString);
    }
    return Array(33 - binaryString.length).join("0") + binaryString;
  }

  var fullBinaryNumber = to32Bits(hi) + to32Bits(lo);
  var isNegative = fullBinaryNumber[0] === "1";

  fullBinaryNumber = binaryAbs(fullBinaryNumber);

  var result = "";

  while (fullBinaryNumber.length > 0) {
    var remainingToConvert = "", resultDigit = 0;
    for (var position = 0; position < fullBinaryNumber.length; ++position) {
      var currentValue = Number(fullBinaryNumber[position]) + resultDigit * 2;
      var remainingDigitToConvert = Math.floor(currentValue / 10);
      resultDigit = currentValue % 10;
      if (remainingToConvert.length || remainingDigitToConvert) {
        remainingToConvert += remainingDigitToConvert;
      }
    }
    fullBinaryNumber = remainingToConvert;
    result = resultDigit + result;
  }
  return (isNegative?"-":"") + result;
}

Examples:

> // largest negative number -2^63 (just the most significant bit set)
> convert(1 << 31, 0)
'-9223372036854775808'
> // largest positive number
> convert(0x7fffffff, 0xffffffff)
'9223372036854775807'
> // -1 is all bits set.
> convert(0xffffffff, 0xffffffff)
'-1'

score 1 · Accepted Answer · answered Jul 09 '21 at 05:03

I've done exactly this in Javascript in a quick'n'dirty-but-works'n'fast manner at: Int64HighLowToFromString, using 53-bit mantissa double precision arithmetic and 32-bit bit operations, specialized for decimal input/output.

function Int64HiLoToString(hi,lo){
  hi>>>=0;lo>>>=0;
  var sign="";
  if(hi&0x80000000){
    sign="-";
    lo=(0x100000000-lo)>>>0;
    hi=0xffffffff-hi+ +(lo===0);
  }
  var dhi=~~(hi/0x5af4),dhirem=hi%0x5af4;
  var dlo=dhirem*0x100000000+dhi*0xef85c000+lo;
  dhi += ~~(dlo/0x5af3107a4000);
  dlo%=0x5af3107a4000;
  var slo=""+dlo;
  if(dhi){
    slo="000000000000000000".slice(0,14-slo.length)+dlo;
    return sign+dhi+slo;
  }else{
    return sign+slo;
  }
}

Most likely this is what you needed.

score 0 · Answer 3 · edited May 23 '17 at 12:09

According to JavaScript can't handle 64-bit integers, can it?, native numbers in Javascript have 53 bits of mantissa, so JS can't deal with 64 bits integers unless using specialized libraries.

Whatever the datatype and implementation limits, I assume you want to compute the Two's complement of the initial 64 bits unsigned number, to convert it from the [0 ... 2^64-1] range into the [-2^63 ... 2^63-1] range.

high is presumably the initial unsigned 64 bits number divided by 2^32, and low is the remainder.

The conversion to a signed 64 bits should go like this:

if high>=2^63 then
   s64 = -(2^64-(high*2^32+low))
 else
   s64 = high*2^32+low;

In a PostgreSQL function, this can be done using the exact-precision numeric type to avoid overflows in intermediate multiplications, and downcast the final result to bigint (signed 64 bits):

create function concat64(bigint, bigint) returns bigint
as $$
 select (case when $1>=2147483648
  then -(18446744073709551616::numeric-($1*4294967296::numeric+$2))
  else $1*4294967296::numeric+$2 end)::bigint;
$$ language sql;

The input arguments have to be bigint (64 bits) because postgres doesn't have unsigned types. They're assumed to be in the [0..4294967296] range and the output should be in the [-9223372036854775808..9223372036854775807] range.

Convert two 32 bit integers to one signed 64 bit integer string

3 Answers3