Oracle SQL Date to Long and vice versa

Question

I have the following SQL Query which converts the long number to a date , i cannot convert the date back to the long number . kindly let me know your suggestion.

SELECT to_date((( 1432550197431912935  - POWER(2,60)) /POWER(2,44)) + to_date('01-JAN-1970','DD-MON-YYYY')) from dual

Output

7/9/2013

select     TO_CHAR(((TO_DATE ('09-JUL-2013','DD-MON-YYYY') -to_date('01-JAN-1970','DD-MON-YYYY'))  * POWER(2,44) ) + POWER(2,60)) from dual

Output
1432549301782839296

The long values are not the same.

Solution 1

You are losing too much precision in your conversion to be able to go back the other way. You can get closer by using timestamps instead of dates.

Firstly your initial query was losing the time component completely:

select to_char(date '1970-01-01'
  + (1432550197431912935 - power(2, 60))/power(2, 44), 'YYYY-MM-DD HH24:MI:SS')
from dual;

2013-07-09 01:13:19

... but even with that, converting back has lost way too much:

select ((to_date('2013-07-09 01:13:19','YYYY-MM-DD HH24:MI:SS')
  - date '1970-01-01') * power(2, 44)) + power(2, 60) from dual;

1432550197477589405

Which is closer than the 1432549301782839296 you got, but still quite a long way off.

Part of the problem is the precision of DATE, which is only to the second. If you use TIMESTAMP instead you can get quite close; you can see that the value to have is supposedly very precise:

select timestamp '1970-01-01 00:00:00'
  + numtodsinterval((1432550197431912935 - power(2, 60))/power(2, 44), 'DAY')
from dual;

2013-07-09 01:13:18.775670462

Converting that back is complicated by timestamp arithmetic giving interval results, which you then have to manipulate to get back to a number, first as the original number of days:

select extract(day from int_val)
  + extract(hour from int_val) / 24
  + extract(minute from int_val) / (24 * 60)
  + extract(second from int_val) / (24 * 60 * 60)
from (
select to_timestamp('2013-07-09 01.13.18.775670462', 'YYYY-MM-DD HH24:MI:SS.FF9')
  - timestamp '1970-01-01 00:00:00' as int_val from dual);

15895.0509117554451620370370370370370371

... and then with your power manipulation:

select ((extract(day from int_val)
    + extract(hour from int_val) / 24
    + extract(minute from int_val) / (24 * 60)
    + extract(second from int_val) / (24 * 60 * 60))
  * power(2, 44)) + power(2, 60)
as x
from (
select to_timestamp('2013-07-09 01.13.18.775670462', 'YYYY-MM-DD HH24:MI:SS.FF9')
  - timestamp '1970-01-01 00:00:00' as int_val from dual);

1432550197431912935.09988554676148148148

Which is pretty darn close. You could trunc or round that to the nearest whole number.

---

Just looking at your numbers and the power manipulation each way shows that it seems to be within the precision Oracle can cope with:

select (1432550197431912935 - power(2, 60)) / power(2, 44)
from dual;

15895.050911755445156359201064333319664

select (15895.050911755445156359201064333319664 * power(2, 44)) + power(2, 60)
from dual;

1432550197431912935.000...

Even with a timestamp, you lose some of that, as that first value is going past the 9-digit fractional second limit. The part that represents the fractional seconds - once you've accounted for the 15895 hours etc. - is .0000089776673785814232865555418862 of a day, which is .77567046150943497195839881896768 seconds; the timestamp is rounding that to .775670462. So it's never going to be perfect.

That also leads one to wonder how the original number is being generated; it seems unlikely it actually represents a time down to that extreme precision, since it's below yoctoseconds. It isn't really clear if the 'precision' is actually an artefact of it being manipulated based on powers of 2, but it doesn't look very useful anyway. It's more common to use the Unix-style epoch date, counting seconds or sometimes milliseconds since the epoch date you're using anyway, if it has to be stored as a number at all. This design is... interesting.

Solution 2

Try using the following query:

select     TO_CHAR(((TO_DATE ('09-JUL-2013','DD-MON-YYYY') -to_date('01-JAN-1970','DD-MON-YYYY')) + POWER(2,60) ) * POWER(2,44)) from dual