go-pg/pg — pull request #6 by Babazka (2014-06-14 12:23:02+00:00)
Title: Treat timestamps without timezone as if they are in a Local timezone

Original discussion at https://github.com/vmihailenco/pg/pull/11

--------

Current version of `pg` resets timezones to UTC on encoding, and assumes UTC timezone when decoding timestamps without TZ suffix. This does not introduce any problems if we use a database column of type `timestamp with time zone`; however, when working with database columns of type `timestamp [without time zone]`, several problems may arise from the fact that values are being stored in the database as UTC time.

Postgres itself, when necessary, treats `timestamp without time zone` as if it contains a value in a local TZ, not in UTC ([see documentation](http://www.postgresql.org/docs/9.3/static/datatype-datetime.html)):
> Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time.

Such conversion, for instance, occurs when a `timestamp without time zone` is compared to `NOW()` (which returns a timestamp with timezone). So, UTC timestamps without time zone cannot be correctly compared to `NOW()`.

From this follows another point: a Postgres database design with `timestamp without time zone` columns is likely to assume local time zone for such columns because it would be more painful to work with them otherwise. Any person using a client such as `psql` would have to grapple with mental arithmetic of adding his local TZ offset to the values returned from query, or compose something more complex than `SELECT * FROM something WHERE NOW() BETWEEN start_time AND end_time` in order to get comparison right.

Software which works with `timestamp without time zone` values might also assume them to be in a local time zone. For example, standard Python `datetime.datetime.now()` function returns a timestamp containing local time, but lacking any TZ information. It is very straightforward to compare local-timezoned `timestamp without time zone` values returned from Postgres to the return value of `datetime.datetime.now()`; therefore it can be assumed that it is a common way of developing Python+Postgres systems. It will be not easy to migrate such systems from Python to `pg`-based Go code (which is something I'm currently involved in).

----------

The change I propose makes `pg` assume that `timestamp without time zone` columns contain values in a local time zone.

This change does not affect `timestamp with time zone`s, however it *breaks* the existing behaviour regarding `timestamp without time zone`s. If this pull request is merged, I suggest creating a `v2.0` tag after merging.