Lecture 25 
Additional Date and Time Information

Time in Databases

Normally, databases only only model one state of the real world outside -- the current state.  Sometimes it is desirable to have information on past states.  These are called temporal databases.  For example, a factory monitoring system will hold past readings as well as current readings for long term analysis.
 

Time Specifications in SQL-92

The SQL-92 standard defines date, time, and timestamp.  Date and time are obvious, but timestamp contains date and time.  There is also a way to include the time zone.  Additionally, there is what is called UTC (Universal Coordinated Time), which is the military is referred to as "Zulu" time or Greenwich Mean Time (GMT).  The world clock is defined by what time it is in Greenwich, England, which is where this time system was invented.  Additionally, there is a data type call interval, which the the span of time between two points on the time line.

Date Formats

Date Formats

Date Functions

• +, -                               You can add or subtract dates
• sysdate                          Returns the current date and time
• add_months(d, count)    adds count months to date d
• last_day(d)                    Returns the date of the last day of the month that d is in
• to_char(d, format)         Returns the date d in the format specified
• to_date(s, format)         Converts the string s into date, where the format describes the string

Examples

select hdate, sysdate, hdate+2, hdate-2, sysdate-hdate
from employees;

insert into employees hdate
values ( to_date( '12-JAN-99 02:45:30', 'DD-MON-YY HH:MI:SS' );

select to_char(hdate, 'DD-MONTH-YYYY', )
from employees;

CSEE | 461 | 461 F'98 | lectures | news | help