postgresql connector date timezone inconsistencies

[Andy Kellerstrass]

Hello,

A date field (callStartDate) is being converted to server local time somewhere in the ORM. We save the date as UTC ("2015-01-01T00:00:00.000Z" for this test) to the datebase, and it is stored as such in our timestamp without time zone field (2015-01-01 00:00:00). When the model object is returned by the create method, it has the correct date for this case as Wed Dec 31 2014 18:00:00 GMT-0600 (CST) in our local time. This is the same timestamp that we sent to the database.

When we do a find on the model, the date is returned as a completely different timestamp. This is shown in the gist that is posted below. The ORM always converts the dates that are sent to the database to UTC, so you would expect it to assume the dates that are stored inside are in UTC. In this case it is assuming that the data is stored in the server's local timezone of CST (Thu Jan 01 2015 00:00:00 GMT-0600 (CST)). This is a different point in time.

I know that we should be saving the timestamps as "timestamp with time zone" instead of without, but we are standardizing on UTC for storage and we would just set up a constraint to verify that only UTC data is being saved anyway. 

When we DO have the column set to "timestamp with time zone", regardless of the time string sent to the database it will save the timestamp as the server's local time zone, which is also incorrect. It will save "2015-01-01T00:00:00.000Z" as "2015-01-01 00:00:00-06", which are two different points in time.

Sorry for the wall of text, but we are kind of at a loss at this point on how to proceed. Any advice on this matter would be appreciated.

Connector and model debug output here:

https://gist.github.com/zero5100/0c574f393da246ae121e

Version Info:

    "loopback": "^2.14.0"

    "loopback-boot": "^2.6.5"

    "loopback-connector-postgresql": "^2.1.0"

    "loopback-datasource-juggler": "^2.19.0"

[Raymond Feng]

This is a tricky situation, not only for postgresql. MySQL has similar issues. Let’s think about the following round trip:

1. Client 1 in PDT uses a LoopBack model try to create a new instance with `created` set to new Date(). The JS Date object has information about the time zone.

2. The `created` value is passed to a DB connector, which will be responsible for calling a SQL statement with the `created`. It needs to convert the Date object to a string (timezone information could be lost depending on the DB). Theoretically the connector can either read the session/system timezone from the DB and use it for the conversion or set up the DB connection to force a timezone such as GMT.

3. The DB decides an internal format to store the date, for example, MySQL stores it as UTC.

4. Client 2 in EDT tries to read the same record with `created` from the DB.

I think the key is to make sure connector implementations to either use or force the timezone for the DB connection. At the moment, we use DB side to_timestamp() in SQL. It probably ignore the timezone.

Thanks,

---

Raymond Feng

Co-Founder and Architect @ StrongLoop, Inc.

StrongLoop makes it easy to develop APIs in Node, plus get DevOps capabilities like monitoring, debugging and clustering.

--
You received this message because you are subscribed to the Google Groups "LoopbackJS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to loopbackjs+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/loopbackjs/2c68002a-a67f-473b-8c9c-b0c8929c7ec3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Andy Kellerstrass]

Thank you for the response.

To fix this issue, we changed the default time zone to UTC for the application postgresql user.

ALTER USER username SET TIMEZONE TO 'UTC';

This causes the database to assume that the dates should be stored and retrieved in UTC for any query that user performs.

[Kesav Kumar Kolla]

Yes this is what I had to do in my case.  I wrote a custom boot step in loopback to setup the correct timezone into juggling data source.  loopback postgresql connector uses pooled connections that are available in node-postgress library, so it opens/closes connection for each query.  I had to patch the node-postgres library to support event emitting for each new physical connection that opens with database.  I handle that event and whenever a new connection made to database, I set the time zone session property on to the connection.

This is working well for me.

Regards,

-Kesav

[Andy Kellerstrass]

That does sound like a much better solution. Setting the time zone for the session would be a lot easier to manage since it is in code and doesn't require us to remember to set the database user on every new server to UTC. Is the patch you made to node-postgres available anywhere? We will probably stick with the time zone set on the user level for now, but this sounds like a much better option for us to implement down the line.

Thanks,

Andy

