Do you have a options.timezone set? http://docs.sequelizejs.com/en/latest/api/sequelize/#new-sequelizedatabase-usernamenull-passwordnull-options

Sequelize will set the timezone for the connection to utc by default

I have that left as default, I don't see how that could be affecting this as the query that sequelize is executing is output in the console as "SELECT * FROM foo('2015-07-01','2015-07-31');" it seems that only affects results coming back from the DB.

The query looks the same, but if the timezone for your pgadmin connection and the sequelize connection are not the same, the dates might be interpreted differently.

Its hard to say anything definitive without knowing your schema and your stored procedure.

Could you check what timezone your pgadmin connection is using SHOW TIME ZONE

It's using "US/Eastern" and my sequelize instance is using "+00:00" (utc)

That could definitely cause some issues - Try either running SET TIME ZONE INTERVAL "+00:00" in your pgadmin or setting options.timezone: "US/Eastern" and see if that gives the same results

I tried updating sequelize to use '+04:00' which didn't change anything. I then set that back to default (utc) and tried changing the database timezone to utc which also didn't change the results returned by sequelize.

If "Executing (default): SELECT * FROM foo('2015-07-01','2015-07-31');" is output in the console, doesn't that mean that exact command is being sent to the DB?

Sorry for being pedantic, but "us/eastern" is behind UTC ;)

Yes, the exact same query is sent to the server, but how that is interpreted might be different, since 2015-07-02 00:00:00 is not the same point in time in UTC and in US/Eastern.

Again, without knowing your schema, your data and your procedure its hard to say something thats not total guesswork.

Yes, sequelize does not alter the query in any way, what you see in the console is whats sent to the server, and we don't filter the results after the come back from the DB ;)

By the way, i meant, set time zone to UTC in pgadmin, then run the query again, in pgadmin

i see, now we're getting somewhere, I'm able to replicate now in pgAdmin. I set time zone to utc in pgadmin and now when i execute the query I get back 87500 rows, the same as with sequelize.

Seems like now that the DB is using the same timezone as sequelize, the results are the same.

I tried to change the timezone option for sequelize to "US/Eastern" but am getting the following error: "Unhandled rejection error: invalid input syntax for type interval: "US/Eastern""

The documentation states I can use that string instead of an interval, "Will also accept string versions of timezones used by moment.js (e.g. 'America/Los_Angeles'); this is useful to capture daylight savings time changes." I also tried upgrading to the latest version from 2.1 to no avail.

Yep, seems postgres does not handle named timezones. Should be as simple as checking moment.tz.zone() around here https://github.com/sequelize/sequelize/blob/master/lib/dialects/postgres/connection-manager.js#L106 and issuing either SET TIME ZONE zone or SET TIME ZONE INTERVAL zone HOUR TO MINUTE, depending on whether the zone is an utc offset or a named timezone

Did someone solve this? I have the same problem, I save data with timezone +00:00. But I want to set time zone -03:00 before execute a query. I have tried with PgAdmin and it is working but I can't do it with sequelize.