Convert JS date time to MySQL datetime

I think the solution can be less clunky by using method toISOString(), it has a wide browser compatibility.

So your expression will be a one-liner:

new Date().toISOString().slice(0, 19).replace('T', ' ');

The generated output:

"2017-06-29 17:54:04"

While JS does possess enough basic tools to do this, it's pretty clunky.

/**
 * You first need to create a formatting function to pad numbers to two digits…
 **/
function twoDigits(d) {
    if(0 <= d && d < 10) return "0" + d.toString();
    if(-10 < d && d < 0) return "-0" + (-1*d).toString();
    return d.toString();
}

/**
 * …and then create the method to output the date string as desired.
 * Some people hate using prototypes this way, but if you are going
 * to apply this to more than one Date object, having it as a prototype
 * makes sense.
 **/
Date.prototype.toMysqlFormat = function() {
    return this.getUTCFullYear() + "-" + twoDigits(1 + this.getUTCMonth()) + "-" + twoDigits(this.getUTCDate()) + " " + twoDigits(this.getUTCHours()) + ":" + twoDigits(this.getUTCMinutes()) + ":" + twoDigits(this.getUTCSeconds());
};

JS time value for MySQL

const DATE_FORMATER = require( 'dateformat' );
const datetime = DATE_FORMATER( new Date(), "yyyy-mm-dd HH:MM:ss" );

OR

const MOMENT = require( 'moment' );
const datetime = MOMENT().format( 'YYYY-MM-DD  HH:mm:ss.000' );

you can send this in params its will work.

For arbitrary date string,

// Your default date object  
var starttime = new Date();
// Get the iso time (GMT 0 == UTC 0)
var isotime = new Date((new Date(starttime)).toISOString() );
// getTime() is the unix time value, in milliseconds.
// getTimezoneOffset() is UTC time and local time in minutes.
// 60000 = 60*1000 converts getTimezoneOffset() from minutes to milliseconds. 
var fixedtime = new Date(isotime.getTime()-(starttime.getTimezoneOffset()*60000));
// toISOString() is always 24 characters long: YYYY-MM-DDTHH:mm:ss.sssZ.
// .slice(0, 19) removes the last 5 chars, ".sssZ",which is (UTC offset).
// .replace('T', ' ') removes the pad between the date and time.
var formatedMysqlString = fixedtime.toISOString().slice(0, 19).replace('T', ' ');
console.log( formatedMysqlString );

Or a single line solution,

var formatedMysqlString = (new Date ((new Date((new Date(new Date())).toISOString() )).getTime() - ((new Date()).getTimezoneOffset()*60000))).toISOString().slice(0, 19).replace('T', ' ');
console.log( formatedMysqlString );

This solution also works for Node.js when using Timestamp in mysql.

@Gajus Kuizinas's first answer seems to modify mozilla's toISOString prototype

I am surprised that no one mention the Swedish date time format for javascript yet.
the BCP 47 language tag for the Swedish language is sv-SE that you can use for the new Date "locale" parameter.
I am not saying it is a good practice, but it works.

console.log(new Date().toLocaleString([['sv-SE']])) //2022-09-10 17:02:39

The easiest correct way to convert JS Date to SQL datetime format that occur to me is this one. It correctly handles timezone offset.

const toSqlDatetime = (inputDate) => {
    const date = new Date(inputDate)
    const dateWithOffest = new Date(date.getTime() - (date.getTimezoneOffset() * 60000))
    return dateWithOffest
        .toISOString()
        .slice(0, 19)
        .replace('T', ' ')
}

toSqlDatetime(new Date()) // 2019-08-07 11:58:57
toSqlDatetime(new Date('2016-6-23 1:54:16')) // 2016-06-23 01:54:16

