CODEWITHSUNDEEP
mysqlnode.jsdatetimetimestampsequelize.js
ılǝ
ılǝ

Reputation: 3508

sequelize.js TIMESTAMP not DATETIME

In my node.js app I have several models in which I want to define TIMESTAMP type columns, including the default timestamps created_at and updated_at.

According to sequelize.js' documentation, there is only a DATE data type. It creates DATETIME columns in MySQL.

Example:

var User = sequelize.define('User', {
... // columns
last_login: {
            type: DataTypes.DATE,
            allowNull: false
        },
...
}, { // options
        timestamps: true
});

Is it possible to generate TIMESTAMP columns instead?

Upvotes: 46

Views: 108980

Answers (6)

Siddharth Sunchu
Siddharth Sunchu

Reputation: 1084

You can also use the moment for creating a timestamp:

const moment = require('moment-timezone');

    createdAt: {
      type: DataTypes.NOW,
      allowNull: false,
      defaultValue: moment.utc().format('YYYY-MM-DD HH:mm:ss'),
      field: 'createdAt'
    },

Upvotes: 4

jithu
jithu

Reputation: 2467

instead of 

 type: DataTypes.DATE,

use below code

      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.fn('NOW'),
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.fn('NOW'),
      },

Upvotes: 3

Renish Gotecha
Renish Gotecha

Reputation: 2522

In my case i create model like below

module.exports = (sequelize, type) => {
    return sequelize.define('blog', {
        blogId: {
          type: type.INTEGER,
          primaryKey: true,
          autoIncrement: true
        },
        text: type.STRING,
        createdAt:{
            type: 'TIMESTAMP',
            defaultValue: sequelize.literal('CURRENT_TIMESTAMP'),
            allowNull: false
        },
        updatedAt:{
            type: 'TIMESTAMP',
            defaultValue: sequelize.literal('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'),
            allowNull: false
        }
    })
}

enter image description here

Upvotes: 5

alucic
alucic

Reputation: 13293

Just pass in 'TIMESTAMP' string to your type

module.exports = {
  up: function (queryInterface, Sequelize) {
    return queryInterface.createTable('users', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
        created_at: {
        type: 'TIMESTAMP',
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
        allowNull: false
      },
      updated_at: {
        type: 'TIMESTAMP',
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP'),
        allowNull: false
      }
    });
  }
};

Upvotes: 61

Nate Stone
Nate Stone

Reputation: 531

According to the Sequelize Documentation, you can set a defaultValue of Sequelize.NOW to create a timestamp field. This has the effect but relies on Sequelize to actually populate the timestamp. It does not create a "CURRENT_TIMESTAMP' attribute on the table. 

var Foo = sequelize.define('Foo', {
    // default values for dates => current time
    myDate: { 
         type: Sequelize.DATE, 
         defaultValue: Sequelize.NOW 
    }
});

So, this does accomplish the end goal of having a timestamp field, but it is controlled through Sequelize and not through the actual database engine.

It also appears to work on databases that do not have a timestamp functionality, so that may be a benefit.

Reference URL: http://sequelize.readthedocs.org/en/latest/docs/models-definition/#definition

Upvotes: 5

Andrey Borisko
Andrey Borisko

Reputation: 4609

What I did with sqLite is extended DataTypes with my custom sql logic for TIMESTAMP and it worked fine. I'm not 100% sure how the sql syntax should look for MySQL but my guess it's something similar to what I have. Look at example:

function (sequelize, DataTypes) {

    var util = require('util');
    var timestampSqlFunc = function () {
        var defaultSql = 'DATETIME DEFAULT CURRENT_TIMESTAMP';
        if (this._options && this._options.notNull) {
            defaultSql += ' NOT NULL';
        }
        if (this._options && this._options.onUpdate) {
            // onUpdate logic here:
        }
        return defaultSql;
    };
    DataTypes.TIMESTAMP = function (options) {
        this._options = options;
        var date = new DataTypes.DATE();
        date.toSql = timestampSqlFunc.bind(this);
        if (!(this instanceof DataTypes.DATE)) return date;
        DataTypes.DATE.apply(this, arguments);
    };
    util.inherits(DataTypes.TIMESTAMP, DataTypes.DATE);

    DataTypes.TIMESTAMP.prototype.toSql = timestampSqlFunc;

    var table = sequelize.define("table", {
        /* table fields */
        createdAt: DataTypes.TIMESTAMP,
        updatedAt: DataTypes.TIMESTAMP({ onUpdate: true, notNull: true })
    }, {
        timestamps: false
    });

};

All you need to do for MySQL is to change SQL type generation in timestampSqlFunc function, so for example defaultSql variable would be 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP'

Upvotes: 1

Related Questions