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 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/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 type of the first parameter is DATE/DATETIME, then the return type is DATETIME.
• 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