CODEWITHSUNDEEP
node.jspostgresqlormsequelize.js
John O
John O

Reputation: 5423

How do I make the default timestamp columns behave?

I'm trying to force these columns to do two things:

  1. Use "timestamp without time zone" (the backend is Postgres).

  2. Actually set the default on the backend to "now/utc".

        {
        // a bunch of column definitions
        createdAt: { type: DataTypes.DATE, defaultValue: sequelize.fn('now') }
    }, 
    {
        timestamps: false,
        comment: "this isn't even the real comment"
    }

The results are this:

CREATE TABLE IF NOT EXISTS "common"."images" ("id" VARCHAR(255) , "imageType"
VARCHAR(255), "mimeType" TEXT, "source" VARCHAR(255), "path" TEXT UNIQUE, 
"createdAt" TIMESTAMP WITH TIME ZONE DEFAULT now(), PRIMARY KEY ("id")); 
COMMENT ON TABLE "common"."images" IS 'blah blah blah.';

However, I can't find any documentation on how I could force this to be a timestamp-without-tz, or how I can make the default "now() at time zone 'utc'".

Are either of these possible?

If they are possible, can it be done without setting timestamps to false and manually defining the column as I have done?

Upvotes: 4

Views: 7814

Answers (4)

Hashir Venthodi
Hashir Venthodi

Reputation: 1

You can use:

defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')

Upvotes: 0

Siddharth Sunchu
Siddharth Sunchu

Reputation: 1084

You can use moment library for creating timestamp: 

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

Also, you can enable the timestamps as true in the model config to automatically update the timestamp whenever you create the row in DB.

  const orderDetails = sequelize.define('orderDetails', {
    id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
      field: 'id'
    },
    itemId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      field: 'item_id'
    }
    quantity: {
      type: DataTypes.FLOAT,
      allowNull: false,
      field: 'quantity'
    },
    itemDescription: {
      type: DataTypes.STRING(128),
      allowNull: false,
      defaultValue: ' ',
      field: 'item_desc'
    },
    createdAt: {
      type: DataTypes.NOW,
      allowNull: false,
      defaultValue: moment.utc().format('YYYY-MM-DD HH:mm:ss'),
      field: 'createdAt'
    },
    updatedAt: {
      type: DataTypes.NOW,
      allowNull: false,
      defaultValue: moment.utc().format('YYYY-MM-DD HH:mm:ss'),
      field: 'updatedAt'
    },
  }, {
    tableName: 'order_details',
    timestamps: true,
  });

Upvotes: 0

John O
John O

Reputation: 5423

Apparently there is a fairly elegant solution for the second half of this question. Sequelize presents a "literal" method that allows you to (among other things) make a default value exactly as you like.

{
    // a bunch of column definitions
    createdAt: { type: DataTypes.DATE, defaultValue: sequelize.literal("(now() at time zone 'utc')") }
},

I wrap it in double quotes because Postgres wants single quotes around the "utc" on the back end.

Of course, Yuri is correct in that there are no "without time zone" data types in Sequelize, nor any option to configure the Data type that way. I know that partial answers are discouraged here, but thought this might help others.

Upvotes: 2

Yuri Zarubin
Yuri Zarubin

Reputation: 11677

When you set up your sequelize instance, you can set options.timezone to UTC which sets the timezone for all sequelize connections, meaning every NOW() call would be in UTC. This would work with the default timestamps too. See the docs: http://sequelize.readthedocs.org/en/latest/api/sequelize/

Alternatively, you could modify your postgresql.conf and default the timezone there.

Upvotes: 1

Related Questions