Date and Time Guidelines

DB2 provides sophisticated facilities for processing date and time data. First, DB2 provides native data types for storing date and time data. By storing date and time data directly using data types specifically designed for the data, the user does not need to transform the data to and from another data type. This simplifies program development and makes processing the data more efficient. Whenever you wish to store date and/or time data in a DB2 table, always use the appropriate date or time data type, instead of a character or numeric data type.

The date and time data types are:

DATE	a date stored as 4 bytes
TIME	a time stored as 3 bytes
TIMESTAMP	a combination of date and time stored as 10 bytes

Using TIMESTAMP Versus TIME and DATE

It is obvious when to use DATE and TIME data types: DATE for storing dates and TIME for storing times. But what if you must store both date and time information on a single row in DB2. Is it better to use a single TIMESTAMP column or two columns , one stored as DATE and the other as TIME ?

The answer to this question depends on the specific situation. Consider the following points before making your decision:

• With DATE and TIME you must use two columns. TIMESTAMP uses one column, thereby simplifying data access and modification.

• The combination of DATE and TIME columns requires 7 bytes of storage, while a TIMESTAMP column requires 10 bytes of storage. Using the combination of DATE and TIME columns will save space.

• Index space requirements can be minimized when DATE and TIME are used separately, but only the date needs to be indexed. Additionally, indexing a DATE and TIME as separate columns provides the ability to assign an ascending sequence to the date and a descending sequence to the time (and visa versa).

• If you need to provide indexed access by date only (or time only) use two separate columns. Achieving indexed access for a TIMESTAMP column can be tricky when only the date value is supplied, and impossible when only the time value is supplied.

  For example, say TRACKING_TIMESTAMP is indexed. Most programmers would write code something like this:

  
  

   AND DATE(B.TRACKING_TIMESTAMP) < :WSDATE AND DATE(B.TRACKING_TIMESTAMP)  7 DAYS  >= :WSDATE 

  
  

  The index would not be used in this case. Instead, the programmers must be trained (and reminded) to write the SQL like this:

  
  

   AND B.TRACKING_TIMESTAMP < TIMESTAMP(:WSDATE,'00.00.00')   AND B.TRACKING_TIMESTAMP >= TIMESTAMP(:WSDATE,'00.00.00') - 7 DAYS 

  
  

• TIMESTAMP provides greater time accuracy, down to the microsecond level. TIME provides accuracy only to the second level. If precision is important, use TIMESTAMP . Use TIME if you do not need the time value stored to the microsecond level.

• Date and time arithmetic is easier to implement using TIMESTAMP data instead of a combination of DATE and TIME . Subtracting one TIMESTAMP from another results in a TIMESTAMP duration. To calculate a duration using DATE and TIME columns, two subtraction operations must occur: one for the DATE column and one for the TIME column.

• It is easier to format DATE and TIME columns via local DATE and TIME exits, the CHAR function, and the DATE and TIME precompiler options. These facilities are not available for TIMESTAMP columns. If the date and time information is to be extracted and displayed on a report or by an online application, the availability of these DB2-provided facilities for DATE and TIME columns should be considered when making your decision.

Displaying Dates and Times

DB2 provides four built-in options for displaying dates and times:

Format	Date Display	Time Display
ISO	YYYY-MM-DD	HH.MM.SS
USA	MM/DD/YYYY	HH:MM (AM or PM)
EUR	DD.MM.YYYY	HH.MM.SS
JIS	YYYY-MM-DD	HH:MM:SS

Date and time values will display, and be returned to your programs, as character string data formatted according to the format chosen by your site. The default is ISO . It is also possible to define your own installation-specific defined formats using a LOCAL format exit.

You can also change the display format by using built-in functions (to be discussed in Chapter 3).

Workaround Non-Standard Dates

What if you wish to work with a date in a format that does not fit into any of those supported by DB2? For example, you have a date stored in a character column using a format like YYYYMMDD (with no dashes or slashes ), and you need to compare it to a DB2 date.

One potential solution to this problem is to convert the character column into a valid DB2 date format. You can use the SUBSTR function to break the character column into the separate components . For example, SUBSTR(column,1,4) returns the year component, SUBSTR(column,5,2) returns the month, and SUBSTR(column,7,2) returns the day.

Then you will need to concatenate all of these into a format that DB2 recognizes, for example, the USA format, which is MM/DD/YYYY. This can be done as follows :

 SUBSTR(column,5,2)  "/"  SUBSTR(column,7,2)  "/"  SUBSTR(column,1,4) 

Then you can use the DATE function to convert this character string into a date that DB2 will recognize. This is done as follows:

 DATE(SUBSTR(column,5,2)  "/"  SUBSTR(column,7,2)  "/"  SUBSTR(column,1,4)) 

The result of this can be used in date arithmetic with other dates or date durations. Of course, it might not perform extremely well, but it should return the results you desire . Of course, all of these gyrations can be avoided by using DB2 DATE , TIME , and TIMESTAMP data types instead of forcing the data into CHAR or numeric data types.

