In a Nodejs application I'm using the Sequelize ORM to write records to a mysql database. Every table has a createdAt field by default, but that only records datetime with seconds:

mysql> SELECT createdAt FROM ticks LIMIT 3;
+---------------------+
| createdAt           |
+---------------------+
| 2017-11-08 16:34:21 |
| 2017-11-08 16:34:15 |
| 2017-11-08 16:34:27 |
+---------------------+
3 rows in set (0.00 sec)

Since I'm running a very time sensitive service I would also like to record milliseconds. In the docs I found the data types, which includes:

Sequelize.DATE(6)  // DATETIME(6) for mysql 5.6.4+. Fractional seconds support with up to 6 digits of precision

I never explicitly write the createdAt field though (Sequelize does that automagically), so I'm not sure how I could make that write milliseconds.

Could anybody point me in the right direction to save records with the createdAt field using millisecond precision? All tips are wele!

In a Nodejs application I'm using the Sequelize ORM to write records to a mysql database. Every table has a createdAt field by default, but that only records datetime with seconds:

mysql> SELECT createdAt FROM ticks LIMIT 3;
+---------------------+
| createdAt           |
+---------------------+
| 2017-11-08 16:34:21 |
| 2017-11-08 16:34:15 |
| 2017-11-08 16:34:27 |
+---------------------+
3 rows in set (0.00 sec)

Since I'm running a very time sensitive service I would also like to record milliseconds. In the docs I found the data types, which includes:

Sequelize.DATE(6)  // DATETIME(6) for mysql 5.6.4+. Fractional seconds support with up to 6 digits of precision

I never explicitly write the createdAt field though (Sequelize does that automagically), so I'm not sure how I could make that write milliseconds.

Could anybody point me in the right direction to save records with the createdAt field using millisecond precision? All tips are wele!

• javascript
• mysql
• node.js
• datetime
• milliseconds

Share Follow asked Dec 11, 2017 at 12:08 kramer65kramer65 54.1k133133 gold badges331331 silver badges526526 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 3

I updated the model and created a migration to utilize milliseconds:

Model:

import Sequelize from 'sequelize'

class Ticks extends db.instance.Model {
  static init(sequelize) {
    return super.init({
      .
      .
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE(6)
      }
    })
  }
}

Migration:

export default {
  up: (queryInterface, Sequelize) => queryInterface
    .changeColumn('ticks', 'createdAt', {
      allowNull: false,
      type: Sequelize.DATE(6)
    }),
  down: (queryInterface, Sequelize) => queryInterface
    .changeColumn('ticks', 'createdAt', {
      allowNull: false,
      type: Sequelize.DATE
    })
}

(Using ES6 syntax)

And with those changes I was able to get the milliseconds working (although it seems to be only to 3 places, even though I specified 6)

You can define createdAt explicitly in your model, for example:

id: {
    type: Sequlieze.INTEGER(11).UNSIGNED,
    autoIncrement: true,
    primaryKey: true
},
...
...
createdAt: {
    type: schema.DATE(6),
    allowNull: true,
    defaultValue: Sequelize.fn('NOW')
}

Or rename it by:

created_ts: {
    type: schema.DATE(6),
    allowNull: true,
    defaultValue: Sequelize.fn('NOW')
}, {
    createdAt: created_ts
}