SQL equivalent of Java code

Question

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.

Seems to me you need a unique identifier out of oracle, isn't that what ORA_ROWSCN was intended for with row dependencies enabled at the table level? Why reinvent the wheel?docs.oracle.com/cd/B19306_01/server.102/b14200/… or am I missing why you're doing this? — xQbert
– xQbert, Commented Sep 8, 2015 at 17:29
I asked why we are using this instead of just a sequence. We have partitions based on time and I was told that it is beneficial to have this instead of just a sequence. — Ashwin
– Ashwin, Commented Sep 8, 2015 at 18:13

Alex Poole · Accepted Answer · 2015-09-08 19:55:25Z

2

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.

edited Sep 8, 2015 at 19:55

answered Sep 8, 2015 at 18:27

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sentinel Over a year ago

Won't bitand(x, y) always be zero? The low order 32 bits of X will always be 0x00000000 due to the left shift operation (* power(2, 32)) and the high order bits of Y will always be zero due to the BITAND operation applied to it on assignment. As such you should be able assign X + Y to tID without subtracting the BITAND of X and Y

Alex Poole Over a year ago

True. D'oh. I was copying what the OP said without thinking enough.

Sentinel Over a year ago

X + (Y - BITAND(X, Y)) is the general form for performing a bitwise OR when all you have is bitwise AND. The parenthesis around the last two terms ensures that there are no overflow issues encountered during the addition step. That might not be and issue in oracle, but then again better safe than sorry.

Collectives™ on Stack Overflow

SQL equivalent of Java code

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related