Also, keep in mind that you can define your own installation-specific defined DATE and TIME formats using a LOCAL format exit.

Date and Time Arithmetic

Another nice feature of DATE and TIME data is the ability to perform arithmetic functions. The plus ( + ) and minus ( - ) operations can be used on date and time values and durations. A duration is a number used to represent an interval of time. DB2 recognizes four types of durations.

• A labeled duration explicitly specifies the type of duration. An example of a labeled duration is 15 MINUTES . Labeled durations can specify the duration in years , months, days, hours, minutes, seconds, or microseconds. A labeled duration can only be used as an operand of an arithmetic operator, and the other operand must have a data type of DATE , TIME , or TIMESTAMP .

• A DATE duration is a DECIMAL(8,0) number that has the format YYYYMMDD . The YYYY represents the number of years in the duration, MM the number of months, and DD the number of days. When you subtract one date from another, the result is a date duration in this format.

• Similar to DATE durations, DB2 also supports TIME durations. A TIME duration is a DECIMAL(6,0) number with the format HHMMSS . The HH represents the number of hours, MM the number of minutes, and SS the number of seconds. When you subtract one time from another, the result is a time duration in this format.

• A TIMESTAMP duration is more complex than date and time durations. The TIMESTAMP duration is a DECIMAL(20,6) number having the format YYYYXXDDHHMMSSZZZZZZ . The duration represents YYYY years, XX months, DD days, HH hours, MM minutes, SS seconds, and ZZZZZZ microseconds. When you subtract a TIMESTAMP from a TIMESTAMP , you get a TIMESTAMP duration.

CAUTION

The numeric component of a labeled duration cannot be substituted with a host variable or parameter marker ”it must be a numeric literal.

The rules for date and time arithmetic are somewhat complex. Remember that only addition and subtraction can be performed on data and time data (no division or multiplication). For addition, one of the two operands must be a duration. This stands to reason. For example, two dates cannot be added together, but a duration can be added to a date. The same goes for two times.

For addition, use the matrix in Table 2.2 to determine what type of duration is valid for which data type. For example, for TIME data types, a labeled duration or a TIME duration can be specified in the addition expression.

Table 2.2. Date and Time Addition Table

Date Type	Labeled	Date	Time	Timestamp
DATE	YES	YES	NO	NO
TIME	YES	NO	NO	NO
TIMESTAMP	YES	YES	YES	YES

For labeled durations, they must be appropriate durations. For DATE , the labeled duration must specify years, months, or days only; for TIME , the label duration must specify hours, minutes, or seconds only. All of the preceding options for labeled durations are valid for TIMESTAMP data.

The result of adding a DATE and a duration is another DATE ; a TIME and a duration is another TIME ; and a TIMESTAMP and a duration is another TIMESTAMP .

For subtraction, the rules are different. Instead, the result of subtracting one date or time value from another date or time value results in a duration.

For DATE columns, you can subtract another DATE , a DATE duration, an appropriate labeled duration (years, months, or days), or a character representation of a DATE . The result is a DATE duration.

For TIME columns, you can subtract another TIME , a TIME duration, an appropriate labeled duration (hours, minutes, or seconds), or a character representation of a TIME . The result is a TIME duration.

For TIMESTAMP columns, you can subtract another TIMESTAMP , a TIMESTAMP duration, any labeled duration, or a character representation of a TIMESTAMP .

Do Not Mix DB2 Dates with Non-Dates in Arithmetic Expressions

Consider an example where you decide to store dates using a column defined as DECIMAL(8,0) , instead of as a DATE . If you mix this column with a DATE column in arithmetic expressions, the results will be incorrect. For example, subtracting the column (in this example, DATE_COL ) from a DB2 date (in this example, the current date), as follows

 CURRENT DATE  DATE_COL 

will not return a date duration, as you might expect. Instead, DB2 will interpret the DATE_COL value as a duration. Consider, for example, the value of DATE_COL being 19720212 , which is meant to represent February 12, 1972. Instead, DB2 interprets it as a duration of 1,972 years, 2 months, and 12 days.

Use Functions to Return Total Number of Days

If you keep an open mind, most every problem is solvable in DB2. A common requirement when date arithmetic is involved is to return results in a common unit, for example, number of days. Let's examine using DB2 date subtraction to express the resulting duration as an exact total number of days. To illustrate , consider this query:

 SELECT DATE('03/01/2004') - DATE('12/01/2003') 

It will return a duration of 00000300 (that is, three months). We want total number of days. Now keep in mind that those three months encompass a 29-day February plus a 31-day January plus a 31-day December (total 91 days). How do we do this?

The answer lies in using the DAYS function. The following should return what you need:

 SELECT DAYS('03/01/2004') - DAYS('12/01/2003') 

This query will return to you the exact number of days between the two dates. More information on DB2 functions (like DAYS ) can be found in Chapter 3.