How to get current or custom time in different time zone in Oracle DB ?

To get current time in another time zone use the following :

SQL> select systimestamp at time zone 'ZONE_NAME' from dual ;

ZONE_NAME can be gotten from v$timezone_names view.

To get custom time of different timezone in local time zone do the following :

SQL> select to_char ( to_timestamp_tz ('2021-07-22 02:00 pm us/eastern','yyyy-mm-dd hh:mi pm tzr') at time zone 'europe/minsk', 'dd.mm.yyyy hh24:mi:ss') from dual

Thus, here we can see the local time (time in Minsk in my case) when there is 02:00 PM 22.07.2021 at US Eastern time. 

Here also the other samples :

select to_char (from_tz (to_timestamp ('2021-09-08 07:00am','yyyy-mm-dd hh:miam')  ,'America/Los_Angeles') at time zone 'Europe/Minsk', 'dd.mm.yyyy hh24:mi:ss') as local_time
from dual
/

select to_char (from_tz (cast (to_date ('2021-09-08 07:00am','yyyy-mm-dd hh:miam') as timestamp), 'america/los_angeles') at time zone 'europe/minsk', 'dd.mm.yyyy hh24:mi:ss') as local_time
from dual
/

Good Luck !

How does Oracle database represent numbers ?

Let consider it on small examples : we take natural number 412 and dump it like the following :


SQL> l
  1  select dump(412,16) dump_result from dual
  2  union all
  3* select dump(-412,16) dump_result from dual
SQL>

DUMP_RESULT
------------------------
Typ=2 Len=3: c2,5,d
Typ=2 Len=4: 3d,61,59,66


Oracle represents its numbers as the form 0.(base_100_mantissa)*(100**(base_100_exponent)).

base_100 here means numbers (unique symbols) from 00 to 99.

In both cases first byte contains the information about sign and exponent, the next are the value of mantissa.

The highest bit of the first byte is sign - 1 for positive and 0 for negative numbers.

The exponent (last 7 bits of the first byte) is in the form (base_100_exponent+64) for positive and (255-((base_100_exponent)+64),highest bit is ignored) for negative numbers.

In case of negative numbers the last byte 0x66 (102) is added. Zero is stored as 1 byte of 0x80 (128).
Positive values of bytes of mantissa is being written as (value+1), negative as (101-value).

1. 412

DUMP_RESULT
------------------------
Typ=2 Len=3: c2,5,d

1.1. c2 is 0b11000010

The highest bit is 1 - number is positive.
The exponent is 0b1000010-0b1000000 (64)=0b10 (2)


1.2. Bytes of base_100_mantissa - 0x5 and 0xd. 0x5 is 4+1, 0xd is 12+1 (i.e. similar to our number 412). But we have base_100_mantissa, so 5 transforms to 05 for Oracle.

So, the number 412 is presented as 0.0513*(100**2)

2. -412

DUMP_RESULT
------------------------
Typ=2 Len=4: 3d,61,59,66

Last 0x66 - another evidence of negative number.

2.1. 3d is 0b111101 (61)

The highest bit is 0 - number is negative.
The exponent=255-61-64=130 --> 0b10000010. The highest bit is ignored, so the exponent is 0b10 (2).

2.2. Bytes of base_100_mantissa - 0x61 (97) and 0x59 (89). 101-4=97, 101-12=89. But we have base_100_mantissa, so 4 transforms to 04 for Oracle.

So, the number -412 is presented as -0.0412*(100**2)

We can see Oracle added some tricks to present its numbers. I guess it can be for comparison, sorting, ordering etc.

Let's check our "transformation" :

SQL> select utl_raw.cast_to_number (hextoraw('c2050d')) "check" from dual
/

  check
----------
       412

SQL> select utl_raw.cast_to_number (hextoraw('3d615966')) "check" from dual
/

     check
----------
      -412


Good Luck !