Beware that @Paulo Roberto answer will produce incorrect results at the turn on new day (i can't leave comments). For example:

var d = new Date('2016-6-23 1:54:16'),
    finalDate = d.toISOString().split('T')[0]+' '+d.toTimeString().split(' ')[0];
console.log(finalDate); // 2016-06-22 01:54:16 

We've got 22 June instead of 23!

new Date().toISOString().slice(0, 10)+" "+new Date().toLocaleTimeString('en-GB');

The short version:

// JavaScript timestamps need to be converted to UTC time to match MySQL

// MySQL formatted UTC timestamp +30 minutes
let d = new Date()
let mySqlTimestamp = new Date(
  d.getFullYear(),
  d.getMonth(),
  d.getDate(),
  d.getHours(),
  (d.getMinutes() + 30), // add 30 minutes
  d.getSeconds(),
  d.getMilliseconds()
).toISOString().slice(0, 19).replace('T', ' ')

console.log("MySQL formatted UTC timestamp: " + mySqlTimestamp)

UTC time is generally the best option for storing timestamps in MySQL. If you don't have root access, then run set time_zone = '+00:00' at the start of your connection.

Display a timestamp in a specific time zone in MySQL with the method convert_tz.

select convert_tz(now(), 'SYSTEM', 'America/Los_Angeles');

JavaScript timestamps are based on your device's clock and include the time zone. Before sending any timestamps generated from JavaScript, you should convert them to UTC time. JavaScript has a method called toISOString() which formats a JavaScript timestamp to look similar to MySQL timestamp and converts the timestamp to UTC time. The final cleanup takes place with slice and replace.

let timestmap = new Date()
timestmap.toISOString().slice(0, 19).replace('T', ' ')

Long version to show what is happening:

// JavaScript timestamps need to be converted to UTC time to match MySQL

// local timezone provided by user's device
let d = new Date()
console.log("JavaScript timestamp: " + d.toLocaleString())

// add 30 minutes
let add30Minutes = new Date(
  d.getFullYear(),
  d.getMonth(),
  d.getDate(),
  d.getHours(),
  (d.getMinutes() + 30), // add 30 minutes
  d.getSeconds(),
  d.getMilliseconds()
)
console.log("Add 30 mins: " + add30Minutes.toLocaleString())

// ISO formatted UTC timestamp
// timezone is always zero UTC offset, as denoted by the suffix "Z"
let isoString = add30Minutes.toISOString()
console.log("ISO formatted UTC timestamp: " + isoString)

// MySQL formatted UTC timestamp: YYYY-MM-DD HH:MM:SS
let mySqlTimestamp = isoString.slice(0, 19).replace('T', ' ')
console.log("MySQL formatted UTC timestamp: " + mySqlTimestamp)

This is by far the easiest way I can think of

new Date().toISOString().slice(0, 19).replace("T", " ")

The venerable DateJS library has a formatting routine (it overrides ".toString()"). You could also do one yourself pretty easily because the "Date" methods give you all the numbers you need.

Full workaround (to mantain the timezone) using @Gajus answer concept:

var d = new Date(),
    finalDate = d.toISOString().split('T')[0]+' '+d.toTimeString().split(' ')[0];
console.log(finalDate); //2018-09-28 16:19:34 --example output

I have given simple JavaScript date format examples please check the bellow code

var data = new Date($.now()); // without jquery remove this $.now()
console.log(data)// Thu Jun 23 2016 15:48:24 GMT+0530 (IST)

var d = new Date,
    dformat = [d.getFullYear() ,d.getMonth()+1,
               d.getDate()
               ].join('-')+' '+
              [d.getHours(),
               d.getMinutes(),
               d.getSeconds()].join(':');

console.log(dformat) //2016-6-23 15:54:16

Using momentjs

var date = moment().format('YYYY-MM-DD H:mm:ss');

console.log(date) // 2016-06-23 15:59:08

Example please check https://jsfiddle.net/sjy3vjwm/2/

var _t = new Date();

if you want UTC format simply

_t.toLocaleString('indian', { timeZone: 'UTC' }).replace(/(\w+)\/(\w+)\/(\w+), (\w+)/, '$3-$2-$1 $4');

or

_t.toISOString().slice(0, 19).replace('T', ' ');

and if want in specific timezone then

_t.toLocaleString('indian', { timeZone: 'asia/kolkata' }).replace(/(\w+)\/(\w+)\/(\w+), (\w+)/, '$3-$2-$1 $4');

Using toJSON() date function as below:

var sqlDatetime = new Date(new Date().getTime() - new Date().getTimezoneOffset() * 60 * 1000).toJSON().slice(0, 19).replace('T', ' ');
console.log(sqlDatetime);

Datetime in a different time zone

This uses @Gayus solution using the format outputted from toISOString() but it adjusts the minutes to account for the time zone. Final format: 2022-03-01 13:32:51

let ts = new Date();
ts.setMinutes(ts.getMinutes() - ts.getTimezoneOffset());
console.log(ts.toISOString().slice(0, 19).replace('T', ' '));

A simple solution is send a timestamp to MySQL and let it do the conversion. Javascript uses timestamps in milliseconds whereas MySQL expects them to be in seconds - so a division by 1000 is needed:

// Current date / time as a timestamp:
let jsTimestamp = Date.now();

// **OR** a specific date / time as a timestamp:
jsTimestamp = new Date("2020-11-17 16:34:59").getTime();

// Adding 30 minutes (to answer the second part of the question):
jsTimestamp += 30 * 1000;

// Example query converting Javascript timestamp into a MySQL date
let sql = 'SELECT FROM_UNIXTIME(' + jsTimestamp + ' / 1000) AS mysql_date_time';

I needed a function to return the sql timestamp format in javascript form a selective timezone

<script>
console.log(getTimestamp("Europe/Amsterdam")); // Europe/Amsterdam
console.log(getTimestamp()); // UTC

function getTimestamp(timezone) {
  if (timezone) { 
  var dateObject = new Date().toLocaleString("nl-NL", { // it will parse with the timeZone element, not this one
    timeZone: timezone, // timezone eg "Europe/Amsterdam" or "UTC"
    month: "2-digit",
    day: "2-digit",
    year: "numeric",

    hour: "2-digit",
    minute: "2-digit",
    second: "2-digit",
  });

  let [dateRaw, timeRaw] = dateObject.split(" ");
  let [day, month, year] = dateRaw.split("-");
  var timestamp = year + "-" + month + "-" + day + " " + timeRaw;
  }else{
  // UTC from @Gajus, 95% faster then the above
  timestamp = new Date().toISOString().slice(0, 19).replace("T", " ");
  }
  return timestamp; // YYYY-MM-DD HH:MI:SS
}
</script>

If you are using Date-fns then the functionality can be achived easily using format function.

const format = require("date-fns/format");
const date = new Date();

const formattedDate = format(date, "yyyy-MM-dd HH:mm:ss")

This is the easiest way -

new Date().toISOString().slice(0, 19).replace("T", " ")

I'm using this long time and it's very helpful for me, use as you like

Date.prototype.date=function() {
    return this.getFullYear()+'-'+String(this.getMonth()+1).padStart(2, '0')+'-'+String(this.getDate()).padStart(2, '0')
}

Date.prototype.time=function() {
    return String(this.getHours()).padStart(2, '0')+':'+String(this.getMinutes()).padStart(2, '0')+':'+String(this.getSeconds()).padStart(2, '0')
}

Date.prototype.dateTime=function() {
    return this.getFullYear()+'-'+String(this.getMonth()+1).padStart(2, '0')+'-'+String(this.getDate()).padStart(2, '0')+' '+String(this.getHours()).padStart(2, '0')+':'+String(this.getMinutes()).padStart(2, '0')+':'+String(this.getSeconds()).padStart(2, '0')
}

Date.prototype.addTime=function(time) {
    var time=time.split(":")
    var rd=new Date(this.setHours(this.getHours()+parseInt(time[0])))
    rd=new Date(rd.setMinutes(rd.getMinutes()+parseInt(time[1])))
    return new Date(rd.setSeconds(rd.getSeconds()+parseInt(time[2])))
}

Date.prototype.addDate=function(time) {
    var time=time.split("-")
    var rd=new Date(this.setFullYear(this.getFullYear()+parseInt(time[0])))
    rd=new Date(rd.setMonth(rd.getMonth()+parseInt(time[1])))
    return new Date(rd.setDate(rd.getDate()+parseInt(time[2])))
}

Date.prototype.subDate=function(time) {
    var time=time.split("-")
    var rd=new Date(this.setFullYear(this.getFullYear()-parseInt(time[0])))
    rd=new Date(rd.setMonth(rd.getMonth()-parseInt(time[1])))
    return new Date(rd.setDate(rd.getDate()-parseInt(time[2])))
}

and then just:

new Date().date()

which returns current date in 'MySQL format'

for add time is

new Date().addTime('0:30:0')

which will add 30 minutes.... and so on

Solution built on the basis of other answers, while maintaining the timezone and leading zeros:

var d = new Date;

var date = [
    d.getFullYear(),
    ('00' + d.getMonth() + 1).slice(-2),
    ('00' + d.getDate() + 1).slice(-2)
].join('-');

var time = [
    ('00' + d.getHours()).slice(-2),
    ('00' + d.getMinutes()).slice(-2),
    ('00' + d.getSeconds()).slice(-2)
].join(':');

var dateTime = date + ' ' + time;
console.log(dateTime) // 2021-01-41 13:06:01

Simple: just Replace the T. Format that I have from my <input class="form-control" type="datetime-local" is : "2021-02-10T18:18"

So just replace the T, and it would look like this: "2021-02-10 18:18" SQL will eat that.

Here is my function:

var CreatedTime = document.getElementById("example-datetime-local-input").value;

var newTime = CreatedTime.replace("T", " ");

Reference: https://www.tutorialrepublic.com/faq/how-to-replace-character-inside-a-string-in-javascript.php#:~:text=Answer%3A%20Use%20the%20JavaScript%20replace,the%20global%20(%20g%20)%20modifier.

https://www.tutorialrepublic.com/codelab.php?topic=faq&file=javascript-replace-character-in-a-string

What about this one taking account the timezone offset?

const getTimestamp = (date = false) => {
  const d = ! date ? new Date() : new Date(date);

  return new Date(d - d.getTimezoneOffset() * 60 * 1000).toJSON().replace('T', ' ').split('.')[0];
}

console.log(getTimestamp());
console.log(getTimestamp('2016-6-23 1:54:16'));

That works fine for me:

function DateToSql(d: Date): string {
    let dt: Date = new Date(d.getTime());
    dt.setTime(dt.getTime() - dt.getTimezoneOffset() * 60000);
    return dt.toISOString().slice(0, 19).replace('T', ' ');
}

No need for external libraries or string manipulation.

Create js date object (I use datepicker format as an example) and convert it to Swedish locals, which is the same format that mysql uses:

var local_time ="Fri Oct 06 2023 01:01:25 GMT+0300 (Eastern European Summer Time)";

const d = new Date(local_time);      // d=2023-10-05T22:01:25.000Z
today = d.toLocaleString('sv-SE');   // 2023-10-06 01:01:25 

And then use MYSQL function to convert time zone to mysql UTC time

select convert_tz('2023-10-06 01:01:25','+03:00', 'SYSTEM') // 2023-10-05 22:01:25

Easy and Short way to store DateTime(YYYY-MM-DD HH:MM:SS) into MySQL Database

const [dateTime, setDateTime] = useState('');

setDateTime(new Date(YOUR_DATETIME).toLocaleString('sv-SE')); // Any Date formate into this formate (YYYY-MM-DD HH:MM:SS)

// On submit or Something conditions

axios.post('url',dateTime).then((res)=>{});

in the front-end, convert to UTC time first :

const utcDate = new Date(tmpCalendarEntry.date.getTime() - (tmpCalendarEntry.date.getTimezoneOffset() * 60000));
tmpCalendarEntry.date = utcDate;

then send it over the back end.

C#, when retrieving the data will properly change the datetime back to whatever timezone you need implicitely.

But if you want to do it explicitely, once C# knows its in UTC time, you can just call ToLocalTime:

DateTime dt = convertedDate.ToLocalTime();

This will give you the result you require. Take a look at this answer for more details : https://stackoverflow.com/a/963812/6327086