CODEWITHSUNDEEP
sqloracle-database
Ashwin
Ashwin

Reputation: 1230

SQL equivalent of Java code

I need to generate an ID as the first 32 bits being Unix timestamp value and the next 32 bits being the sequence number. The Java equivalent of it would be 

tID  = (long) timeMinutes << 32 | sequence & 0xFFFFFFFFL; // Upper 32 bits are the Unix minutes lower 32 are the sequence number

I am trying to create this ID in SQL so that I can support an upsert. Since Oracle is limited to bitAND I am using bitAND() to create the SQL equivalent of the above. The bitwise OR using bitAND is produced as

x + y - bitand(x,y)

x = timeMinutes << 32 = trunc((cast(SYS_EXTRACT_UTC(systimestamp) as date) - to_date('1-1-1970 00:00:00', 'MM-DD-YYYY HH24:Mi:SS'))*24*3600) * power(2,32)
y = sequenceNumber & 0xFFFFFFFFL = bitAND(sequenceNumber,?)

I am at a loss as to what the equivalent mask for the sequence number (The question mark in the last expression) will be.

Upvotes: 0

Views: 201

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

Ignoring why you're doing this for now, 0xFFFFFFFFL is 2^32 - 1, so you could use:

bitand(sequence, power(2, 32) - 1)

Using a fixed epoch time of:

Tue Sep  8 19:18:45 BST 2015
1441736325

... and made up sequence value of 2549626543, your Java code generates:

x:   6192210365330227200
y:   2549626543
tID: 6192210367879853743

Demo Oracle code:

set serveroutput on
declare
  x number;
  y number;
  tID number;
begin
  x := 1441736325 * power(2, 32);
  y := bitand(2549626543, power(2, 32) - 1);
  tID := x + y - bitand(x, y);

  dbms_output.put_line('x:   ' || x);
  dbms_output.put_line('y:   ' || y);  
  dbms_output.put_line('tID: ' || tID);  
end;
/

PL/SQL procedure successfully completed.

x:   6192210365330227200
y:   2549626543
tID: 6192210367879853743

... gets the same output.

Using a sequence value higher than 2^32, e.g. 92549626543, also yields the same; from Java:

x:   6192210365330227200
y:   2355313327
tID: 6192210367685540527

From Oracle:

x:   6192210365330227200
y:   2355313327
tID: 6192210367685540527

As @Sentinel pointed out, bitand(x, y) will always be zero, as x only has the first 32 bits, and y only has the second 32 bits - so there is no overlap. So you can drop that part and just do:

tID := x + y;

Or in longer form now there is no repetition:

select ((cast(sys_extract_utc(systimestamp) as date) - date '1970-01-01')
    * 86400 * power(2,32)) + bitand(:sequence, power(2, 32) - 1)
from dual;

or with the same fixed values:

select (1441736325 * power(2, 32)) + bitand(2549626543, power(2, 32) - 1)
from dual;

The results are the same as above.

Upvotes: 2

Related Questions