Postgres does not support named timezones

[amajedi]

I'm using a raw sequelize query in node to execute a postgresql function which returns a query. The problem I'm having is that the result set obtained from running the query in pgAdmin does not match the result set returned in node when using the .query() method, even though the query in pgAdmin and the query in node are both exactly the same.

here's the query I'm performing:

SELECT * FROM foo('2015-07-01','2015-07-31');
This query returns 91875 rows in pgAdmin, however, in node using sequelize, running the following only returns 87500 rows:

models.sequelize.query("SELECT * FROM foo('2015-07-01','2015-07-31');")
.spread(function (results, metadata) {
console.log(results.length);
});
output: 87500

The rows which are excluded correspond to the last date 2015-07-31.

I found that I can obtain all the rows in node by increasing the 2nd date parameter to go beyond the end date. For example, if my goal is to get results which include 2015-07-31, setting the end date to be 2015-08-03 works. Although, this is masking the problem and not ideal.

I thought there must be something wrong with the WHERE clause in my stored procedure function. However, if that was the case, why do I get back the desired number of rows when I run the query though pgAdmin?

[janmeier]

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

[amajedi]

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.

[janmeier]

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

[amajedi]

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

[janmeier]

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

[amajedi]

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.

[amajedi]

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?

[janmeier]

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 ;)

[janmeier]

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

[amajedi]

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.

[amajedi]

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""

[amajedi]

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.

[janmeier]

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