Nodejs mysql get correct TimeStamp format

Question

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 command?

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

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

anvk · Accepted Answer · 2016-07-18 15:24:50Z

7

I would recommend 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 recommend 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(command, (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(command, 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);
});

edited Jul 18, 2016 at 15:24

answered Jul 17, 2016 at 15:14

anvk

1,2019 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

mahdi pishguy Over a year ago

Thanks, but whats ...row, ? could you complete this section?

mahdi pishguy Over a year ago

Thanks, why i get [] ? . problem is Invalid number of arguments, expected 0..1 for this part of your code {}, row, { create_date: formatDate(row.create_date) }

anvk Over a year ago

I'm very sorry. I had a typo create_date instead of created_date. I fixed that in my reply. Also I have a Fiddle example right here to demonstrate how my code works jsfiddle.net/zperwfex

mahdi pishguy Over a year ago

Thanks. but in jsfiddle i get this error: ReferenceError: moment is not defined

Hicham Zouarhi Over a year ago

@mahdipishguy add moment.js to your html page ( the one calling your js file )

|

Vyacheslav Potseluyko · Accepted Answer · 2017-06-27 19:41:04Z

1

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

answered Jun 27, 2017 at 19:41

Vyacheslav Potseluyko

1191 silver badge2 bronze badges

Comments

Eric Aya · Accepted Answer · 2018-09-10 09:59:38Z

0

Just add this to your db connection config:

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

edited Sep 10, 2018 at 9:59

Eric Aya

70.2k36 gold badges190 silver badges266 bronze badges

answered Sep 10, 2018 at 9:57

emmveqz

11 bronze badge

Comments

Marcio J · Accepted Answer · 2023-05-15 12:03:11Z

0

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.com/mysqljs/mysql#connection-options

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

answered May 15, 2023 at 12:03

Marcio J

7801 gold badge7 silver badges19 bronze badges

Collectives™ on Stack Overflow

Nodejs mysql get correct TimeStamp format

4 Answers 4

9 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related