[Erik Fantasia]

Just ran across this issue, and found a fairly ugly hack which will get to_timestamp() to format the time using the postgres server's timezone. You can append this on line 454 in the LoopBack postgres connector:

AT TIME ZONE \'UTC\' AT TIME ZONE (extract(TIMEZONE FROM now())/3600)::varchar(32)

Note this will not work with timezones that have non-integer offsets, such as Nepal (UTC+5:45)

[Erik Fantasia]

Or even better:

 AT TIME ZONE current_setting('timezone') AT TIME ZONE 'UTC';

[Giovanni Lela]

I encountered similar problems with dates in mysql and opened an issue... maybe it could be related?  https://github.com/strongloop/loopback-connector-mysql/issues/101

[Erik Fantasia]

You can see how we patched the postgres connector here:

https://github.com/kindlycare/loopback-connector-postgresql/blob/kindly/lib/postgresql.js#L454

[Giovanni Lela]

We fixed the (same?) problem on mysql, but in the fromColumn function: https://github.com/linkmesrl/loopback-connector-mysql/blob/develop/lib/mysql.js#L390

Il giorno mercoledì 10 giugno 2015 23:32:56 UTC+2, Andy Kellerstrass ha scritto:

[Erik Fantasia]

The only issue I would see with that Giovanni is that your solution assumes the LoopBack server is operating on the same offset as your MySQL server.

--
You received this message because you are subscribed to a topic in the Google Groups "LoopbackJS" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/loopbackjs/aIZZko3uL2s/unsubscribe.
To unsubscribe from this group and all its topics, send an email to loopbackjs+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/loopbackjs/d8e66a2c-5d74-4ddb-996c-f1cfcc5c1f2b%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Erik Fantasia

https://www.kindlycare.com

[Raymond Feng]

I’ll propose to fix the issue as follows:

return new ParameterizedSQL({

      sql: 'TIMESTAMP WITH TIME ZONE \''+iso+'\'',

      params: []

    });

It will produce the SQL as follows:

INSERT INTO "public"."account"("email","createdat","lastmodifiedat") VALUES($1,TIMESTAMP WITH TIME ZONE '2015-06-12T15:54:18.159Z',TIMESTAMP WITH TIME ZONE '2015-06-12T15:54:18.159Z') RETURNING "id"

The bottom line is that LoopBack will always send date in UTC format and have postgres to store it as UTC timezone. Can you folks confirm?

Thanks,

---

Raymond Feng

Co-Founder and Architect @ StrongLoop, Inc.

StrongLoop makes it easy to develop APIs in Node, plus get DevOps capabilities like monitoring, debugging and clustering.

You received this message because you are subscribed to the Google Groups "LoopbackJS" group.

To unsubscribe from this group and stop receiving emails from it, send an email to loopbackjs+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/loopbackjs/CAAKa9XKQBOcQ6%2BXopX2rJWns3PSHNTf2CrSTcOtnNM%3DQ2A12Ew%40mail.gmail.com.

[Erik Fantasia]

Hi Raymond,

That's correct. The postgres connector uses toISOString() which will always send the time in Zulu/UTC. Your solution works.

Erik

[Andy Kellerstrass]

That works perfectly for me Raymond. Would this be a better way to write it?

    return new ParameterizedSQL({

      sql: '?::TIMESTAMP WITH TIME ZONE',

      params: [iso]

    });

Thanks,

Andy

[Raymond Feng]

Fixed per Andy’s suggestion and released as loopback-conne...@2.2.0.

Thanks,

---

Raymond Feng

Co-Founder and Architect @ StrongLoop, Inc.

StrongLoop makes it easy to develop APIs in Node, plus get DevOps capabilities like monitoring, debugging and clustering.

To view this discussion on the web visit https://groups.google.com/d/msgid/loopbackjs/222a6725-a371-4e43-9925-08b73507c603%40googlegroups.com.

[Giovanni Lela]

Erik, in the mysql connector dates are stored as UTC dates https://github.com/strongloop/loopback-connector-mysql/blob/master/lib/mysql.js#L253, that piece of code was meant to convert them back in the server timezone.. maybe we had different issues