SYSTIMESTAMP and SYSDATE VS. CURRENT_TIMESTAMP and CURRENT_DAY

Most DBAs I've spoken to are familiar with the function SYSDATE, but there are some other functions, too, that may prove to be useful, even for DBAs that aren't directly involved in developing applications.
For example, when querying the data dictionary, it can certainly be an advantage to be familiar with some other built-in date functions that comes with the Oracle database.

The two first functions reflect the datetime settings of the operating system on which the database runs:

* The SYSTIMESTAMP function returns a value of the TIMESTAMP WITH TIME ZONE datatype. It includes fractional seconds and time zone.
* the SYSDATE function returns a value of the DATE datatype.It includes timestamp, but not fractional seconds, nor time stamp.

The three functions below reflects the settings of your session:

* The CURRENT_DATE fuction returns a value of the DATE datatype within the session time zone
* The CURRENT_TIMESTAMP function returns a value of the TIMESTAMP WITH TIME ZONE data type within the session time zone
* The LOCALTIMESTAMP function returns a value of the TIMESTAMP data type, within the session time zone

Sitting in Oslo and querying a database with dbtimezone set to +2:00:

select dbtimezone from dual;

DBTIME
+02:00

I get the following default output when I execute the queries at 11:45:

select sysdate, systimestamp, current_date,current_timestamp(5), localtimestamp(3) from dual

   
SYSDATE
   
SYSTIMESTAMP
   
CURRENT_DATE
   
CURRENTTIMESTAMP
   
LOCALTIMESTAMP
   
13.07.2016 11:45:52
   
13.07.2016 11.45.52,597707 +02:00 
   
13.07.2016 11:45:52 
 13.07.2016 11.45.52,59771 +02:00 
  
 13.07.2016 11.45.52,598

Now I will change my session time zone:

alter session set time_zone='America/New_York';

Session altered.

Execute the functions again, and the difference between the function becomes apparent:

select sysdate, systimestamp, current_date,current_timestamp(5), localtimestamp(3) from dual;

SYSDATE	SYSTIMESTAMP	CURRENT_DATE	CURRENTTIMESTAMP	LOCALTIMESTAMP
13.07.2016 11:49:15	13.07.2016 11.49.15,381888 +02:00	13.07.2016 05:49:15	13.07.2016 05.49.15,38189 AMERICA/NEW_YORK	13.07.2016 05.49.15,382

Current_date now returns the date and time as it would be in New York
Current_timestamp does the same, but adds the region name as the time zone indicator
Localtimestamp returns the timestamp as it would be in New York

The precision in the current_timestamp and localtimestamp (5 and 3, respectively) are set to override the default precision of 6 for fractional seconds.