8.9.8 TIMESTAMPADD

Add the date and time to the date and time specified for the target data in the unit specified for the date and time unit.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Rules
(4) Examples

(1) Specification format

scalar-function-TIMESTAMPADD::= TIMESTAMPADD(datetime-unit,add-value,target-data)
 
  datetime-unit::= {YEAR|QUARTER|MONTH|WEEK|DAY|DAYOFYEAR|HOUR|MINUTE
                 |SECOND|MILLISECOND|MICROSECOND|NANOSECOND|PICOSECOND}
  add-value::= value-expression
  target-data::= value-expression

To Page Top

(2) Explanation of specification format

datetime-unit:

Specifies the date and time unit added to the target data.

The following table shows the units that can be specified as date and time, and the range of numeric data values that can be specified as add values.

Table 8‒30: Scope of units that can be specified for date and time units and the range of numeric data values that can be specified for add values
Date and time unit specification	Description	Range of numeric data that can be specified as add value
YEAR	Year	-9,998 to 9,998
QUARTER	Quarter 1QUARTER is calculated as 3MONTH (3 months).	-39,995 to 39,995
MONTH	Month	-119,987 to 119,987
WEEK	Week 1WEEK is calculated as 7DAY (7 days).	-521,722 to 521,722
DAY	Day	-3,652,058 to 3,652,058
DAYOFYEAR	Ordinal days The same result is returned as when DAY is specified.	-3,652,058 to 3,652,058
HOUR	Hour	-87,649,415 to 87,649,415
MINUTE	Minute	-5,258,964,959 to 5,258,964,959
SECOND	Second	-315,537,897,599 to 315,537,897,599
MILLISECOND	Milliseconds (1/1,000 sec.) Assumed fractional seconds precision is 3.	-315,537,897,599,999 to 315,537,897,599,999
MICROSECOND	Microseconds (1/1,000,000 sec.) Assumed fractional seconds precision is 6.	-315,537,897,599,999,999 to 315,537,897,599,999,999
NANOSECOND	Nanosecond (1/1,000,000,000 sec.) Assumed fractional seconds precision is 9.	-9,223,372,036,854,775,807 to 9,223,372,036,854,775,807
PICOSECOND	Picoseconds (1/1,000,000,000,000 sec.) Assumed fractional seconds precision is 12.	-9,223,372,036,854,775,807 to 9,223,372,036,854,775,807

add-value:

Specifies the value added to the date and time of the target data.

The following specification rules apply:

Specify the add value in the form of a value expression. For details about value expressions, see 7.21 Value expression.
Specify the data type for the added value to either INTEGER, BIGINT, or SMALLINT.
To subtract the date and time of the target data, specify a negative value for the add value.

Example:
```
TIMESTAMPADD(DAY,-1,DATE'2020-06-20') → DATE'2020-06-19'
```
For details about the value range of numeric data that can be specified as add value, see Table 8‒30: Scope of units that can be specified for date and time units and the range of numeric data values that can be specified for add values.
If a dynamic parameter is specified by itself as the add value, the assumed data type of the dynamic parameter will be the BIGINT type^#.

#

If the integer data type format is a legacy format, it will be an INTEGER type.

target-data:

Specifies the target data to be added to the date and time.

The following specification rules apply:

Specify the target data in the form of a value expression. For details about value expressions, see 7.21 Value expression.
You cannot specify a dynamic parameter by itself for the target data.
The data type for the target data should be DATE, TIME, TIMESTAMP, CHAR, or VARCHAR. In the case of CHAR or VARCHAR, you must specify a character string literal that adheres to the format of the predefined input representation. For details about predefined input representations, see 6.3.3 Predefined character-string representations.

The following table shows the possible combinations of date and time units and target data.

Table 8‒31: Possible combinations of datetime units and target data
Date and time unit specification	Possible data type of target data
Date and time unit specification	Predefined input representation expressing DATE type or date	Predefined input representation expressing TIME type or hour	Predefined input representation expressing TIMESTAMP type or time stamp
YEAR	Y	N	Y
QUARTER	Y	N	Y
MONTH	Y	N	Y
WEEK	Y	N	Y
DAY	Y	N	Y
DAYOFYEAR	Y	N	Y
HOUR	N	Y	Y
MINUTE	N	Y	Y
SECOND	N	Y	Y
MILLISECOND	N	Y	Y
MICROSECOND	N	Y	Y
NANOSECOND	N	Y	Y
PICOSECOND	N	Y	Y

Legend:

Y: Can be specified.

N: Cannot be specified.

The following example illustrates the result of executing the scalar function TIMESTAMPADD.

Example 1:

TIMESTAMPADD(DAY,2,DATE'2020-03-01') → DATE'2020-03-03'

Example 2:

