Objection.js: Time zone misinterpreted as local time instead of UTC on query

[CryptoKiddies]

UTC timestamps that are pulled from my postgres database are pushed ahead in time, the difference being that between my local time and UTC. Objection thinks my timestamp record is local for some reason. I want to keep the returned value unchanged, as it is already in UTC. This is the current format: 2018-05-04 19:08:57

In my database, timestamp is set to "without time zone" (the default postgres setting). I also have the default value setting to now(), though I believe this value is overwritten by my base model timestamp insertion action in Objection.

Is there a way to turn off time conversion after timestamp fetching in Objection (or Knex)? If not, I think I'll need to turn timezone on in my database, which I don't want to do.

[jeff3yan]

That's probably best to do through modifying the postgres type parsers:

const { types } = require('pg');
types.setTypeParser(1114, value => value);

This will result in node interpreting the result as a string, then it's up to you to determine whether you want to wrap the string using a Date type.

[koskimas]

Not related to objection. Objection doesn't touch dates at all, but simply returns whatever the database client (pg in this case) returns.

[heisian]

@GeeeCoin Yes, as @jeff3yan you can handle this by telling pg to parse DATEs as such:

const knex = require('knex')
const objection = require('objection')
const moment = require('moment') // or whatever date/time functions you want to use

const setTypeParser = (knex) => {
  const DATE_OID = 1082
  const TIMESTAMP_OID = 1114 // don't know if this is correct, just got it from jeff's response.

  knex.client.driver.types.setTypeParser(DATE_OID, function(val) {
    // For a DATE field, I only want the date
    return val === null ? null : moment.utc(val).format('YYYY-MM-DD')
  })

  knex.client.driver.types.setTypeParser(TIMESTAMP_OID, function(val) {
    // Ensure no timezone
    return val === null ? null : moment.utc(val).toDate()
  })
}

// Set type parsers on the client
setTypeParser(knex)

// Inject knex into base Model
objection.Model.knex(knex)

exports.objection = objection

[CryptoKiddies]

@heisian thanks for the pointers. I noticed our knex config file sets client: 'postgresql'. Do you think that's the reason right there? Perhaps I need to specify client: 'pg' in order for the correct driver to be recognized and loaded. Will test in a bit.

[CryptoKiddies]

Nevermind, postgresql is a recognized "dialect," no need to call client "pg."

[radiumrasheed]

@GeeeCoin Yes, as @jeff3yan you can handle this by telling pg to parse DATEs as such:

const knex = require('knex')
const objection = require('objection')
const moment = require('moment') // or whatever date/time functions you want to use

const setTypeParser = (knex) => {
  const DATE_OID = 1082
  const TIMESTAMP_OID = 1114 // don't know if this is correct, just got it from jeff's response.

  knex.client.driver.types.setTypeParser(DATE_OID, function(val) {
    // For a DATE field, I only want the date
    return val === null ? null : moment.utc(val).format('YYYY-MM-DD')
  })

  knex.client.driver.types.setTypeParser(TIMESTAMP_OID, function(val) {
    // Ensure no timezone
    return val === null ? null : moment.utc(val).toDate()
  })
}

// Set type parsers on the client
setTypeParser(knex)

// Inject knex into base Model
objection.Model.knex(knex)

exports.objection = objection

@heisian how do I go about this for mysql, is it the same ?