Functions to Get the Date and Time

Getting the date and time in PL/SQL used to be easy: you used the SYSDATE function and that was it. In Oracle9i, you have a number of other options at your disposal, and you need to understand how they work and what your choices are. Following are the specifications for all the Oracle9i functions that return the current date and time:

CURRENT_DATE RETURN DATECURRENT_TIMESTAMP RETURN TIMESTAMP_TZ_UNCONSTRAINEDLOCALTIMESTAMP RETURN TIMESTAMP_UNCONSTRAINEDSYSDATE RETURN DATESYSTIMESTAMP RETURN TIMESTAMP_TZ_UNCONSTRAINED

The SQL reference manual shows an optional precision parameter for CURRENT_TIMESTAMP and LOCALTIMESTAMP. In Oracle9i Release 1, SQL supports that parameter; PL/SQL does not. PL/SQL does support the parameter in Oracle9i Release 2.

So which function should you use in a given situation? The answer depends on several factors, which you should probably consider in the following order:

1. You are using a release prior to Oracle8i or need to maintain compatibility with such a release. In this case, your choice is simple: use SYSDATE.

2. Time zone is an issue. The database is associated with a time zone, and so is each session; your session may be in a different time zone from the database. Do you want the time with respect to your time zone or with respect to the server's time zone?

3. Datatype is an issue. Some functions return DATE, one returns TIMESTAMP, and yet others return TIMESTAMP WITH TIME ZONE values. After deciding on session time versus server time, narrow your choice based on the function's return type.

Use Table 10-6 in conjunction with these three factors to help you decide which function to use. Note that, unfortunately, Oracle didn't maintain parallelism in its nomenclature. LOCALTIMESTAMP and SYSTIMESTAMP have parallel names but return different datatypes.

Table 10-6. Comparison of functions that return current date and time
Function	Time zone	Datatype
CURRENT_DATE	Session	DATE
CURRENT_TIMESTAMP	Session	TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP	Session	TIMESTAMP
SYSDATE	Server	DATE
SYSTIMESTAMP	Server	TIMESTAMP WITH TIME ZONE

For example, if you want the current date and time with respect to the session, and you want to put that value into a DATE variable, you should use the CURRENT_DATE function. On the other hand, if you are working with dates from different time zones using TIMESTAMP WITH TIME ZONE variables, choose the CURRENT_TIMESTAMP function.

If you decide to use a function that returns the time in the session time zone, be certain that your DBA has correctly configured the server and that you've correctly specified your session time zone. The following example illustrates the difference between CURRENT_TIMESTAMP, which is used to get the time in the session's time zone, and SYSTIMESTAMP, which is used to get the time in the server's time zone.

BEGIN DBMS_OUTPUT.PUT_LINE(CURRENT_TIMESTAMP); DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);END;

The output is:

24-FEB-02 04.55.54.803009000 PM -08:0024-FEB-02 07.55.54.804221000 PM -05:00

In this example, the session is on U.S. Pacific Standard Time (-8:00) while the server is on U.S. Eastern Standard Time (-5:00). Note the time difference of three hours between the two values. Also note the respective time zone displacements.

What if there's no function to return a value in the datatype that you need? For example, what if you need the server time in a TIMESTAMP variable? The good news is that you can allow Oracle to convert the types for you. Even better might be to use an explicit CAST. For example:

DECLARE a TIMESTAMP; b TIMESTAMP;BEGIN a := CAST(SYSTIMESTAMP AS TIMESTAMP); b := SYSDATE; DBMS_OUTPUT.PUT_LINE(TO_CHAR(a,'DD-MON-YYYY HH:MI:SS AM')); DBMS_OUTPUT.PUT_LINE(TO_CHAR(a,'DD-MON-YYYY HH:MI:SS AM'));END;

The output is:

24-FEB-2002 06:46:39 PM24-FEB-2002 06:46:39 PM

The call to SYSTIMESTAMP uses CAST to make the conversion from TIMESTAMP WITH TIME ZONE to TIMESTAMP explicit. The call to SYSDATE allows the conversion from DATE to TIMESTAMP to happen implicitly.

Be cognizant of hardware and operating-system limitations if you are using these functions for subsecond timing purposes. The CURRENT_TIMESTAMP, LOCALTIMESTAMP, and SYSTIMESTAMP functions return values in either the TIMESTAMP WITH TIME ZONE or TIMESTAMP datatypes. These datatypes allow you to resolve time down to the billionth of a second. That's all well and good, but think about where that time comes from. Oracle (probably) gets the time from the operating system, and the operating system depends at some level on the hardware. If your operating system or underlying hardware tracks time only to the hundredth of a second, Oracle won't be able to return results any finer than that. For example, when using Suse Linux 7.2 on an Intel x86 processor we can resolve time only to the millionth of a second (six digits), whereas we do seem to get values resolved to the billionth of a second when running Windows XP on the same hardware.