Rails
PostgreSQL

Rails の Time#beginning_of_day と同様のことを、タイムゾーンを考慮しつつ PostgreSQL で行う

前提

Rails のタイムゾーンが Asia/Tokyo であり、かつ DB のタイムゾーンが UTC である。

Time.zone.to_s
=> "(GMT+09:00) Tokyo"

ActiveRecord::Base.connection.execute('SHOW TIME ZONE').first
#=> { "TimeZone" => "UTC" }

はじめに

例えば Rails に Character というモデルがあり、本日更新された Character レコードを取得したい場合、次の方法があります。

now = Time.zone.now
#=> Thu, 12 Oct 2017 14:48:11 JST +09:00

beginning_of_today = now.beginning_of_day
#=> Thu, 12 Oct 2017 00:00:00 JST +09:00

beginning_of_tomorrow = now.tomorrow.beginning_of_day
#=> Fri, 13 Oct 2017 00:00:00 JST +09:00

Character
  .where('characters.updated_at >= ?', beginning_of_today)
  .where('characters.updated_at < ?', beginning_of_tomorrow)
  .to_sql
#=> SELECT "characters".* FROM "characters"
#   WHERE (characters.updated_at >= '2017-10-11 15:00:00'
#      AND characters.updated_at < '2017-10-12 15:00:00')

本日の開始時刻以降かつ明日の開始時刻より前に更新されたレコードという条件で絞り込んでいます。このとき SQL に含まれる時刻がそれぞれ 9 時間前に自動的に変換されています。つまり ActiveRecord が SQL を生成するときに、タイムゾーンを自動的に UTC に変換してくれていることが分かります。

これと同様のことを Rails (ActiveSupport の Time#beginning_of_day や ActiveRecord) を利用せずに PostgreSQL の SQL のみで完結させるのが今回の目的です。

方法

SELECT
  *
FROM
  characters
WHERE
  characters.updated_at >= ((now()::date)::timestamptz at time zone 'utc') -- beginning_of_today
  AND characters.updated_at < ((now()::date + 1)::timestamptz at time zone 'utc') -- beginning_of_tomorrow
;
  • now() 関数を date 型にキャストすることで時刻情報を取り除き、再度 timestamp with time zone 型に戻す。
  • date 型の値に +1 することで明日の値を取得できる。
  • 念のためデータベースの現在の (SELECT 文発行時の) タイムゾーンが UTC 以外になっている場合でも問題ないように UTC に変換する。
SET TIME ZONE 'Japan';

SELECT
  (now()::date)::timestamptz AS beginning_of_today,
  (now()::date + 1)::timestamptz AS beginning_of_tomorrow,
  (now()::date)::timestamptz at time zone 'utc' AS beginning_of_today_utc,
  (now()::date + 1)::timestamptz at time zone 'utc' AS beginning_of_tomorrow_utc
;
   beginning_of_today   | beginning_of_tomorrow  | beginning_of_today_utc | beginning_of_tomorrow_utc
------------------------+------------------------+------------------------+---------------------------
 2017-10-12 00:00:00+09 | 2017-10-13 00:00:00+09 | 2017-10-11 15:00:00    | 2017-10-12 15:00:00
(1 row)

参考