DATE_ADD
Adds a specified time interval to a DATE or TIMESTAMP value.
Syntax
DATE_ADD(<unit>, <interval>, <date_or_time_expr>)
Return Type
DATE or TIMESTAMP (depending on the type of <date_or_time_expr>).
Examples
This example adds different time intervals (year, quarter, month, week, and day) to the current date:
SELECT
TODAY(),
DATE_ADD(YEAR, 1, TODAY()),
DATE_ADD(QUARTER, 1, TODAY()),
DATE_ADD(MONTH, 1, TODAY()),
DATE_ADD(WEEK, 1, TODAY()),
DATE_ADD(DAY, 1, TODAY());
-[ RECORD 1 ]-----------------------------------
today(): 2024-10-10
DATE_ADD(YEAR, 1, today()): 2025-10-10
DATE_ADD(QUARTER, 1, today()): 2025-01-10
DATE_ADD(MONTH, 1, today()): 2024-11-10
DATE_ADD(WEEK, 1, today()): 2024-10-17
DATE_ADD(DAY, 1, today()): 2024-10-11
This example adds different time intervals (hour, minute, and second) to the current timestamp:
SELECT
NOW(),
DATE_ADD(HOUR, 1, NOW()),
DATE_ADD(MINUTE, 1, NOW()),
DATE_ADD(SECOND, 1, NOW());
-[ RECORD 1 ]-----------------------------------
now(): 2024-10-10 01:35:33.601312
DATE_ADD(HOUR, 1, now()): 2024-10-10 02:35:33.601312
DATE_ADD(MINUTE, 1, now()): 2024-10-10 01:36:33.601312
DATE_ADD(SECOND, 1, now()): 2024-10-10 01:35:34.601312
- When unit is MONTH, If date is the last day of the month or if the resulting month has fewer days than the day component of date,
- then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.
When adding a month to a date that would result in an invalid date (e.g., January 31 → February 31), it returns the last valid day of the resulting month:
SELECT DATE_ADD(month, 1, '2023-01-31'::DATE) ;
╭────────────────────────────────────────╮
│ DATE_ADD(MONTH, 1, '2023-01-31'::DATE) │
│ Date │
├────────────────────────────────────────┤
│ 2023-02-28 │
╰────────────────────────────────────────╯
When adding a month to a date where the resulting month has sufficient days, it performs simple month arithmetic:
SELECT DATE_ADD(month, 1, '2023-02-28'::DATE);
╭────────────────────────────────────────╮
│ DATE_ADD(MONTH, 1, '2023-02-28'::DATE) │
│ Date │
├────────────────────────────────────────┤
│ 2023-03-28 │
╰────────────────────────────────────────╯