Convert JS date time to MySQL datetime

Question

Does anyone know how to convert JS dateTime to MySQL datetime? Also is there a way to add a specific number of minutes to JS datetime and then pass it to MySQL datetime?

Answer 1

var date;
date = new Date();
date = date.getUTCFullYear() + '-' +
    ('00' + (date.getUTCMonth()+1)).slice(-2) + '-' +
    ('00' + date.getUTCDate()).slice(-2) + ' ' + 
    ('00' + date.getUTCHours()).slice(-2) + ':' + 
    ('00' + date.getUTCMinutes()).slice(-2) + ':' + 
    ('00' + date.getUTCSeconds()).slice(-2);
console.log(date);

or even shorter:

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

Output:

2012-06-22 05:40:06

For more advanced use cases, including controlling the timezone, consider using http://momentjs.com/:

require('moment')().format('YYYY-MM-DD HH:mm:ss');

For a lightweight alternative to momentjs, consider https://github.com/taylorhakes/fecha

require('fecha').format('YYYY-MM-DD HH:mm:ss')

Answer 2

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"

Answer 3

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());
};

Answer 4

JS time value for MySQL

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

OR

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

you can send this in params its will work.

Answer 5

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

Answer 6

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

Answer 7

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!

Answer 8

This is by far the easiest way I can think of

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

Answer 9

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.

Answer 10

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)

Answer 11

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

Answer 12

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

Answer 13

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/

Answer 14

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');

Answer 15

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);

Answer 16

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', ' '));

Answer 17

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';

Answer 18

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>

Answer 19

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")

Answer 20

This is the easiest way -

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

Answer 21

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

Answer 22

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

Answer 23

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

Answer 24

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'));

Answer 25

const dateToSqlDate = (date) => date.toISOString().slice(0, -5).replace('T', ' ')

console.log(dateToSqlDate(new Date()))

Answer 26

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', ' ');
}