📋

Description

This error indicates that an attempt was made to store a value into a `TIMESTAMP` column that MySQL considers invalid. It typically occurs during `INSERT` or `UPDATE` operations when the data provided does not conform to the expected date and time format or falls outside the supported range for `TIMESTAMP`.

💬

Error Message

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

🔍

Known Causes

3 known causes

⚠️

Incorrect Date/Time String Format

The string literal provided for the `TIMESTAMP` column does not conform to MySQL's accepted date and time formats (e.g., 'YYYY-MM-DD HH:MM:SS').

⚠️

Value Outside TIMESTAMP Range

The date or time value supplied falls outside the valid range for MySQL's `TIMESTAMP` data type (e.g., before '1970-01-01 00:00:01' UTC or after '2038-01-19 03:14:07' UTC).

⚠️

Non-Existent Date Components

The date string contains invalid day or month values, such as '2023-02-30' or '2023-13-01', which do not represent a real date.

🛠️

Solutions

3 solutions available

1. Correct Invalid Timestamp Values easy

Identify and update rows with invalid timestamp formats.

1

Identify the problematic column and row number from the error message.

The error message will typically state: "Invalid TIMESTAMP value in column '%s' at row %ld". Replace '%s' with the column name and '%ld' with the row number.

2

Connect to your MySQL database using a client (e.g., MySQL Workbench, `mysql` command-line client).

mysql -u your_username -p your_database_name

3

Update the specific row with a valid timestamp. The format should typically be 'YYYY-MM-DD HH:MM:SS' or 'YYYY-MM-DD HH:MM:SS.ffffff'.

UPDATE your_table_name SET your_timestamp_column = 'YYYY-MM-DD HH:MM:SS' WHERE primary_key_column = row_id;

4

If the invalid value is due to a zero date (like '0000-00-00 00:00:00'), you might need to adjust the SQL mode. However, this is generally not recommended for production environments as it can mask underlying data issues.

SET sql_mode = 'NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

2. Handle Invalid Timestamps During Data Import medium

Clean or reject invalid timestamp data before importing.

1

Before importing data, inspect your source file (CSV, SQL dump, etc.) for invalid timestamp formats. Common issues include incorrect separators, missing components, or non-existent dates (e.g., February 30th).

Manually review a sample of your data or use scripting languages (Python, Perl) to pre-process the file.

2

If using `LOAD DATA INFILE` or `mysqlimport`, you can use `SET` clauses to transform or assign default values to invalid timestamps. This is often done by checking for specific invalid patterns.

LOAD DATA INFILE 'your_data.csv' INTO TABLE your_table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(column1, @timestamp_var, column3)
SET your_timestamp_column = STR_TO_DATE(@timestamp_var, '%Y-%m-%d %H:%i:%s') OR NULL;

3

Alternatively, you can preprocess the data to replace invalid timestamps with NULL or a default valid timestamp before the import.

Example using Python with pandas:
python
import pandas as pd

df = pd.read_csv('your_data.csv')

def clean_timestamp(ts):
    try:
        return pd.to_datetime(ts, errors='raise')
    except:
        return pd.NaT # or a default timestamp like pd.Timestamp('1970-01-01')

df['your_timestamp_column'] = df['your_timestamp_column'].apply(clean_timestamp)
df.to_csv('cleaned_data.csv', index=False)

3. Modify Table Schema to Allow NULL or Default Values medium

Allow NULLs or set a default for the timestamp column to avoid insertion errors.

1

Connect to your MySQL database.

mysql -u your_username -p your_database_name

2

Alter the table to allow NULL values for the timestamp column. This is a quick fix if NULL is an acceptable representation of an unknown or invalid timestamp.

ALTER TABLE your_table_name MODIFY your_timestamp_column TIMESTAMP NULL;

3

Alternatively, set a default value for the timestamp column. This will be used if no value is provided or if an invalid value is encountered during insertion/update.

ALTER TABLE your_table_name MODIFY your_timestamp_column TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

4

If you want a specific default value other than `CURRENT_TIMESTAMP`, you can specify it. Ensure the default value is a valid timestamp.

ALTER TABLE your_table_name MODIFY your_timestamp_column TIMESTAMP DEFAULT '2000-01-01 00:00:00';

🔗

Related Errors

5 related errors

MySQL Error 1299: Invalid Timestamp Value