0

I'm notice that when I replicate data between 2 server with different time zone, the time data in PostgreSQL is shifted when I load it to a client (in my case, a Rails console). I thought all persisted data should be converted to UTC but it doesn't seem to be the case.

Is there any thing to config when setting up replication so the time wouldn't mess?

Phuong Nguyen
  • 703
  • 1
  • 12
  • 27
  • Is the data type `timestamp` or `timestamp with time zone` (`timestamptz`)? Rails should really be setting `TimeZone` to `UTC` for each connection. – Craig Ringer Aug 15 '14 at 00:58
  • It's `datetime` – Phuong Nguyen Aug 15 '14 at 03:59
  • That isn't a postgresql type. What is the database type it is mapped to? – Craig Ringer Aug 15 '14 at 04:41
  • Sorry, it's mapped to `timestamp without time zone` – Phuong Nguyen Aug 15 '14 at 09:12
  • In that case I suggest changing the PostgreSQL timezone, if Rails doesn't do so its self. `timestamp without time zone` is a time in *local time*. So if the local time is different, then .... so is the timestamp. – Craig Ringer Aug 15 '14 at 09:15
  • This is confusing here. I think Rails (ActiveRecord) stores date time using its own timezone, then load back in the same timezone, then as long as PostgreSQL return the same value, all shall be fine, shan't it? It is messed up only if PostgreSQL return X in a server and return X' in another server. – Phuong Nguyen Aug 26 '14 at 06:53

0 Answers0