Nodejs MySQL Get Correct Timestamp Format

Nodejs mysql get correct TimeStamp format

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

Get a correctly formatted date from mysql

I got it working thanks to nbk's comment.

The solution was to add the timezone when starting the mysql connection :

db = mysql.createConnection({
    host:     "...",
    user:     "...",
    password: "...",
    database: "...",
    timezone: 'utc'
});

ISO 8601 Timestamp for MySQL Database: MySQL Incorrect datetime value

As stated in Date and Time Literals:

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

• As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A “relaxed” syntax is permitted here, too: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '2012-12-31 11:30:45', '2012^12^31 11+30+45', '2012/12/31 11*30*45', and '2012@12@31 11^30^45' are equivalent.

• As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28', but '071122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.

• As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. Although this fractional part is recognized, it is discarded from values stored into DATETIME or TIMESTAMP columns. For information about fractional seconds support in MySQL, see Section 11.3.6, “Fractional Seconds in Time Values”.

Your date literal of '2012-08-24T17:29:11.683Z' does not fit any of these formats; suggest you either—

• use instead the Node.js Date object's toLocaleFormat() method (be sure that the timezone of the MySQL connection matches that of Node.js's locale):

    if s instanceof Date
           return s.toLocaleFormat("%Y-%m-%d %H:%M:%S")
  

• use the Node.js Date object's valueOf() method to obtain the time's value in milliseconds since the UNIX epoch, divide by 1000 (to get seconds since the UNIX epoch) and pass through MySQL's FROM_UNIXTIME() function.

How to convert a Timestamp into MySQL DateTime in JavaScript?

You can pass the Date object of JavaScript directly to MySQL. And MySQL will automatically generate the DateTime format from that Date object.

const date = new Date(1631514003973);

Displaying the MySQL date correctly

Use toUTCString() to get rid of -0500 (Eastern Standard Time) but GMT will be there. To get rid of whole GMT-0500 (Eastern Standard Time) string, you need to do manipulation like:

today.toUTCString().split('GMT')[0];

The best practice would be to manipulate DateTime into String format at server side or at least embedded JavaScript before rendering it on UI.

If you want to do it at the client (HTML) side then you need to use JavaScript string manipulation functions. So in your case, it should be like this:

<table>
    <% print.forEach(function (student) { %>

        <tr class="table-info">
        <td><%= student.firstName %></td>
        <td><%= student.lastName %></td>

        <td><%= student.building %></td>
        <td><%= student.room %></td>
        <td><%= student.checkIn.split('GMT')[0] %></td>
        <td><%= student.checkOut.split('GMT')[0] %></td>


        </tr>
        <% }) %>

I am not sure if this is the best way to accomplish what you want but if you don't want to change your code then above could be the nearest solution for your problem.

---

Related Topics