Replicating Rails timezone "magic" in Postgres

Question

Is there a way I can have Postgres (I use Navicat but will demonstrate via the command line tool) behave in the same way that Rails does when it comes to timezones and UTC?

Rails seems to do some magic that makes Postgres behave in a UTC-fashion; whereas via the command line (or Navicat) it is using my system timezone. This is confusing because results vary based on whether I am executing the query in Ruby/Rails or in the console.

I still struggle with timezones in Postgres but having Rails and the console exihibit different behavior is maddening. Can I remedy this?

Here is one example (different output values):

-- in postgress:
# select ('2018-11-06' at time zone 'utc')::timestamptz;
        timezone
------------------------
 2018-11-06 08:00:00-08
(1 row)

-- in rails console
> ApplicationRecord.execute("select ('2018-11-06' at time zone 'utc')::timestamptz").first
   (0.5ms)  select ('2018-11-06' at time zone 'utc')::timestamptz
=> #<OpenStruct timezone="2018-11-06 00:00:00+00">

They are both formatted differently (-00 vs -08) and, even more confusing, they return actual different times.

The Postgres example is returning a result 16 hours ahead of the Rails one!

Answer 1

In my understanding, it is not a Rails magic, but Rails simply uses the UTC as the default. And that is why Rails can and does use timezone as opposed to timezonetz or something for the default database type for created_at etc.

You can modify the Rails default timezone as follows if you like (see the official reference):

# application.rb:
class Application < Rails::Application
  config.time_zone = 'Eastern Time (US & Canada)'
end

As for PostgreSQL, your result is not ahead; it seems your default time zone is somewhere in the American continent (8 hours behind the UTC). You can check it as follows:

postgres=> SET TIME ZONE 'America/New_York';
postgres=> select ('2018-11-06' at time zone 'UTC')::timestamptz;
        timezone        
------------------------
 2018-11-06 05:00:00-05
(1 row)

I don't know what Navicat does. But maybe check out your postgresql.conf. You may reset your timezone to the UTC if you want (and restart the postgreSQL server). See this answer to "postgres default timezone" for further detail.