📋

Description

This error indicates that a value being inserted or updated into a `TIMESTAMP` column does not conform to a valid timestamp format or range. It typically occurs during `INSERT` or `UPDATE` operations when the data provided for a `TIMESTAMP` column is malformed, out of bounds, or incompatible with the column's definition.

💬

Error Message

Invalid TIMESTAMP value in column '%s' at row %ld

🔍

Known Causes

4 known causes

⚠️

Incorrect Date/Time Format

The string literal used for the `TIMESTAMP` column does not match any of the recognized date and time formats by MariaDB.

⚠️

Out-of-Range Value

The provided timestamp value falls outside the valid range for MariaDB `TIMESTAMP` data types (e.g., too early or too late a date).

⚠️

Null Value for NOT NULL Column

Attempting to insert a `NULL` value into a `TIMESTAMP` column that is defined as `NOT NULL` and does not have a `DEFAULT` value.

⚠️

Implicit Conversion Failure

MariaDB failed to implicitly convert a non-timestamp value (like an incompatible string or number) into a `TIMESTAMP` type.

🛠️

Solutions

3 solutions available

1. Identify and Correct Invalid Timestamp Values medium

Find and fix the specific invalid timestamp data causing the error.

1

Identify the table and column causing the error. The error message usually provides this information. If not, you may need to examine recent data modifications or perform a more general search.

2

Use a `SELECT` query to find rows with invalid timestamp values. MariaDB often represents invalid timestamps as '0000-00-00 00:00:00' or similar. The exact representation might depend on the `sql_mode`.

SELECT * FROM your_table WHERE your_timestamp_column IN ('0000-00-00 00:00:00', '0000-00-00', '00:00:00');

3

Update the identified invalid timestamp values to a valid date and time. This could be the current timestamp, a default value, or a specific date depending on your application's logic.

UPDATE your_table SET your_timestamp_column = NOW() WHERE your_timestamp_column IN ('0000-00-00 00:00:00', '0000-00-00', '00:00:00');

4

If you are inserting data and encountering this error, ensure your application logic prevents inserting invalid timestamp formats. Log or handle these cases gracefully.

2. Adjust `sql_mode` to Tolerate Invalid Timestamps easy

Temporarily or permanently modify `sql_mode` to allow '0000-00-00 00:00:00' timestamps.

1

Check the current `sql_mode` setting.

SHOW VARIABLES LIKE 'sql_mode';

2

To temporarily allow '0000-00-00 00:00:00' timestamps for the current session, set the `sql_mode` and remove `NO_ZERO_DATE` and `NO_ZERO_IN_DATE` if present. Be cautious with this approach as it can mask other data integrity issues.

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- Adjust to your existing sql_mode minus NO_ZERO_DATE/NO_ZERO_IN_DATE

3

To permanently change the `sql_mode` (requires root privileges and server restart), edit your MariaDB configuration file (e.g., `my.cnf` or `my.ini`). Find or add the `[mysqld]` section and modify the `sql_mode` parameter. Remove `NO_ZERO_DATE` and `NO_ZERO_IN_DATE`.

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

4

After modifying the configuration file, restart the MariaDB server for the changes to take effect.

sudo systemctl restart mariadb  # Or equivalent command for your OS

3. Re-create Table with `NO_ZERO_DATE` Disabled advanced

Create a new table with a compatible `sql_mode` and migrate data.

1

Temporarily set the `sql_mode` to allow zero dates for table creation.

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- Ensure NO_ZERO_DATE and NO_ZERO_IN_DATE are absent

2

Create a new table with the same schema as the problematic table, ensuring that `NO_ZERO_DATE` and `NO_ZERO_IN_DATE` are not part of the session's `sql_mode` during creation.

CREATE TABLE new_your_table LIKE your_table;

3

Insert data from the old table to the new table. This process will automatically handle the '0000-00-00 00:00:00' values as valid (if `NO_ZERO_DATE` is not enforced).

INSERT INTO new_your_table SELECT * FROM your_table;

4

Rename the tables to replace the old one with the new one. It's highly recommended to back up your data before performing this operation.

RENAME TABLE your_table TO old_your_table, new_your_table TO your_table;

5

Once confirmed, you can drop the old table.

DROP TABLE old_your_table;

🔗

Related Errors

5 related errors

MariaDB Error 1299: Invalid Timestamp Column Value