Objection.js: conversion to utc for DATETIME

[capaj]

I am migrating an app from sequelize to objection and one thing I've hit is that sequelize normalizes any date into a UTC before storing in the DB: https://stackoverflow.com/a/28410849/671457

knex/objection just stores the time local to the machine it is running on. Is there a way to normalize all dates to UTC as with sequelize @koskimas ?

[koskimas]

Neither objection nor knex touch the dates. They are saved as given and returned in the format the DB returns them. One way to change the DB timezone is to add a afterCreate hook to the knex config:

const knexConfig = {
  client: 'postgres',
  connection: {
    ...
  },
  pool: {
    min: 2,
    max: 50,
    afterCreate(conn, cb) {
      conn.query('SET timezone="UTC";', (err) => {
        cb(err, conn);
      });
    }
  }
}

[capaj]

Thanks @koskimas I solved it even easier by doing:

const db = knex({
  client: 'mysql',
  connection: {
    host: sqlConfig.hostname,
    user: sqlConfig.username,
    password,
    database,
    timezone: 'UTC'  // this needed to be added
  }
})

as suggested here https://stackoverflow.com/a/25729295/671457

[koskimas]

Oh, didn't know about that 😄

[elhigu]

@capaj yup mysql driver supports that attribute in connection object. If you are using postgres you would need the trick mentioned by @koskimas

[CryptoKiddies]

@elhigu @koskimas do you know why this is not working for my postgres setup? When I query a timestamp with my objection model, it still comes in as GMT followed by my time zone offset. I am using afterCreate to specify "UTC" thanks! Is this an issue with my Amazon RDS setup?

[kojilab]

I'm having that kind of issue. My Mysql system timezone is +00:00 and yet dates are being converted to UTC with a 7 hour difference. I'm PST daylight saving.