Time will tell

SYSDATE is sufficient?
Today almost every date is stored in type DATE. One thought is to keep it simple and stay with that approach. “It works today why think about it”.

This document describes facilities worth taking under consideration. There might be scenarios where the SYSDATE-approach is not enough.

Things could be so that if you set the DB-server in UTC and the database time in init.ora you are OK.

Decide on Date OR time
The first decision is date OR time? Remember

SELECT TRUNC(SYSDATE)FROM DUAL;

gives you the date only. Whilst

SELECT SYSDATE FROM DUAL;

will present you date and time. Essential in comparisons between DATE-variables, when the intention is to perform something a special date.

Datatypes for date and time

There are four data types for date and time available.

Datatype	Description
DATE	Date and time, prior to Oracle 8i the one and only. Reflects DATE and DATETIME in Oracle Forms.
TIMESTAMP	Date and time, no respect to time zone. TIMESTAMP(0) equivalent to DATE. Resolution: billionth of a second. Default resolution: millionth of a second.
TIMESTAMP WITH TIME ZONE	Date and time along with time zone
TIMESTAMP WITH LOCAL TIME ZONE	Date and time (with fractions) along with time zone in DB local time zone.

PL/SQL-variables of this type “local” is regarded as session time zone.
Values transferred between DB-columns and PL/SQL-variables are converted from one time zone to the other.

What’s the date and time?
There are two ways of accessing server date and time.

SYSDATE (datatype DATE) the old fashion way, must be used since there are no other ways if you are using Oracle 8i.

SYSTIMESTAMP (TIMESTAMP) the new way that also gives you time zone.

There are three ways of getting the sessions date and time.

• 1. CURRENT_DATE of type DATE.
  
• 2. CURRENT_TIMESTAMP that is of type TIMESTAMP WITH TIME ZONE
  
• 3. LOCALTIMESTAMP of type TIMESTAMP.