How to prevent Sequelize from converting Date object to local time

Question

I am using sequelize for a node project. It's connecting to a Postgres databsae, which contains a table with a DATE field (unlike TIMESTAMP WITH TIMEZONE, DATE has not time data).

In the code I'm modeling the date using a javascript Date object, which stores the time as UTC midnight. When I use that to insert a record into the table using that Date object, sequelize is apparently coverting it to local time first because the records are always 1 day behind. So if I want to insert 2000-10-31 into the database I end up with 2000-10-30. I am in UTC-5.

How do I tell sequelize to not convert the Date to a local time before inserting into the database?

Here is some sample code. I also created a repository if you want to run it yourself.

var Sequelize = require('sequelize');

const sequelize = new Sequelize('testdb', 'postgres', '???', {
    host: 'localhost',
    dialect: 'postgres'
});

TestTable = sequelize.define('date_test',
    {
        id: {
            primaryKey: true,
            type: Sequelize.INTEGER,
            autoIncrement: true
        },

        someDate: {
            field: 'some_date',
            type: Sequelize.DATEONLY
        }
    },
    {
        timestamps: false,
        freezeTableName: true
    }
);

// midnight UTC on Halloween 🎃
var date = new Date(Date.UTC(2000, 9, 31));

// converts to local time resulting in 2000-10-30
TestTable.create({ someDate: date })
    .then(function() {
        // also apparently converts to local time resulting in 2000-10-30
        return TestTable.create({ someDate: date.toUTCString() });
    })
    .then(function() {
        // convert to string, definitely works but seems unnecessary
        var strDate = date.getUTCFullYear() + '-' + pad2(date.getUTCMonth() + 1) + '-' + pad2(date.getUTCDate());
        return TestTable.create({ someDate: strDate });
    })
    .then(function() {
        // cerate a new local Date, also works but seems hacky
        var newDate = new Date(date.getUTCFullYear(), date.getUTCMonth(), date.getUTCDate());
        return TestTable.create({ someDate: newDate });
    })
    .then(function() {
        process.exit(0);
    });


function pad2(n) {
    if (n.length === 1) {
        return '0' + n;
    }

    return n;
}

Answer 1

The problem is that the date is created as UTC time, but because DATEONLY has no awareness of the timezone, it formats the Date object "as is" (in local time) with format YYYY-MM-DD (using moment.js - see here in source).

For a DATEONLY you can just do this:

var date = new Date(2000, 9, 31);

That will insert the date correctly.

Programmers seldom say this, but for once you were thinking too much about timezones!

---

OLD INCORRECT ANSWER

It depends on how you are checking the value, but javascript and postgresql are converting it to your local timezone for display.

Sequelize uses a TIMESTAMP WITH TIMEZONE type for a date field (source).

In the postgresql docs it says:

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

If you are outputting the time value in postgresql, try converting it to UTC. If you are are outputting it in javascript, try date.toUTCString().

The reason your hacks appear to work is because they are actually storing 2000-11-01 05:00 but when you inspect the value, it is converted into your local time zone.