Knexjs returning mysql timestamp, datetime columns as Javascript Date object

Question

i am using knexjs i insert data in the format YYYY-MM-DD HH:mm:ss e.g 2017-07-14 15:00:00 and after saving when the data is fetched the datetime column values are returned as javasript Date object. i want to return those object in the format YYYY-MM-DD HH:mm:ss but it returning in the format YYYY-MM-DDTHH:mm:ss.000Z e.g 2017-06-23T06:44:44.000Z. i am returning them by iterating and converting them manually. i was wondering if there is another way to do it like in mysql driver or knexjs configuration. currently my knexjs configuration is this.

 var connection = require('knex')({
            client: 'mysql',
            connection: {
                host: db.host,
                user: db.user,
                password: db.password,
                database: db.database,
                timezone: 'UTC'
            }
       });

Answer 1

This is how mysql driver converts types read from database to javascript (https://github.com/mysqljs/mysql#type-casting)

You can override default conversion by adding typeCast connection option:

var moment = require('moment');
var connection = require('knex')({
        client: 'mysql',
        connection: {
            host: db.host,
            user: db.user,
            password: db.password,
            database: db.database,
            timezone: 'UTC',
            typeCast: function (field, next) {
              if (field.type == 'DATETIME') {
                return moment(field.string()).format('YYYY-MM-DD HH:mm:ss');
              }
              return next();
            }
        }
   });

I'm not sure if you need to add custom parsing for DATETIME or TIMESTAMP type though.

Answer 2

Change your connection object with this:

var connection = require('knex')({
        client: 'mysql',
        connection: {
            host: db.host,
            user: db.user,
            password: db.password,
            database: db.database,
            timezone: 'UTC',
            dateStrings: true
        }
   });

Answer 3

In my case, the connection was a string so I had to find the date OID and use pg.types.setTypeParser(DATE_OID, d => moment(d));