Whenever I run some kind of database operation (IE update_attributes, save etc.), timestamps are in UTC

Question

When I run:

psql great_dev -c 'show timezone'

The following is returned:

TimeZone  
------------
US/Eastern
(1 row)

I want the timezone to be EST, so I think that is correct. The problem is that whenever I run some kind of database operation (IE update_attributes, save etc.), the timestamps are in UTC like this:

SQL (0.6ms)  UPDATE "schedules" SET "accepted" = $1, "updated_at" = $2 WHERE 
 "schedules"."id" = 46  [["accepted", "only_freelancer"], 
 ["updated_at", Mon, 02 Dec 2013 16:51:07 UTC +00:00]]

I am starting to implement several background jobs and it is essential that I understand what timezone Postgres is in.

Is Postgres in EST or UST?
If it is in UST, how do I convert it to EST and is it dangerous to do so?
I am going to allow a user to enter in timestamps from a select menu - should the menu list UTC times or EST times?

Postgres is stores time in unix timestamp read here about conversion http://www.postgresql.org/docs/9.1/static/datatype-datetime.html — Sam, Dec 02 '13 at 17:30
Rails prefers to have the database in UTC with all the time zone mangling in Rails-land. — mu is too short, Dec 02 '13 at 17:55

score 0 · Accepted Answer · edited May 23 '17 at 10:31

The default timestamp type in Postgres is timestamp without time zone.
To deal with this, employ the data type timestamp with time zone.

Or run your database with the time zone set to UTC and use timestamp [without time zone]. Be aware that the time zone setting of the session is relevant to what Postgres actually returns for your queries.

This related answer provides all the details you might need:
Ignoring timezones altogether in Rails and PostgreSQL

Whenever I run some kind of database operation (IE update_attributes, save etc.), timestamps are in UTC

1 Answers1