The system time

While simple datatypes like integers and text strings behave as you would expect them to, dates are particularly difficult to manipulate. All database platforms provide their own set of functions for handling dates and times, but they are not particularly intuitive.

Date and time values are stored in an internal format which means nothing to the ordinary database user. Oracle, for example, stores dates as the time elapsed since 1 January, 4712BC. This is known as a Julian date format, and thus 1 February 4712 would be stored as 31 (equal to the number of days in January). Dates are counted from midnight, and periods of time less than a day are calculated as a fraction of a day. Thus 1200 on 1 February 4712 would be 31.5.

The Julian date format makes the comparison of dates and time very simple for the DBMS to calculate, but it is no good for representing dates for the user.

Another complication is that there is a huge range of formats for dates and times that are appropriate under different circumstances. For example, British dates are typically written in the form DD/MM/YYYY where DD represents the number of the day in the month, MM represents the number of the month in the year, and YYYY is the four-digit representation of the year. American dates on the other hand are typically written in the format MM/DD/YYYY. To interpret the date 1/2/2012 therefore, we need to know whether it is in British or American format since it could represent 1 February, or 2 January.

Proper handling of dates and times usually means converting from the internal date format to a text representation. Likewise, storing a date value usually means converting from a text representation to the internal date format.

This set of notes contains some practical examples that you may want to try for yourself. If so, you will need to create a temporary table:

    CREATE TABLE date_tab (date_col DATE);

Check that the table has been created correctly using the command

    desc date_tab

All computer systems maintain an internal clock that can be referenced in the programming language you are using. Oracle provides a pseudocolumn called SYSDATE that can be used in SQL statements. In SQL*Plus, type the following:

    SELECT SYSDATE
    FROM   dual;

There are two things to note:

• The table dual is a dummy table created automatically in Oracle databases. It gives you a table to use in your FROM clause in cases like this one where the data you want is not stored in a table.
• The displayed value contains day, month and year elements as expected.

What is not obvious from this last result is that the system date actually contains a time component as well, but it is not displayed by default. To see how this can lead to errors, we will store a record in our date_tab table, and compare it to the system date. Use the statement below:

    INSERT INTO date_tab VALUES (SYSDATE);

Check that the record has been created properly using the query:

    SELECT * from date_tab;

The displayed result should be self-explanatory. Now what will happen if we include a WHERE clause that restricts the results to those where the stored date is equal to today? Let’s try:

    SELECT * from date_tab
    WHERE date_code = SYSDATE;

We stored today’s date; it’s still the same day – why are there no results? The reason is that the small number of seconds that have elapsed since you created the record means that the stored value is different from the current SYSDATE because of the time component.

SYSDATE is often used as a default value when storing time-dependent entries in a table. It is important to remember therefore that the stored values include the time of storage as well as the date, or to explicitly truncate the value so that the time element is removed.

Further reading

SYSDATE

SQL Dates