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
|