PostgreSQLのバージョンは9.6, DBのタイムゾーンはUTCとします。
結論
users.created_at のような timestamp without time zone 型のカラムがある場合、次のようにするとJSTの時刻を取得できます。
timezone('JST', users.created_at::timestamptz)
まず、timestamp without time zone 型のカラムである users.created_at の値を timestamp with time zone 型の略記である timestamptz 型にキャストしています。:: は[PostgreSQLにおける型キャストの記法](4.2. 評価式)です。そして、タイムゾーン変換の構文 timestamp AT TIME ZONE zone と等価である timezone 関数でJSTに変換しています。
正しくない例
次のようにするとできません。
timezone('JST', users.created_at)
この場合、 users.created_at が 2018-08-10 00:00:00 だと timezone('JST', users.created_at) は 2018-0８-09 15:00:00 となり、UTCの2018-08-10 00:00:00におけるJSTの時間を期待して9時間進んでほしいところが、9時間戻ってしまいます。
これは、JSTとして users.created_at を解釈したあとに、ローカル時間（この場合UTC）の時間はいつか、というロジックで値を表示するからです。つまり、この場合 2018-08-10 02:46:58 をJSTとして解釈し、それはUTCでは 2018-0８-09 17:46:58 である、というふうになっています。これは timestamp without time zone 型の値に対する timezone 関数は「与えられた時間帯なしタイムスタンプを指定された時間帯にあるとして取り扱う」という仕様によります。
そこで、users.created_at を timestamp with time zone 型にキャストします。すると、この場合は timezone 関数が「与えられた時間帯付きタイムスタンプを新規の時間帯に、時間帯の指定なく変換する」という仕様になります。これによって、JSTへ変換したタイムスタンプを表示することができます。
参考
- PostgreSQL 9.6.5文書