Adding 1 to a Date Gives the Next Day on PostgreSQL and a Number on MySQL and SQLite

due_date + 1 reads like the day after, and on PostgreSQL that is what it gives. On MySQL, MariaDB and SQLite the answer is a plain number.

MySQL and MariaDB turn the date into the number 20240131 and add 1. SQLite turns the text into the number 2024. PostgreSQL adds a day. Subtracting two dates goes the same way: PostgreSQL counts days and MySQL and MariaDB subtract the numbers. INTERVAL arithmetic works on PostgreSQL, MySQL and MariaDB, with different result types, and SQLite has no INTERVAL. PostgreSQL’s usual INTERVAL '1 day', with the unit inside the quotes, works on PostgreSQL only.

SELECT CAST('2024-01-31' AS DATE) + 1, run on four engines: SQLite 3.45.1 2025; PostgreSQL 16.15 2024-02-01; MySQL 8.0.46 20240132; MariaDB 10.11.14 20240132.

What Each Engine Answered

The example

SELECT CAST('2024-01-31' AS DATE) + 1;

gave these answers:

Engine	Answer
SQLite 3.45.1	`2025`
PostgreSQL 16.15	`2024-02-01`
MySQL 8.0.46	`20240132`
MariaDB 10.11.14	`20240132`

Every Form BareSQL Checks

BareSQL Portable checks 3 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

Form	SQLite	PostgreSQL	MySQL	MariaDB
`date + number`	`2025`	`2024-02-01`	`20240132`	`20240132`
`INTERVAL`	no	`2024-02-01 00:00:00`	`2024-02-01`	`2024-02-01`
`INTERVAL 'n unit'`	no	yes	no	no

What to Write Instead

No date arithmetic reads the same on all four engines. Use date(d, '+1 day') on SQLite and d + INTERVAL '1' DAY on the other three, or do the arithmetic in the application.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:8: warning: SQLite has no date type: CAST('2024-05-06' AS DATE) is the number 2024 there and a date on the other engines. [cast-to-date]
    CAST('2024-01-31' AS DATE)
    DATE(x) gives a date on PostgreSQL, MySQL and MariaDB and ISO text (2024-05-06) on SQLite, which sorts and compares the same way.
example.sql:1:8: warning: A date plus 1 is the next day on PostgreSQL, the number 20240132 on MySQL and MariaDB, and 2025 on SQLite. [date-arithmetic]
    CAST('2024-01-31' AS DATE) + 1
    No date arithmetic reads the same on all four engines. Use date(d, '+1 day') on SQLite and d + INTERVAL '1' DAY on the other three, or do the arithmetic in the application.

2 problems: 2 warnings.

The example also runs into CAST to a date, which has a post of its own.

baresql rules date-arithmetic prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.