Db2 DATE and TIME Arithmetic

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!

More on DB2 Date and Time Data: Arithmetic Expressions

DB2 allows you to add and subtract DATE, TIME, and TIMESTAMP columns. In addition, you can add date and time durations to, or subtract them from, date and time columns. But use date and time arithmetic with care. If you do not understand the capabilities and features of date and time arithmetic, you will likely encounter some 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 following two date arithmetic statements are not equivalent:

    1997/04/03 - 1 MONTH

  1997/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 1997/03/03, but the result of the second statement is 1997/03/04. In general, use like durations (for example, use months or use days, but not both) when you issue date arithmetic.

Another consideration: 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.

Now, what exactly is in that field returned as the result of a date or time calculation? Simply stated, it is a duration. There are three types of durations: date durations, time durations, and labeled durations.

Date durations are expressed as a DECIMAL(8,0) number. The result of subtracting one DATE value from another is a date duration. To be properly interpreted, the number must have the format yyyymmdd, where yyyy represents the number of years, mm the number of months, and dd the number of days.

Time durations are expressed as a DECIMAL(6,0) number. To be properly interpreted, the number must have the format hhmmss, where hh represents the number of hours, mm the number of minutes, and ss the number of seconds. The result of subtracting one TIME value from another is a time duration.

Labeled durations represent a specific unit of time as expressed by a number followed by one of the seven duration keywords: 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. For example:

CURRENT DATE + 3 YEARS + 6 MONTHS 

This will add three and a half years to the current date.

On Date Formats, Part 2

Here is a follow-up question and answer based on my previous blog post:

Q: My format does not fit into any of the formats listed in the DB2 manuals. What if I have a DATE stored like YYYYMMDD (with no dashes or slashes) and I want to compare it to a DB2 date?

A: Okay, let's look at one potential solution to your problem (and then I want to briefly talk about the use of proper data types). First of all you indicate that your date column contains dates in the following format: yyyymmdd with no dashes or slashes. You do not indicate whether this field is a numeric or character field - I will assume that it is character. If it is not, you can use the CHAR function to convert it to a character string.

Then, you can use the SUBSTR function to break the character column apart 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 can concatenate all of these together 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 may not perform extremely well, but it should return the results you desire.

Now, a quick word about using proper data types. I say this all of the time, but there are many applications and implementations "out there" that do not heed the advice: it is wise to use the DATE data type when you store dates in DB2 tables. It simplifies life later on when you want to do things like formatting dates and performing date arithmetic.

Using the appropriate data type also ensures that DB2 will perform the proper integrity checks on the columns when data is entered, instead of requiring application logic to ensure that valid dates are entered.

On Date Formats

Regular readers of my blog know that from time to time I use the blog as a forum to answer questions I get via e-mail. Today, we address a popular theme - dealing with DB2 date data...

Q:I have a DATE column in a DB2 table, but I do not want it to display the way DB2 displays it by default. How can I get a date format retrieved from a column in a table from DB2 database in the format MM/DD/YYYY?

A:The simplest way to return a date in the format you desire is to use the built-in column function CHAR. Using this function you can convert a date column into any number of formats. The specific format you request, MM/DD/YYYY, is the USA date format. So, for example, to return the date in the format you requested for a column named START_DATE you would code the function as follows:

CHAR(START_DATE,USA)

The first argument is the column name and the second argument is the format. Consult the following table for a list of the date formats that are supported by DB2.

Name	Layout	Example
ISO	yyyy-mm-dd	2002-10-22
USA	mm/dd/yyyy	10/22/2002
EUR	dd.mm.yyyy	22.10.2002
JIS	yyyy-mm-dd	2002-10-22
LOCAL	Locally defined layout	N/A

You may also have an installation-defined date format that would be named LOCAL. For LOCAL, the date exit for ASCII data is DSNXVDTA, the date exit for EBCDIC is DSNXVDTX, and the date exit for Unicode is DSNXVDTU.

Of course, this is a simple date question... I will follow-up with some additional date-related questions and answers in my next couple of blog posts.