Handling dates and times is probably one of the most complicated and inconsistent topics in SQL. According to our personal experiences, operations with dates often cause confusion and even frustration not only among database users, but in the developers' community, too.
One of the problems is dates are not actually what they look like. So far we were talking only about simple data types that store one value per row. Date and time data types hold a number of elements (year, day, month, hour, etc.). In programming, such data types are called complex and are often represented as structures. When returned as a database query result, date and time fields appear like strings, but in fact they rather are parts of structures, similar to ones in the example below. (We don't use any specific programming language in this example, but rather some kind of pseudocode.)
STRUCTURE DATE { YEAR DECIMAL(4,0), MONTH DECIMAL(2,0), DAY DECIMAL(2,0) }
STRUCTURE TIME { HOUR DECIMAL(2,0), MINUTE DECIMAL(2,0), SECOND DECIMAL(5,2) }
STRUCTURE DATETIME { YEAR DECIMAL(4,0), MONTH DECIMAL(2,0), DAY DECIMAL(2,0), HOUR DECIMAL(2,0), MINUTE DECIMAL(2,0), SECOND DECIMAL(5,2) }
The displayed value just formats and concatenates the fields of this structure. For example, for the YYYY/DD/MM format, the pseudocode may look like this:
CONCAT(CAST(DATETIME.YEAR, STRING), '/', CAST(DATETIME.DAY, STRING), '/', CAST(DATETIME.MONTH, STRING))
Note |
The Dot (.) notation used in the above example is explained in Chapter 1. |
These structures should also have some methods to handle situations when users want to display dates and times in different formats, for example to display time on the 12- or 24-hour scale, show day of week for a certain date, display century, convert it into a different time zone, and so on.
We'll talk more about complex data types later in this chapter.
As we've mentioned before, date and time data types are mandated by SQL99 and handled by different RDBMS implementations quite in a different way. Date and time data types are summarized in Table 3-5.
SQL99 |
Oracle 9i |
DB2 UDB 8.1 |
MS SQL SERVER 2000 |
---|---|---|---|
DATE |
DATE |
DATE |
DATETIME SMALLDATETIME |
TIME [WITH TIME ZONE] |
DATE |
TIME |
DATETIME SMALLDATETIME |
TIMESTAMP[(p)] [WITH TIME ZONE] |
DATE TIMESTAMP [WITH[LOCAL] TIME ZONE] |
TIMESTAMP |
DATETIME SMALLDATETIME |
INTERVAL |
INTERVAL DAY TO SECOND INTERVAL YEAR TO MONTH |
SQL99 supports DATE, TIME, TIMESTAMP, TIME WITH TIME ZONE, and TIMESTAMP WITH TIME ZONE data types.
DATE data type is a structure that consists of three elements: year, month, and day. The year is a four-digit number that allows values from 0000 through 9999; the month is a two-digit element with values from 01 through 12; and the day is another two-digit figure with range from 01 through 31. SQL99 does not have any strict rules on how to implement DATE internally, so vendors can make their own decisions. One vendor could choose something similar to the structures above; others could implement characters, numbers with different scale, and so on.
TIME consists of hour, minute, and second components. The hour is a number from 00 to 23, the minute is a two-digit number from 00 to 59, and the second is either another integer from 00 to 61 or a decimal number with scale of 5 and precision of 3 that can hold values from 00.000 to 61.999.
Note |
The range of values for seconds greater than 59 is to handle the representation of leap seconds, occasionally added to Earth's time. None of our three major vendors has that feature implemented. |
TIMESTAMP is a combination of DATE and TIME data types and includes year, month, day, hour, minute, and second.
TIME WITH TIME ZONE is basically an improvement to the TIME data type. It stores time zone information in addition to standard TIME elements.
TIMESTAMP WITH TIME ZONE is an extension to the TIMESTAMP with information on time zone.
Oracle has DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE data types.
DATE is a slightly confusing data type, because in spite of its name it stores both date and time information and rather corresponds to SQL99 TIMESTAMP. In addition to standard SQL99 TIMESTAMP fields (year, month, date, hour, minute, and second), it also includes century.
TIMESTAMP data type is practically same as DATE, but you can specify an optional precision for the number of digits in the fractional part of seconds. The valid values are numbers from 0 to 9 with the default of 6.
TIMESTAMP WITH TIME ZONE data type speaks for itself. The only difference from TIMESTAMP is a time zone displacement included in its value, which is the difference in hours and minutes between local time and Coordinated Universal Time (UTC), also known as Greenwich Mean Time (GMT).
TIMESTAMP WITH LOCAL TIME ZONE data type is another variation of TIMESTAMP (or TIMESTAMP WITH TIME ZONE). The difference is that it is normalized to the database time zone and the displacement value is not stored with it, but when users query the column, the result is returned in user's local session time zone.
INTERVAL YEAR TO MONTH data type stores a period of time using year and month fields.
INTERVAL DAY TO SECOND data type can store a period of time in days, hours, minutes, and seconds.
Caution |
The Oracle format value for minutes is MI, not MM like most other databases. So, be aware that if you want specify a time format it should look like HH:MI:SS, not HH:MM:SS. MM in Oracle stands for months. |
DB2 has three standard SQL99 date and time data types — DATE, TIME, and TIMESTAMP.
DATE consists of year, month, and day. The range of the year part is 0001 to 9999; month can be from 1 to 12; and the day part ranges from 1 to 28, 29, 30, or 31, depending on the month. DATE in DB2 is stored internally as a string of four bytes. Each byte represents two decimal digits. The first two bytes are for the year, the third is reserved for the month, and the fourth one holds day value. The length of a DATE column is 10 bytes to fit character string representation in literals.
TIME data type is represented internally as a string of three bytes — one byte for hours, one for minutes, and one for seconds. Hour ranges from 0 to 24 (if value is 24, minutes and seconds will be all zeroes). Minute and second components have to be from 0 through 59. The length of a TIME column in DB2 is 8 bytes to allow the appropriate length for a character string representation.
TIMESTAMP data type is a combination of DATE and TIME elements plus a microsecond component. The internal representation is a string of ten bytes (four DATE bytes, three TIME bytes, and additional three bytes for microseconds). The length of a TIMESTAMP column is 26 bytes.
MS SQL Server has two date and time data types, DATETIME and SMALLDATETIME that both represent the combination of date and time values, but have different ranges.
DATETIME can store values from 01/01/1753 to 12/31/9999 with accuracy of 0.00333 seconds. It is stored internally as an eight-byte string. The first four bytes represent the number of days before (or after) 01/01/1900, which is the system reference date. The second four bytes store time in milliseconds passed since midnight.
SMALLDATETIME can hold dates from January 1, 1900 to June 6, 2079, with accuracy to the minute. The internal storage for that data type is four bytes. Again, the first portion (two bytes) stores the number of days after the system reference date (01/01/1900), and the second portion stores time (in minutes after midnight).
Date and time literals are implementation-specific and vary significantly among different vendors.
Oracle lets you specify DATE values as literals if they match special database initialization parameter NLS_DATE_FORMAT, which defaults to DD-MON-YY. (Oracle initialization parameters are usually handled by DBA and are not covered in this book.) You can also use SQL99 literal standard (YYYY-MM-DD) with a DATE prefix, or convert literals into string using the Oracle function TO_DATE (covered in Chapter 10). The three following statements are valid date literals examples. (SHIPMENT_ARRIVDATE_D is a DATE field in Oracle ACME database.)
UPDATE shipment SET shipment_arrivdate_d = '03-SEP-02' WHERE shipment_id_n = 30661;
UPDATE shipment SET shipment_arrivdate_d = DATE '2003-09-02' WHERE shipment_id_n = 30661;
UPDATE shipment SET shipment_arrivdate_d = TO_DATE('September, 02 2003', 'Month, DD YYYY') WHERE shipment_id_n = 30661;
But these are illegal:
UPDATE shipment SET shipment_arrivdate_d = 'YYYY-MM-DD' WHERE shipment_id_n = 30661;
UPDATE shipment SET shipment_arrivdate_d = '03-SEP-02 23:12:45' WHERE shipment_id_n = 30661;
TIMESTAMP and TIMESTAMP WITH TIME ZONE data types also accept the DD-MON-YY format for literals (with optional TIME part); in addition, you can specify literals with a TIMESTAMP prefix:
TIMESTAMP '1997-01-31 09:26:50.124' TIMESTAMP '1997-01-31 09:26:56.66 +02:00' TIMESTAMP '1999-04-15 8:00:00 -8:00' TIMESTAMP '1999-04-15 8:00:00 US/Pacific' TIMESTAMP '1999-10-29 01:30:00 US/Pacific PDT'
The first of these lines is for TIMESTAMP data type, and the other four are for TIMESTAMP WITH TIME ZONE.
Note |
TIMESTAMP WITH LOCAL TIME ZONE data type does not have any literals associated with it. |
Oracle gives you a great deal of flexibility when specifying interval values as literals. Reference Table 3-6 for examples.
INTERVAL LITERAL |
INTERPRETATION |
---|---|
INTERVAL '23-5' YEAR TO MONTH |
Interval of 23 years and 5 months |
INTERVAL '67' YEAR(3) |
Interval of 67 years and 0 months |
INTERVAL '500' MONTH(3) |
Interval of 500 months |
INTERVAL '7' YEAR |
Interval of 4 years (maps to INTERVAL '7-0' YEAR TO MONTH) |
INTERVAL '74' MONTH |
Maps to INTERVAL '6-2' YEAR TO MONTH and indicates 6 years and 2 months |
INTERVAL '7 6:15' DAY TO MINUTE |
Interval of 7 days, 6 hours and 15 minutes |
INTERVAL '40' DAY |
Interval of 40 days |
INTERVAL '11:20' HOUR TO MINUTE |
Interval of 11 hours and 20 minutes |
INTERVAL '10:22' MINUTE TO SECOND |
Interval of 10 minutes 22 seconds |
INTERVAL '25' HOUR |
Interval of 25 hours |
INTERVAL '40' MINUTE |
Interval of 40 seconds |
The following formats for DATE literals are recognized: YYYY-MM-DD (ANSI/ISO), MM/DD/YYYY (IBM US), and DD.MM.YYYY (IBM Europe).
So, these three statements are legal:
UPDATE shipment SET shipment_arrivdate_d = '2003-09-02' WHERE shipment_id_n = 30661
UPDATE shipment SET shipment_arrivdate_d = '09/02/2003' WHERE shipment_id_n = 30661
UPDATE shipment SET shipment_arrivdate_d = '02.09.2003' WHERE shipment_id_n = 30661
But this one is not:
UPDATE shipment SET shipment_arrivdate_d = '02-SEP-2003' WHERE shipment_id_n = 30661
The valid TIME literal formats are: HH.MM.SS (ANSI/ISO and IBM Europe), HH:MM AM|PM (IBM USA), and HH:MM:SS (Japanese Industrial Standard). Also, trailing blanks may be included, and a leading zero may be omitted from the hour part of the time; seconds may be omitted entirely:
'12.23.56' '23:15 AM' '8:45'
The valid string formats for TIMESTAMP literals are YYYY-MM-DD-HH.MM.SS.NNNNNN and YYYY-MM-DD HH:MM:SS.NNNNNN:
UPDATE shipment SET shipment_createdate_d = '2003-10-12-23.34.29' WHERE shipment_id_n = 30661
UPDATE shipment SET shipment_createdate_d = '2003-10-12 23:34:29.345678' WHERE shipment_id_n = 30661
MS SQL Server is probably the friendliest RDBMS in terms of handling date and time. It recognizes the date and time literals enclosed in single quotation marks in many different formats. For example:
'August 15, 2003' '15 August, 2003' '15-AUG-2003' '15 Aug, 2003' '030815' '2003/08/15' '08/15/03' '14:30:24' '04:24 PM' '15 August, 2003 23:00' '15-AUG-2003 22:45:34.345'
All these (and many other) formats are valid for both DATETIME and SMALLDATETIME.