Objection.js: Incorrect datetime value for created_at column

[mtpultz]

Unhandled rejection Error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2017-10-10T06:50:04.006Z' for column 'created_at' at row 1

When using $beforeInsert and $beforeUpdate methods on created_at and updated_at. If I comment these methods out it works, and the examples don't show how to setup created_at or updated_at so I'm not sure if it's a issue with one of the models or the migration.

export class BaseModel extends Model {
  created_at: string;
  updated_at: string;

  $beforeInsert() {
    this.created_at = new Date().toISOString();
  }

  $beforeUpdate() {
    this.updated_at = new Date().toISOString();
  }
}

export class User extends BaseModel {
  readonly id: number;
  username: string;
  password: string;
  role: string;

  static tableName = 'users';

  static jsonSchema = {
    type: 'object',
    required: ['username', 'password'],

    properties: {
      id: { type: 'integer' },
      username: { type: 'string', maxLength: 50 },
      password: { type: 'string', minLength: 8, maxLength: 255 },
      role: {
        type: 'string',
        enum: ['guest', 'admin'],
        default: 'guest'
      }
    }
  };

  static relationMappings: RelationMappings = {
    profile: {
      relation: Model.HasOneRelation,
      modelClass: UserProfile,
      join: {
        from: 'users.id',
        to: 'user_profiles.user_id'
      }
    }
  };

}

exports.up = function (knex, Promise) {
  return knex.schema
    .createTable('users', (table) => {
      table.increments('id').primary();
      table.string('username')
        .notNullable()
        .unique();
      table.string('password')
        .notNullable();
      table.enum('role', ['guest', 'admin'])
        .notNullable()
        .defaultTo('guest');

      table.timestamps(true, true);
    });
};

exports.down = function (knex, Promise) {
  return knex.schema.dropTable('users');
};

[elhigu]

Check what queries are you writing to DB with DEBUG=knex:* environment variable and check your DB schema what type of field your created_at actually is.

[mceachen]

YMMV, but I've never seen a database schema that encoded dates as strings go smoothly. If you really want something readable, you can left-pad your time and data values into a large base-10 number (like YYYYMMDDHHMMSS, 20171010162834), but you can also just encode the date as millis from common epoch (via date.getTime())--or best, use the date type your database provides.

[mceachen]

Here's an example of auto-conversion of Date columns to millis (this is from my project that uses sqlite, which doesn't have a date type):

class Base extends objection.Model {

  readonly timeColumns = ["createdAt", "updatedAt"]

  $parseDatabaseJson(json: object) {
    json = super.$parseDatabaseJson(json)
    this.timeColumns.forEach(ea => {
      if (typeof json[ea] === "number") {
        json[ea] = new Date(json[ea])
      }
    })
    return json
  }

  $formatDatabaseJson(json: object) {
    json = super.$formatDatabaseJson(json)
    this.timeColumns.forEach(ea => {
      if (json[ea] && json[ea].getTime) {
        json[ea] = json[ea].getTime()
      }
    })
    return json
  }
}

(and yeah, my schema has camelCasedColumnNames, not snake_cased...)

[mtpultz]

Hi @mceachen thanks I wasn't sure how knex table.timestamps was supposed to be set in the BaseModel. I assumed based on the objection's doc example of $beforeInsert and $beforeUpdate that created_at and updated_at must be strings since they use new Date().toISOString() so that's my fault. I tried setting their typings to number and changed $beforeInsert and $beforeUpdate to use Date().getTime() instead, but this throws a similar error but for epoc time ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '1507679842025' for column 'created_at' at row 1

export class BaseModel extends Model {
  created_at: number;
  updated_at: number;

  $beforeInsert() {
    this.created_at = new Date().getTime();
  }

  $beforeUpdate() {
    this.updated_at = new Date().getTime();
  }

 // ...
}

[mtpultz]

Sorry just saw your next post after I added my comment. Do you not need to set created_at and updated_at in the model as member variables? This isn't very clear in the docs. I think I'm confused how the models should be set up. The examples seem to show that for the models you add all the columns as member variables with typings in the class. Similar to the User model or BaseModel examples I have above, but in your example, you're adding timeColumns... or is it that knex handles this internally?

[mceachen]

@mtpultz check this out: https://github.com/Vincit/objection.js/pull/548

[mtpultz]

Thanks @mceachen, I owe you several times over now :)

[yanickrochon]

Not sure this is being solved, I have tried several options, even examples listed in this issue, and nothing works for me. MySQL raises the error : ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '"2019-11-05T15:30:39.851Z"' for column 'createdAt' at row 1

It seems like something wraps the value with quotes....

[vvvzp]

this works for me:
new Date(Date.now())

[jokermt235]

Worked L)