Datetime arithmetic in HCOPG

The only arithmetic operations that can be performed on datetime values are addition and subtraction.

Addition operator

If a datetime value is the operand of addition, the other operand must be a labeled duration.

The specific HCOPG rules governing the use of the addition operator with datetime values are:

First operand type Other operand must be
date A labeled date duration of years, months, or days.
time A labeled time duration of hours, minutes, or seconds.
timestamp A labeled date, time or timestamp duration. Any type of labeled duration is valid.

The result of HCOPG datetime addition is always a date, time or timestamp.

Note: Date, time or timestamp durations cannot be used to increment date, time or timestamp columns. Instead, use corresponding labeled durations as cited above.

Subtraction operator

The HCOPG rules for the use of the subtraction operator on datetime values are not the same as those for addition because a datetime value cannot be subtracted from a duration, and because the operation of subtracting two datetime values is not the same as the operation of subtracting a duration from a datetime value.

The general rules governing the use of the subtraction operator with datetime values are:

Operand1 - Operand2 Result
date - date date duration - decimal(8.0) formatted 'yyyymmdd'
date - labeled date duration date
time - time time duration - decimal(6.0) formatted 'hhmmss'
time - labeled time duration time
timestamp - timestamp timestamp duration - decimal(20,6) formatted 'yyyymmddhhmmss.nnnnnn'
timestamp - any labeled duration timestamp
Specific additional HCOPG date subtraction rules:
  • If the first operand is a date, the second operand must be a labeled duration of years, months, days or a DATE(date or string representation of a date) expression.
  • If the second operand is a date, the first operand must be a DATE(date or string representation of a date) expression.
  • If either operand is CURRENT_DATE, DATE function is not required for other operand.
Specific additional HCOPG time subtraction rules:
  • If the first operand is a time, the second operand must be a labeled duration of hours, minutes, seconds or a TIME(time or string representation of a time) expression.
  • If the second operand is a time, the first operand must be a TIME(time or string representation of a time) expression.
  • If either operand is CURRENT_TIME, TIME function is not required for other operand.
Specific additional HCOPG timestamp subtraction rules:
  • If the first operand is a timestamp, the second operand must be any valid labeled duration or a TIMESTAMP(timestamp or string representation of a timestamp) expression.
  • If the second operand is a timestamp, the first operand must be a TIMESTAMP(timestamp or string representation of a timestamp) expression.
  • If either operand is CURRENT_TIMESTAMP, TIMESTAMP function is not required for other operand.
  • The precision of TIMESTAMP is set to be the same as the PostgreSQL timestamp(6) data type.
Note: Date, time or timestamp durations cannot be used to decrement date, time or timestamp columns. Instead, use corresponding labeled durations. HCOPG does support one special case, however - the expression (CURRENT_TIMESTAMP - CURRENT_TIMEZONE).

Examples

Valid z/OS DB2 expressions Required HCOPG expressions
COL_TS - COL_TS COL_TS - TIMESTAMP(COL_TS)
COL_DT - '1900-01-01' COL_DT - DATE('1900-01-01')
'12.12.12' - COL_TM TIME('12.12.12') - TIME(COL_TM)
Acceptable HCOPG expressions
CURRENT_TIMESTAMP - COL_TS
CURRENT_DATE - '1900-01-01'
COL_TM - CURRENT_TIME
CURRENT_TIMESTAMP - CURRENT_TIMEZONE