Db/2 – Date Subtraction

Background

Needing to find the days since the last database backup and so sought out date differential functions.

Outline

  1. timestampdiff
  2. day
  3. julian_day

Code

Functions

timestampdiff

Outline

The timestampdiff expects an interval and the difference between two dates.

Here is the first parameter:

Numeric Expression Interval
1 Microseconds
2 Seconds
4 Minutes
8 Hours
16 Days
64 Months
128 Quarters
256 Years

Syntax


timestampdiff
(
   id
   , tsGreater
        - tsLess
)

Sample


SELECT
         INT
          (
                timestampdiff
                (
                      16
                    , CURRENT TIMESTAMP
                        - '2018-01-01'
                )

          ) As "# of Days since 2018-01-01"

FROM  SYSIBM.SYSDUMMY1

day

Sample

SELECT
         INT
          (
                day
                (CURRENT TIMESTAMP)
                -
                day
                ('2018-01-01')

          ) As "# of Days since 2018-01-01"

FROM  SYSIBM.SYSDUMMY1

julian_day

Sample

SELECT
         INT
          (
                julian_day
                (CURRENT TIMESTAMP)
                -
                julian_day
                ('2018-01-01')

          ) As "# of Days since 2018-01-01"

FROM  SYSIBM.SYSDUMMY1

Overall

 sysibmadm.snapdb

Sample


SELECT 

            tbl.DB_NAME AS "db"

          , to_char
            (
                  tbl.LAST_BACKUP
                , 'YYYY-MM-DD HH24:MI:SS PM'
            )
            AS "tsBackup"          

          , to_char
            (
                  CURRENT TIMESTAMP
                , 'YYYY-MM-DD HH24:MI:SS PM'
            )
            AS "tsNow"

          , INT
            (
                timestampdiff
                (
                      16
                    , CURRENT TIMESTAMP
                        - tbl.LAST_BACKUP
                )

            )
            AS "tsBackupDaysUsingtimestampdiff"

          , INT
            (
                JULIAN_DAY(CURRENT TIMESTAMP)
                    - JULIAN_DAY(tbl.LAST_BACKUP)
            )
            AS "tsBackupDaysUsingJulianDays"

          , INT
            (
                DAYS(CURRENT TIMESTAMP)
                  - DAYS(tbl.LAST_BACKUP) / 1
            )
            AS "tsBackupDaysUsingDays"

FROM   sysibmadm.snapdb tbl

References

 

  1. IBM
    • IBM Knowledge Center
      • Home > Db2 for z/OS 10.0.0 > DB2 SQL > Built-in functions > Scalar functions
        • TIMESTAMPDIFF

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s