Oracle date/time functions

Oracle has an array of date functions that could be confusing because they may seems to give the same output at first sight, i will try to explain here the relation between this functions and the time zone.


First a little bit about time zones, the world is divided into 24 time zones starting at 0 from Greenwish (GMT Greenwish Mean Time , the actual or new time system is called UTC Coordinated Universal Time ) , im on Cali , Colombia so my time zone is UTC -5:00, that means that when it is 12:00 UTC then it is 7:00 here. The picture below shows all the time zones in the world:



current_timestamp VS systimestamp VS localtimestamp


When you issue this three functions apparently they give you the same date ,hour and time zone (except for localtimestamp who doesnt give time zone):

SQL>select current_timestamp,systimestamp, localtimestamp from dual;



CURRENT_TIMESTAMP
---------------------------------------------------------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
29/12/05 15:17:30,214000 -05:00
29/12/05 15:17:30,214000 -05:00
29/12/05 15:17:30,214000

But after changing the session time zone you can notice (below) that systimestamp remains on -5:00 time zone while the other ones show you the date / time in -4:00 time zone. That is because the current_timestamp and localtimestamp works with the session time zone while systimestamp with the database time zone:

SQL> alter session set time_zone= '-4:00';

SQL>select current_timestamp,systimestamp, localtimestamp from dual;



CURRENT_TIMESTAMP
---------------------------------------------------------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
29/12/05 16:19:36,666000 -04:00
29/12/05 15:19:36,666000 -05:00
29/12/05 16:19:36,666000

sysdate VS current_date

Finally there is no difference in the output of this other two functions if you dont get the time (the NLS_DATE_FORMAT is set to'DD-MON-YYYY' ) :

SQL> column sysdate format a15

SQL> column current_date like sysdate;

SQL> select sysdate, current_date from dual;

SYSDATE                  CURRENT_DATE
--------------- ---------------
29/12/05        29/12/05

But current_date works with the session time zone so if we can see the time it will show the date /time in the session time zone:

select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS' ) sdate,
to_char(current_date,'DD-MM-YYYY HH24:MI:SS' )  curr_date 
from dual;

SDATE                        CURR_DATE
---------------              ---------------
29/12/2005  15:19:55         29/12/05  16:19:55