Timezone Support and PostgreSQL

[micheee]

I ran into issues when trying to find expired sessions.
As ISO Dates contain a time zone modifier but the default for a SQL timestamp is without time zone, the expires column may contain values that do not respect the timezone and are saved as UTC instead.

This makes comparing with des PostgreSQL current_timestamp difficult.

You can try it yourself with the following two queries:

The time value is provided by Javascript's:

new Date().toISOString() // 2015-09-08T08:28:29.910Z

This will then be inserted into the database:

-- To PostgresSQL this is 10:28 (correct local time)
INSERT INTO sessions (sid, sess,expired)
 VALUES ('Good','{}', '2015-09-08T08:28:29.910Z'::timestamp with time zone);

-- To PostgresSQL this is 08:28 (incorrect local time)
INSERT INTO sessions (sid, sess,expired)
 VALUES ('Bad','{}', '2015-09-08T08:28:29.910Z'::timestamp);

-- Comparing the dates now yields the following results:
SELECT * from sessions;
-- Good {}  2015-09-08 10:28:29.91
-- Bad  {}  2015-09-08 08:28:29.91

This is no problem unless you try to compare these dates using built-in functions, because they respect time zones and treat 'timestamps without time zone' as local times, in my case UTC+2 😕

I can try to send a pull request.

[gx0r]

Thanks! Pulled. If I understand correctly, without this, Postgres interprets timestamp as being local time, even though it is UTC (since toISOString gives a UTC time). With patch, postgres interprets it as UTC.

[micheee]

Yes, at least from what I understood, but I am by no means a postgres expert.
Thanks!
Michael

[sboehler]

Hi, I believe this is not properly fixed yet, at least in my case it doesn't work (v1.0.19)

When saving the 'expired' time stamp in the "set" method:

• new Date(expired).toISOString() returns a timestamp in UTC
• knex sends that timestamp to postgresql, and because the column "expired" in Postgres has no tz information, the tz is simply ignored and the timestamp is interpreted and saved as local time (CET in my case).

I discovered this because I set my session cookie to expire in 1 hour with my machine being running in CET, which currently is one hour ahead of UTC. The effect was that all my sessions instantly expired.

I believe the proper fix is to set the 'expired' column for postgresql to "timestamp with time zone" by removing the "true" parameter on line 101 (version 1.0.19). Only then postgresql will consider the time zone information in the ISO string.