javascript - Nodejs mysql get correct TimeStamp format - Stack Overflow

admin管理员组

文章数量:1291002

I'm using mysqljs and after get result from sql i get different TimeStamp format like with this:

created_at: Sat Jul 16 2016 23:52:54 GMT+0430 (IRDT)

but it is on mysql is :

2016-07-16 23:52:54

and i want to get that, not other format, how can i set this format on mysql connection or sql mand?

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'a',
    database: 'signal'
});

connection.query(mand, function (err, rows) {
    if (err) throw err;
    console.log(rows);
});

I'm using mysqljs and after get result from sql i get different TimeStamp format like with this:

created_at: Sat Jul 16 2016 23:52:54 GMT+0430 (IRDT)

but it is on mysql is :

2016-07-16 23:52:54

and i want to get that, not other format, how can i set this format on mysql connection or sql mand?

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'a',
    database: 'signal'
});

connection.query(mand, function (err, rows) {
    if (err) throw err;
    console.log(rows);
});

• javascript
• node.js

Share Improve this question Follow asked Jul 17, 2016 at 14:51 mahdi pishguymahdi pishguy 1,03411 gold badge1616 silver badges4343 bronze badges

Add a ment  | 

4 Answers 4

Sorted by: Reset to default 7

I would remend to process rows instead of trying to change MySQL output result. This way your database will have detailed full data about the created_date. While clients (other functions, systems, projects, etc) will format the value retrieved from DB to whatever format they need. Moreover you will keep a consistent return results for dates through your system. Whatever DB query is executed your software will always expect the same format returned back.

Here is an example in ES6 also using moment.js library to simplify any date operations you will have, strongly remend to use this library.

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'a',
    database: 'signal'
});

function formatDate(date) {
  return moment(date).format('YYYY-MM-DD HH:mm:ss');
}

connection.query(mand, (err, rows) => {
    if (err) {
      throw err;
    }

    rows = rows.map(row => ({
      ...row,
      created_date: formatDate(row.created_date)
    }));

    console.log(rows);
});

update or in ES5

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'a',
    database: 'signal'
});

function formatDate(date) {
  return moment(date).format('YYYY-MM-DD HH:mm:ss');
}

connection.query(mand, function(err, rows) {
    if (err) {
      throw err;
    }

    rows = rows.map(function(row) {
      return Object.assign({}, row, { created_date: formatDate(row.created_date) });
    });

    console.log(rows);
});

That will sound unbelievable but it's enough to call '.toString()' on timestamp string. That will give human readable output

Just add this to your db connection config:

timezone: "America/Los_Angeles" // <-- change for whatever your TZ is

You can initialize the connection with option dateString: true to force date types to be returned as string. Or you can also use custom type casting to pass a function and handle type casting yourself:

var pool = mysql.createPool({
  // database properties...
  typeCast: function (field, next) {
    if (field.type === 'TIMESTAMP') {
        return field.string(); // or any format you want
    } else {
        return next();
    }
  }
});

Reference:

https://github./mysqljs/mysql#connection-options

https://github./mysqljs/mysql#type-casting

本文标签： javascriptNodejs mysql get correct TimeStamp formatStack Overflow