node.js mysql – Extract datetime in YYYY-MM-DDThh:mm:ssZ format without milliseconds

I’m importing datetime information in this format:

2020-04-17T19:08:28Z

into a mysql datetime column.

When I make a select I get the data in this format back:

2020-04-17T19:08:28.000Z

What I would like is the original format though. How to do so exactly? So goal:

2020-04-17T19:08:28Z

That’s my setup:

var connection = mysql.createConnection({
  host     : 'host',
  user     : 'u',
  password : 'p',
  database : 'db'
});

connection.query("Select * FROM table", function(err, rows) {
    if (err) {
      console.log(err);
    } else {
      console.log(rows)
    }
});

rows:

[ [ {
      column_date: 2020-04-17T19:08:28.000Z,
      ...
    ... 1846 more items ] ]

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

Answer

you can use DATE_FORMAT() function for this. Example:

const query = "SELECT DATE_FORMAT(date_column, "%Y-%m-%dT%H:%i:%sZ") formatted_date, * from table";

this will format the date as you require.

Also, if you want some more customization, check this: http://www.sqlines.com/oracle-to-mysql/to_char_datetime