Clever Geek Handbook

Knexjs returning mysql timestamp, datetime columns as Javascript Date object

I am using knexjs i inserting data in YYYY-MM-DD HH:mm:ss

eg format 2017-07-14 15:00:00

and after saving when the data is fetched the datetime column values ​​are returned as a javasript Date object. I want to return this object in format YYYY-MM-DD HH:mm:ss

, but it is returned in YYYY-MM-DDTHH:mm:ss.000Z

eg format 2017-06-23T06:44:44.000Z

. I get them back by iterating and converting them manually. I was wondering if there is another way to do this like in mysql or knexjs config. currently my knexjs config is.

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


source to share


3 answers


Modify your connection object as follows:



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


source


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

You can override the default conversion by adding the 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 your own parsing for DATETIME

or TIMESTAMP

.

+3


source


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

0


source






More articles:


All Articles