HOURS_ADD

Description

The HOURS_ADD function adds or subtracts a specified number of hours to/from an input date or datetime value and returns the calculated new datetime. This function supports both DATE, DATETIME, TIMESTAMP_NS, and TIMESTAMPTZ input types. If the input is DATE type (containing only year, month, day), it defaults the time part to 00:00:00 before adding hours.

This function is consistent with the date_add function and date_add function in MySQL when using the HOUR unit.

Syntax

HOURS_ADD(`<date_or_time_expr>`, `<hours>`)

Parameters

Parameter	Description
<date_or_time_expr>	A valid date expression that supports DATE/DATETIME/TIMESTAMP_NS/TIMESTAMPTZ types. For specific formats, please refer to timestamptz conversion, datetime conversion and date conversion
<hours>	The number of hours to add, of integer (INT) type. Can be positive or negative: positive number adds the specified hours, negative number subtracts the specified hours (equivalent to subtracting hours)

Return Value

Return the base time <date_or_time_expr> plus the specified hours <hours>, the return value type is determined by the type of the first parameter:

• If the first parameter is DATE or DATETIME, the return type is DATETIME; if it is TIMESTAMP_NS, the return type is TIMESTAMP_NS.
• If the type of the first parameter is TIMESTAMPTZ, then the return type is TIMESTAMPTZ.

Special cases:

• If the calculation result exceeds the valid range of DATETIME type [0000-01-01 00:00:01, 9999-12-31 23:59:59], returns an error.
• Return NULL if any parameters is NULL.

Examples

-- Add hours to datetime type
SELECT HOURS_ADD('2020-02-02 02:02:02', 1);
+------------------------------------------------------------+
| hours_add(cast('2020-02-02 02:02:02' as DATETIMEV2(0)), 1) |
+------------------------------------------------------------+
| 2020-02-02 03:02:02                                        |
+------------------------------------------------------------+

-- Add hours to date type (default time is 00:00:00)
SELECT HOURS_ADD('2020-02-02', 51);
+-----------------------------+
| HOURS_ADD('2020-02-02', 51) |
+-----------------------------+
| 2020-02-04 03:00:00         |
+-----------------------------+

-- Add negative hours (i.e., subtract hours)
select hours_add('2023-10-01 10:00:00', -3) ;
+--------------------------------------+
| hours_add('2023-10-01 10:00:00', -3) |
+--------------------------------------+
| 2023-10-01 07:00:00                  |
+--------------------------------------+

-- Example of TimeStampTz type, SET time_zone = '+08:00'
SELECT HOURS_ADD('2025-10-10 11:22:33.123+07:00', 1);
+-----------------------------------------------+
| HOURS_ADD('2025-10-10 11:22:33.123+07:00', 1) |
+-----------------------------------------------+
| 2025-10-10 13:22:33.123+08:00                 |
+-----------------------------------------------+

-- Input parameter is NULL, return NULL
select hours_add(null, 5) ;
+--------------------+
| hours_add(null, 5) |
+--------------------+
| NULL               |
+--------------------+

select hours_add('2023-10-01 10:00:00',NULL) ;
+---------------------------------------+
| hours_add('2023-10-01 10:00:00',NULL) |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+

-- Exceeds datetime range
select hours_add('9999-12-31 23:59:59', 2);
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.2)[E-218]Operation hours_add of 9999-12-31 23:59:59, 2 out of range

mysql> select hours_add('0000-01-01',-2);
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.2)[E-218]Operation hours_add of 0000-01-01 00:00:00, -2 out of range