TIMESTAMPADD(MILLISECOND,1,TIMESTAMP'2019-12-31 23:59:59.999')
→ TIMESTAMP'2020-01-01 00:00:00.000'

To Page Top

(3) Rules

The result of the execution returns the value added to the target data in the unit of the specified date and time.
The data type of the execution result is shown in the following table.
- If the target data is of type DATE or the predefined input representation of a date (CHAR, VARCHAR), the resulting data type is DATE.
- If the target data is of type TIME or the predefined input representation of time (CHAR, VARCHAR), the resulting data type is TIME.
- If the target data is of type TIMESTAMP or the predefined input representation of a time stamp (CHAR, VARCHAR), the resulting data type is TIMESTAMP. Note that if the target data has a time zone, the resulting data will also has a time zone. If the target data does not have a time zone, the resulting will also not have a time zone.
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
If the target data or add value has a null value, the execution result will be a null value.
An error occurs if the resulting value does not fall within the range of values of type DATE, TIME, or TIMESTAMP. For details about the value scope for DATE, TIME, or TIMESTAMP, see (3) Datetime data in 6.2.1 List of data types.
If the result of adding or subtracting the year and month results in a date that does not exist (the 31st day of the smallest month, or February 29th in a non-leap year), it is corrected to the last day of the month.

(Example)
```
TIMESTAMPADD(YEAR,1,DATE'2020-02-29') → DATE'2021-02-28'
```
Since the year 2021 is not a leap year, the results of the run will be corrected as above.
If the result of adding or subtracting days exceeds the last day of the month or precedes the first day of the month (day 1), the year and month are carried to the next higher or lower digit.

(Example)
```
TIMESTAMPADD(DAY,1,DATE'2020-12-31') → DATE'2021-01-01'
TIMESTAMPADD(DAY,-1,DATE'2020-07-01') → DATE'2020-06-30'
```
If the fractional seconds precision of the target data differs from the fractional seconds precision of the time or time stamp to be added, it is adjusted to the higher precision (0 is added to the lower precision). For example, if the fractional seconds precision of the target data is 0 and the date/time unit is MILLISECOND, the fractional seconds precision of the target data is expanded to 3 and then the add value is added.
If the result of adding or subtracting the hour exceeds 23:59:59.9999999999 seconds or is before 00:00:00.00.00000000000000000000 seconds, the day is carried to the next higher or lower digit.

(Example)
```
TIMESTAMPADD(SECOND,1,TIMESTAMP'2020-02-01 23:59:59') → TIMESTAMP'2020-02-02 00:00:00'
TIMESTAMPADD(SECOND,-1,TIMESTAMP'2020-02-02 00:00:00') → TIMESTAMP'2020-02-01 23:59:59'
```

The scalar function TIMESTAMPADD is executed after being converted to the datetime operation shown in the following table.

Table 8‒32: Conversion rule from scalar function TIMESTAMPADD to datetime operation
Specification format of TIMESTAMPADD	The post-conversion datetime operation
TIMESTAMPADD(YEAR,`add-value`,`target-data`)	`target-data` + (`add-value`)YEAR
TIMESTAMPADD(QUARTER,`add-value`,`target-data`)	`target-data` + ((`add-value`)*3)MONTH
TIMESTAMPADD(MONTH,`add-value`,`target-data`)	`target-data` + (`add-value`)MONTH
TIMESTAMPADD(WEEK,`add-value`,`target-data`)	`target-data` + ((`add-value`)*7)DAY
TIMESTAMPADD(DAY,`add-value`,`target-data`)	`target-data` + (`add-value`)DAY
TIMESTAMPADD(DAYOFYEAR,`add-value`,`target-data`)	`target-data` + (`add-value`)DAY
TIMESTAMPADD(HOUR,`add-value`,`target-data`)	`target-data` + (`add-value`)HOUR
TIMESTAMPADD(MINUTE,`add-value`,`target-data`)	`target-data` + (`add-value`)MINUTE
TIMESTAMPADD(SECOND,`add-value`,`target-data`)	`target-data` + (`add-value`)SECOND
TIMESTAMPADD(MILLISECOND,`add-value`,`target-data`)	`target-data` + (`add-value`)MILLISECOND
TIMESTAMPADD(MICROSECOND,`add-value`,`target-data`)	`target-data` + (`add-value`)MICROSECOND
TIMESTAMPADD(NANOSECOND,`add-value`,`target-data`)	`target-data` + (`add-value`)NANOSECOND
TIMESTAMPADD(PICOSECOND,`add-value`,`target-data`)	`target-data` + (`add-value`)PICOSECOND

To Page Top

(4) Examples

Examples

Determine the date by adding 5 days to the target data (date) in column C2 of table T1.

SELECT "C1",TIMESTAMPADD(DAY,5,"C2") FROM "T1"

[Figure]

To Page Top