One of the most important database design decisions you will make – for DB2 or any relational DBMS – is to use the proper data types for your columns when building tables. The data type that you choose for each column should be the one that most closely matches the domain of values that the column can be used to store.
Indeed, perhaps the most important design choice is to actually use the date/time data types that are available to you; for DB2 this is DATE, TIME, and TIMESTAMP. The ability to store dates as a native DB2 data type is a great advantage. If you need to store date information in your DB2 tables you should favor using the DATE data type instead of forcing a date into some other data type (such as CHAR or DECIMAL). Many a database design has been ruined (in my opinion) because somebody decided to store date data in a CHAR column.
When DB2 knows that the data should be a DATE or a TIME it can force data integrity such that no non-date/time value could ever be stored in the column. This is a big advantage, but it is not the only one. DB2 also provides numerous display formats so date and time values can be displayed in many different ways without having to store them in specific display formats.
Another big reason is that DB2 allows users to perform date/time arithmetic. So, you can easily use date columns to calculate durations or past and future dates based on a number of days, months, and years. The same type of arithmetic can be used for time and timestamp data. Just think about the application code you would have to write to manipulate and manage date/time values!
How Does Date/Time Arithmetic Work
DB2 enables you to add and subtract DATE, TIME, and TIMESTAMP columns. In addition, you can add date and time durations to or subtract them from these columns.
Arithmetic on date and time data is fairly straightforward. 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.
1. 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.
2. 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.
3. 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.
4. 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.
So, if you want to add one hour to a TIME column you can simply specify TIME_COL + 1 HOUR. Or subtract a day from a date column easily, such as DATE_COL – 1 DAY. Simple, right?
Well, the rules for date and time arithmetic are somewhat complex. Remember that only addition and subtraction can be performed on date 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.
Use date and time arithmetic with care. If you understand the capabilities and features of date and time arithmetic, you should have few problems implementing it. Keep the following rules in mind:
• When you issue date arithmetic statements using durations, do not try to establish a common conversion factor between durations of different types. For example, the date arithmetic statement:
DATE(‘2014/04/03’) - 1 MONTH
is not equivalent to the statement:
DATE(‘2014/04/03’) - 30 DAYS
April has 30 days, so the normal response would be to subtract 30 days to subtract one month. The result of the first statement is 2014/03/03, but the result of the second statement is 2014/03/04. In general, use like durations (for example, use months or use days, but not both) when you issue date arithmetic.
• If one operand is a date, the other operand must be a date or a date duration. If one operand is a time, the other operand must be a time or a time duration. You cannot mix durations and data types with date and time arithmetic.
• If one operand is a timestamp, the other operand can be a time, a date, a time duration, or a date duration. The second operand cannot be a timestamp. You can mix date and time durations with timestamp data types.
Using Date/Time Functions
DB2 also provides a plethora of date/time functions that can be used to simply manipulate and modify date/time values. Let’s take a look at an example.
Suppose you want to express the duration resulting from date subtraction as a total-number-of-days (exact total, and not an approximate total)? Consider this query:
It returns a duration of 00000300 (that is, 3 months). And those 3 months encompass a 29-day February plus a 31-day January plus a 31-day December (total 91 days). So the answer that we want to return is 91.
The answer to this conundrum lies in using the DAYS function. The following will return the result as a number of days:
This query will return to you the exact number of days between the two dates. The DAYS function converts a DB2 date or timestamp into an integer value representing one more than the number of days since January 1, 0001. So converting both dates using the DAYS function and subtracting yields the desired result.
DB2 provides a number of functions that can be applied to DATE, TIME, and TIMESTAMP columns to help you. Chances are you will find a function to help you with your task at hand, depending on your specific needs. Consider the functions listed in Table 1.
Table 1. DB2 Date/Time Functions
Using date and time data correctly in DB2 can be a bit confusing, but the rewards of learning proper date and time usage are numerous. Do not continue to operate in the void. The wise DB2 professional will learn proper DB2 date and time usage and the vast support built into DB2 for manipulating date and time values.
And the sooner, the better!
That way you can let DB2 perform date and time formatting, integrity checking, and arithmetic… so you won’t have to!