Db2 – Dev-Notes

To do so, we can utilize the timestampdiff() function provided by DB2. This function takes in two parameters. The first parameter is a numeric value indicating the unit in which you wish to receive the results in; the values must be one of the following.

1 : Fractions of a second
2 : Seconds
4 : Minutes
8 : Hours
16 : Days
32 : Weeks
64 : Months
128 : Quarters of a year
256 : Years

The second parameter should contain the subtraction formula between two timestamps, with the results converted to character format. Below is one example of usage.

select 
timestampdiff(
  16, 
  char(timestamp('2010-01-11-15.01.33.453312') - current timestamp))  
from sysibm.sysdummy1;

The result from this statement is 210 at the time of the writing. Notice that even though the first timestamp is set to be prior than the current timestamp, the outcome is still positive — This function returns the absolute value (ie. always positive) reflecting the difference in time between two timestamps. Also, take note that the result will always be an integer, thus it can only be considered an estimation of the date/time difference rather than an exact one.

In Oracle, the dummy table is “dual”. It is present in all Oracle database installations, thus no special setup is needed to use it. Below are some samples on how to use this table.

-- Getting the current system date:

PROMPT> select sysdate from dual;

SYSDATE
---------
06-AUG-10

-- Getting the current logged-in user:

PROMPT> select user from dual;

USER
------------------------------
YTCDBA

-- Display the result of some string manipulation

PROMPT> select substr('hello world',1,7) from dual;

SUBSTR(
-------
hello w

The DB2 equivalent of Oracle’s “dual” is “sysibm.sysdummy1”. Below is a sample of the usage.

-- Getting the current system date:

PROMPT> select current date from sysibm.sysdummy1;

00001
----------
2010-08-06

-- Getting the current logged-in user:

PROMPT> select current user from sysibm.sysdummy1;

00001
-----
USER1

-- Display the result of some string manipulation

PROMPT> select substr('hello world',1,7) from sysibm.sysdummy1;

00001
-------
hello w

It is also worthy of note that MySQL and SQL Server can select pseudocolumn data without the use of a dummy table. For example, in SQL Server, you can get the current date by running “select getdate();” without a “from” clause in your SQL statement.

Category: Db2

Calculating the difference between two dates or times in DB2

Using dummy table to select pseudocolumns