How to insert moment JS time into MySQL

How to insert moment JS time into MySQL

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Fixing MySQL Timestamp Errors in Node.js Applications

When working with Node.js and Moment.js, it is common to normalize date strings before persisting them to a database. A frequent stumbling block occurs when trying to store a timestamp formatted by Moment as toISOString() into a MySQL column defined as DATETIME. If you attempt to insert a value like '2014-09-24T04:09:00.000Z' directly into such a column, MySQL will reject the operation with an ER_TRUNCATED_WRONG_VALUE error.

The root cause lies in how MySQL's DATETIME type handles UTC offsets and fractional seconds compared to the ISO 8601 string format generated by Moment.js. The string '2014-09-24T04:09:00.000Z' explicitly includes a 'Z' suffix, which denotes "Zulu time" or UTC. While MySQL supports UTC timestamps in TIMESTAMP columns with explicit timezone information, standard DATETIME columns expect a specific format that does not inherently handle the 'Z' offset or the trailing milliseconds in the same way the driver interprets them during direct string insertion.

Understanding the Data Mismatch

The error message Incorrect datetime value: '2014-09-24T04:09:00.000Z' indicates that MySQL cannot parse the input string into its internal binary representation for a DATETIME column. This happens because the driver attempts to pass the raw string from your JavaScript application to the database engine without interpreting it as a date object first.

In the provided example, the code snippet uses Moment.js:

myDate = moment(data.myTime.format('YYYY/MM/DD HH:MM:SS')).toISOString();
// Result: '2014-09-24T04:09:00.000Z'

The resulting string is valid ISO 8601, but MySQL's DATETIME type expects the format YYYY-MM-DD HH:MM:SS. Furthermore, MySQL DATETIME columns do not store timezone information; they store absolute wall-clock time. When the 'Z' is present, MySQL tries to parse it as part of the datetime literal and fails because that syntax is reserved for TIMESTAMP types with specific driver configurations or when using standard SQL string literals without the 'Z'.

The Correct Insertion Strategy

To resolve this error, you must ensure the data being sent to the database matches the expected format of the target column. There are two primary approaches depending on your application requirements.

Option 1: Format for DATETIME

If your dateColumn is strictly a DATETIME type and you do not need to preserve UTC offset information in the database, strip the timezone indicator and the milliseconds before insertion. You should format the moment object back into the specific string MySQL expects.

const targetFormat = 'YYYY-MM-DD HH:mm:ss';
// Ensure the original data is formatted correctly before passing to Moment or directly
const cleanDate = moment(data.myTime).format(targetFormat);

// Insert INTO (dateColumn) VALUES (:cleanDate)
// Example using a raw query for clarity:
// INSERT INTO table_name (dateColumn) VALUES ('2014-09-24 04:09:00');

By removing the 'Z' and normalizing the separator from '/' to '-', you align the string with MySQL's expectations. Note that if your original data.myTime was already in a different format, ensure Moment parses it correctly before formatting it for storage.

Option 2: Use TIMESTAMP with Timezone Handling

If you require the database to store UTC times and handle timezone conversions automatically, consider changing the column type from DATETIME to TIMESTAMP. However, even then, passing a raw ISO string with 'Z' via a prepared statement often requires the MySQL driver (like mysql2 or mariadb) to interpret it as a date object.

If you are using an ORM or query builder like Sequelize or Knex, they often handle this conversion automatically if you pass a Moment.js object or a native JavaScript Date object instead of a string. If you must use a raw string, ensure the 'Z' is removed or the column type is adjusted to support ISO strings with timezone offsets.

Implementation Trade-offs

The decision between using DATETIME and TIMESTAMP impacts how your application handles timezones. DATETIME stores time as-is without timezone context, which can lead to issues if your application logic assumes UTC storage but inserts local times. TIMESTAMP adjusts for the server's timezone or uses UTC depending on configuration, but it still struggles with raw ISO strings containing 'Z' unless explicitly handled by the driver.

In modern stacks, it is often safer to let the Node.js layer handle the formatting and pass a native JavaScript Date object or a Moment instance directly to the query builder. Most MySQL drivers will then convert these objects into the correct binary representation internally, bypassing string parsing errors entirely.

For example, if using a raw query library that accepts Moment instances:

const moment = require('moment');
const dateObj = moment(data.myTime);
// Pass the object, not the ISO string
INSERT INTO table_name (dateColumn) VALUES (?)

This approach avoids the ER_TRUNCATED_WRONG_VALUE error by delegating the parsing logic to the driver rather than the database engine. Always verify your column definitions and ensure that the data format matches the semantic intent of the column type before executing the insert.

Our network