After turning the last page on the 2021 calendar to embark on another year, I thought it might be a good time to reflect on date and time data... and how it can be used in Db2 for z/OS.
One of the most important database design decisions
you will make – for Db2 or, indeed, 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.
Perhaps the most important design choice is
to use the date and 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, such 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). In 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 numerous 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:
SELECT
DATE ('03/01/2004') - '12/01/2003'
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:
SELECT
DAYS('03/01/2004') - DAYS('12/01/2003')
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.
The functions provided by Db2 that can be
applied to DATE, TIME, and TIMESTAMP columns can greatly assist your development efforts when using date/time data. 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 the following table.
DB2
Date/Time Functions
|
CHAR
|
Converts
a Db2 date, time, timestamp, ROWID, floating point, integer, or decimal value
to a character value.
|
|
DATE
|
Converts
a value representing a date to a Db2 date. The value to be converted can be a
Db2 timestamp, a Db2 date, a positive integer, or a character string.
|
|
DAY
|
Returns
the day portion of a Db2 date or timestamp.
|
|
DAYOFMONTH
|
Similar
to DAY except DAYOFMONTH cannot accept a date duration or time duration as an
argument.
|
|
DAYOFWEEK
|
Converts
a date, timestamp, or string representation of a date or timestamp into an
integer that represents the day of the week. The value 1 represents Sunday, 2
Monday, 3 Tuesday, 4 Wednesday, 5 Thursday, and so on.
|
|
DAYOFYEAR
|
Converts
a date, timestamp, or string representation of a date or timestamp into an
integer that represents the day within the year. The value 1 represents
January 1st, 2 January 2nd, and so on.
|
|
DAYS
|
Converts
a Db2 date or timestamp into an integer value representing one more than the
number of days since January 1, 0001.
|
|
EXTRACT
|
Returns
a portion of a date or timestamp; you can use EXTRACT to slice up a date/time
value into its component pieces.
|
|
HOUR
|
Returns
the hour portion of a time, a timestamp, or a duration.
|
|
JULIAN_DAY
|
Converts
a Db2 date or timestamp, or character representation of a date or timestamp,
into an integer value representing the number of days from January 1, 4712 BC
to the date specified in the argument.
|
|
MICROSECOND
|
Returns
the microsecond component of a timestamp or the character representation of a
timestamp.
|
|
MIDNIGHT_SECONDS
|
Returns
the number of seconds since midnight for the specified argument which must be
a time, timestamp, or character representation of a time or timestamp.
|
|
MINUTE
|
Returns
the minute portion of a time, a timestamp, a character representation of a
time or timestamp, or a duration.
|
|
MONTH
|
Returns
the month portion of a date, a timestamp, a character representation of a
date or timestamp, or a duration.
|
|
MONTHS_BETWEEN
|
Returns
an estimate of the number of months between two expressions.
|
|
QUARTER
|
Converts
a date, timestamp, or string representation of a date or timestamp into an
integer that represents the quarter within the year. The value 1 represents
first quarter, 2 second quarter, 3 third quarter, and 4 fourth quarter.
|
|
SECOND
|
Returns
the seconds portion of a time, a timestamp, a character representation of a
time or timestamp, or a duration.
|
|
TIME
|
Converts
a value representing a valid time to a Db2 time. The value to be converted
can be a Db2 timestamp, a Db2 time, or a character string.
|
|
TIMESTAMP
|
Obtains
a timestamp from another timestamp, a valid character-string representation
of a timestamp, or a combination of date and time values (without a time
zone).
|
|
TIMESTAMPADD
|
Adds an
interval to a timestamp.
|
|
TIMESTAMPDIFF
|
Subtracts
two timestamps and returns an interval.
|
|
TIMESTAMP_FORMAT
|
Changes
the display format for a timestamp value.
|
|
TIMESTAMP_TZ
|
Returns
a timestamp with time zone value.
|
|
WEEK
|
Converts
a date, timestamp, or string representation of a date or timestamp into an
integer that represents the week within the year (with Sunday as the first
day of the week). The value 1 represents the first week of the year, 2 the
second week, and so on.
|
|
WEEK_ISO
|
Converts
a date, timestamp, or string representation of a date or timestamp into an
integer that represents the week within the year (with Monday as the first
day of the week).
|
|
YEAR
|
Returns
the year portion of a date, a timestamp, or a duration.
|
A Caveat
Keep in mind that this post addresses date and time data in Db2 for z/OS. Yes, other RDBMS products also support date and time data, but there will be differences and nuances that you will need to understand for each. Here is a nice blog post that discusses working with dates on Oracle, SQL Server, PostgreSQL, and MariaDB if you are interested.
Summary
Using date and time data correctly can be a
bit confusing, but the rewards of learning how to use Db2 to help you properly use date and time data 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!
