Convert JS date time to MySQL datetime

javascript

date-conversion

mysql-format

timezone-management

byAnton Shumikhin·Dec 20, 2024

// Convert JS Date to MySQL datetime: 'YYYY-MM-DD HH:MM:SS'
// 'Cause nothing says "party" like date formatting
const toMysqlFormat = (date) => date.toISOString().split('T').join(' ').slice(0, 19);

console.log(toMysqlFormat(new Date())); // Example output like some future date: '2023-04-01 12:34:56'

This nifty one-liner swiftly transforms a Date object into a MySQL datetime.

Comprehending date-time conversion

Let's deeply understand the intricacies of converting JavaScript dates to MySQL datetime format.

JavaScript `toISOString()` and MySQL format

We start with JS' toISOString() returning dates in ISO 8601 format, which we need to fit into MySQL’s 'YYYY-MM-DD HH:MM:SS' mold.

Accounting for timezones

toISOString() presents time in UTC, but what if you're a local time lover? Consider the timezone offset:

const toLocalMysqlFormat = (date) => {
  const offset = date.getTimezoneOffset() * 60000;
  return new Date(date - offset).toISOString().split('T').join(' ').slice(0, 19);
};

Adding minutes: More time, more fun

Sometimes we need extra time, like a few bonus minutes on a parking meter:

const addMinutes = (date, minutes) => new Date(date.getTime() + minutes * 60000);

Integrate it with toMysqlFormat, and voilà - MySQL datetime with added minutes!

Timezones and libraries: BFFs

For detailed timezone management, moment.js is your best buddy:

const moment = require('moment-timezone');
const date = moment(new Date()).tz('America/New_York').format('YYYY-MM-DD HH:mm:ss');

If you're into minimalism, fecha is a solid lightweight alternative:

const fecha = require('fecha');
const date = fecha.format(new Date(), 'YYYY-MM-DD HH:mm:ss');

With the above libraries, you can format dates and control timezones like a pro!

Advanced conversions: Be a Pro Formatter

Speed isn't everything - accuracy matters, too. Let's dive deep into the MySQL formatting rules:

Digit Padding: Lead ’em with zeros

Ensure each datetime component is two digits long:

// Padding is not always comfy, but here it helps
const pad = (number) => (number < 10 ? '0' + number : number);

const toMysqlFormatPadded = (date) => 
  `${date.getUTCFullYear()}-${pad(date.getUTCMonth() + 1)}-${pad(date.getUTCDate())} ` +
  `${pad(date.getUTCHours())}:${pad(date.getUTCMinutes())}:${pad(date.getUTCSeconds())}`;

Custom Methods: Your very own magic spell

With Date.prototype, you can add your custom method:

// If Cinderella had this, she might have avoided the whole shoe scandal
Date.prototype.toMysqlFormat = function() {
  return `${this.getFullYear()}-${pad(this.getMonth() + 1)}-${pad(this.getDate())} ` +
    `${pad(this.getHours())}:${pad(this.getMinutes())}:${pad(this.getSeconds())}`;
};

Milliseconds: Small but mighty

When millisecond precision is crucial, include milliseconds in your format:

const toMysqlFormatWithMillis = (date) => 
  `${date.toISOString().split('T').join(' ').slice(0, 22)}`;

Timezone Adjustments: Keep up with the world

To adjust for local times around the globe:

const toTimezoneMysqlFormat = (date, timezone) => {
  let localDate = new Date(date.toLocaleString('en-US', { timeZone: timezone }));
  return toMysqlFormat(localDate);
};