Datetime stored incorrectly in postgresql

[jwclark]

Issue type:

[x ] bug report

Database system/driver:

[ x] postgres

TypeORM version:

[ ] 0.2.6 (or put your version here)

Steps to reproduce or a small repository showing the problem:

The entity and test call below result in a timestamp in server time(rather than UTC) and without timezone information. This behavior exists as of pull request #1717 as far as I can tell.

@entity()
export class DateTest {

@PrimaryGeneratedColumn("uuid")
public id?: string;

@column()
public date?: Date;
}

testRepository.save({date: new Date()});

A potentially dangerous result of this bug is that two different servers in different timezones, but accessing the same database, could read the same entry and construct different timestamps(not just differences in timezone).

The comment here by @chriskalmar is not the actual behavior for postgres. As shown in the example above, new Date() is not stored as UTC.

[jwclark]

Some discussion on this in node-postgres as well: brianc/node-postgres#783

[pleerock]

Why is your server in different timezones? You shall use UTC in any server you have to avoid issues. Try to set process.env.TZ = "UTC"; in your app

[chriskalmar]

@jwclark I think you are constructing a column type without timezone, thus you don't get your timestamps stored in UTC.

From the postgres docs (https://www.postgresql.org/docs/9.6/static/datatype-datetime.html):

The SQL standard requires that writing just timestamp 
be equivalent to timestamp without time zone, 
and PostgreSQL honors that behavior.

So in order to overcome this, try to change the data type like this:

@Column("timestamp with time zone")

[filipjnc]

I had the same problem (also postgres) and I fixed it with the following attribute for all date columns:

  @UpdateDateColumn({ type: 'timestamptz' })
  updatedAt: Date;

Have a look at these supported column types by TypeORM:
http://typeorm.io/#/entities/column-types-for-postgres

Update: timestamptz is just an abbreviation of the timestamp with time zone mentioned above.

[jwclark]

While I would not deploy a production workload to a non-UTC server, it seems a bit fragile to me if changing the timezone of the server can cause these types of bugs.

As @chriskalmar and @filipjnc suggest, I've switched to 'timestamp with time zone' column type. Perhaps that should be the default for Date columns?

[pleerock]

While I would not deploy a production workload to a non-UTC server, it seems a bit fragile to me if changing the timezone of the server can cause these types of bugs.

dates and timezones is a fragile system itself. Dates are always complex and you simply need to know how to handle each case and you'll understand why it works this way. if changing the timezone of the server - you should not, that's the whole point. If you change it you'll have a fragile system to deal with. Why? Because its an issue with dates, not this or another tooling.

As @chriskalmar and @filipjnc suggest, I've switched to 'timestamp with time zone' column type. Perhaps that should be the default for Date columns?

no, they have different purposes. You use what you need in specific situations.

[bengotow]

Hey folks, just wanted to chime in. I think the "put your server in UTC" solution is fine (and the server should already be running in UTC), but this causes all sorts of problems during local development. If you have postgres and node running on your machine and the timezone of both is the SAME, you can safely use timezonetz to fix this problem, but the default timestamp used by postgres will produce the wrong results it looks like? Hmm :-/ Gotta love dates and times...