Thursday, December 22, 2005

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

11 comments:

W.Spurgeon said...

Great, very helpful, thx!

Anonymous said...

Thank you!

Greedings from Austria!

Ashwin Ram said...

Very useful information, well presented.
Thank you!

Anonymous said...

Very helpful. Thank you.

Anonymous said...

You mention that SYSDATE and CURRENT_DATE are the same, however it seems that they would be different if the session time zone is set differently than the database time zone, and the time happens to be around midnight. One timezone would have one date, and the other zone would be either a day later or earlier.

Carlos said...

I didn't say sysdate and current_date functions were the same, what I said ( or at least tried) is that you can be tricked with the output of this two functions if you don't notice that current_date shows the session date/time.

Bad Blogger said...
This comment has been removed by the author.
Bad Blogger said...

Round and round i went, Here is stop.

Thanks and Cheers,
Bad Blogger

Anonymous said...

We have a situation with the database in (United States) Central Time and data entry in Pacific Time. Time was affected by switching PST and PDT. when copying data from Oracle to SQL Server 2005, the dates became messed up when an entry was made after 10:OOPM PST verses CST and after 9:00PM from PDT to CST. I finally settled on this fix:
SELECT MyDATE at time zone 'US/Central'

Dragon said...

very helpful post;
but suppose system(os) time and db time are different, how can we get the timestamp of database?

hemant said...

thanks