Oracle SQL current_timestamp function

In many (database) applications it is important to keep some sort of logging on all kinds of actions taken. For example when was a record created or when was a record changed. When we are talking about a system which mainly interact with human users this can in most cases be done by stating a date and time which is precise up to the second. However in some cases, in many cases systems where you see a lot of machine to machine communication this is not accurate enough.

In a lot of cases where Oracle SQL developers implement a timestamp option in the application they make use of the systdate function from within the Oracle database which returns the current time and date based upon the operating system clock of the operating system the Oracle database is running on.

There is however a more precise way of getting the exact date and time which is using the current_timestamp function from the datbase.

You can extend the current_timestamp with a precision parameter. If you do not provide a precision parameter the default will be 6.

For example if you do;

select
  current_timestamp(1)
from
    dual;

you wil get

10-NOV-12 02.20.30.600000000 PM US/PACIFIC

and if you do;

select
  current_timestamp(3)
from
    dual;

you wil get

10-NOV-12 02.20.30.615000000 PM US/PACIFIC

As you can see the provided precision in influencing the the number of milliseconds that is used in your timestamp. Depending on the nature of your application and the accuracy that is needed for the timestamping this can be very important and worth looking into.