I am using knex and I insert data in the format YYYY-MM-DD HH:mm:ss e.g 2017-07-14 15:00:00. After saving, when the data is fetched, the datetime column values are returned as JavaScript Date objects.

I want to return those objects in the format YYYY-MM-DD HH:mm:ss but knex returns them 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 knex configuration.

This is my knex configuration:

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

I am using knex and I insert data in the format YYYY-MM-DD HH:mm:ss e.g 2017-07-14 15:00:00. After saving, when the data is fetched, the datetime column values are returned as JavaScript Date objects.

I want to return those objects in the format YYYY-MM-DD HH:mm:ss but knex returns them 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 knex configuration.

This is my knex configuration:

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

• javascript
• mysql
• node.js
• knex.js

3 Answers 3

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

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.

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