PostgreSQL + Ecto + Timex でタイムゾーン付き日時情報の扱い方

Question

###前提・実現したいこと

Ecto + Timex を使って、PostgreSQL の timestamp with timezone を扱いたいんですが、Model で持っている日時とデータベースに保存される時間がずれてしまうので、理由と対処法を知りたいです。

もっと別の方法があるよ、とかでもいいです。

参考

• https://github.com/bitwalker/timex_ecto
• https://hexdocs.pm/timex_ecto/Timex.Ecto.DateTimeWithTimezone.html

↑を読むと、カスタム複合タイプ datetimetz つくって対処するといいよって書いてある。

###発生している問題・エラーメッセージ

インタラクションに従って、データベースに datetimetz 型をつくって、Elixir からはTimex.Ecto.DateTimeWithTimezone 型の変数を入れられるようにしました。

CREATE TYPE datetimetz AS (
    dt timestamptz,
    tz varchar
);

Timex.Ecto.DateTimeWithTimezone な変数の中身をダンプすると以下のようになっています。

IO.inspect model.inserted_at
# {{{2017, 6, 2}, {11, 41, 57, 93698}}, "Asia/Tokyo"}

データベースには以下のように登録されています。

("2017-06-02 20:41:57.093698+09",Asia/Tokyo)

9時間ずれている。。実際の時刻は上の方です（今日の午前11時に実行しました）。

###該当のソースコード

migrationファイル

  def change do
    create table(:messages) do
      add :message, :text
      add :inserted_at, :datetimetz
    end
  end

モデルクラス

schema "messages" do
  field :message, :string
  field :inserted_at, Timex.Ecto.DateTimeWithTimezone
end

インサート

changeset = Message.changeset(%Message{}, %{message: message, inserted_at: Timex.now("Asia/Tokyo")})
Repo.insert(changeset)

###試したこと

すみません、何を試せばいいのかすら思いつかず。。

Ectoのコード読んだくらいです（でもよく分からなかった。。）

2017-06-04 13:00 追記

@mhashi さんのコメントにもあるように、インサートの際に UTC で入れないとダメなようです。
そもそも PostgreSQL の timestamp with timezone はタイムスタンプを UTC で持っており、それを、必要に応じて変換してから出力しているみたいなので、そこに JST の日時を入れてしまうと二重に +9 されてしまうようです。

たどり着いたコードとしては、
Ecto.Adapters.Postgres.Timestamp.encode/1 の中で

:calendar.datetime_to_gregorian_seconds({{2017,6,4},{13,0,0}})

のようにして秒に変換しているのですが、当然ながらここではタイムゾーンは考慮されてないです。このタプルは、単純に Timex.Ecto.DateTimeWithTimezone の値 {{{2017,6,4},{13,0,0}}, "Asia/Tokyo"} のタイムスタンプ部分を取ってきているにすぎません。

2017-06-04 13:00 追記ここまで

###補足情報(言語/FW/ツール等のバージョンなど)

Elixir 1.4
ecto 2.1
timex 3.1
timex_ecto 3.1

Answer 1

私はElixirで開発をしてないので間違っているかもしれませんが、こちらに Timex.Ecto.DateTimeWithTimezone の使い方についての詳しい説明があるのを見つけましたので、それに基づいて回答します。

• Using with Ecto - Using DateTimeWithTimezone

まず、DateTimeWithTimezoneは、PostgreSQLのtimestamptz (timestamp with timezone) にデータを格納することはできないようです。

上記のドキュメントに書かれている使い方を説明します。

この機能は現状PostgreSQLのみで使用できる。

対象のデータベースで必ず以下のSQLを実行しなければならない。これにより datetimetz 型が作られる。

CREATE TYPE datetimetz AS (
    dt timestamptz,
    tz varchar
);

あとは detatimetz 型を使ってテーブルを定義すればよい。

CREATE TABLE example (
  id integer,
  created_at datetimetz
);

以上です。試してみてください。

Comments

[退会済みユーザー]

@tatsuya6502 さん、回答ありがとうございます。
本文には書いてなかったですが、書いていただいたように、複合型を作ってます。
Timex.Ecto.DateTimeWithTimezone の値を Ecto 経由で入れる場合はこの方法しかないみたいなんですよね、現状。。

[tatsuya6502]

この通りにやっているのに期待通りに動かないということですか？ とすると、timex か timex_ecto のバグの可能性が考えられますよね。使用しているバージョンは、質問に書かれているように 3.1(.0) でしょうか？ それとも最新版ですか？（timex 3.1.15、timex_ecto 3.1.1）

GitHub Issueをざっと眺めた所、timex 3.0.6 より前のバージョだと、タイムゾーンが + の時に問題があった（parseエラーになった）みたいです。その問題自体は3.0.6で直っているそうですが、開発者の方は恐らく北米（UTC-0800 などのマイナス圏）に居るのでしょうから、プラス圏のテストが不十分なのかもしれません。なんらかのバグが残っている可能性もありそうです。

[退会済みユーザー]

mix.lock を確認したところ、最新版でした（timex 3.1.15、timex_ecto 3.1.1）。
パースエラーなどは起こっておらず、insert は正常に行われるのですが、データを見ると、+9時間ずれているという現象です。
Timex -> Ecto へデータを渡すときに値が変わっていないか、デバッグしてみます。

[tatsuya6502]

バージョンについて承知しました。すぐには無理なのですが、時間ができたら私も試してみようと思います。

[退会済みユーザー]

すみません、ありがとうございます！

Answer 2

timestamp with time zoneのカラムにUTCではなくローカルタイムを入れているため、
日本時刻(UTC+9)に二重に+9されているのだと思います。

("2017-06-02 20:41:57.093698+09",Asia/Tokyo)

これの時刻の末尾+9なのでUTC 11:41...で登録されてますね。

without time zoneでカラムを作るか、 Timex.now("Asia/Tokyo")を Timex.nowとすれば要求を満たせるかもしれません。

Comments

[退会済みユーザー]

@mhashi さん、回答ありがとうございます。
カラムを timestamp with timezone にして、UTC でインサートしたらうまくいきました。

```elixir
INSERT INTO "test" ("ts") VALUES ($1) RETURNING "id" [{{2017, 6, 2}, {5, 39, 48, 589422}}]
{:ok,
%SlackBot.Test{__meta__: #Ecto.Schema.Metadata<:loaded, "test">, id: 2,
ts: #<DateTime(2017-06-02T05:39:48.589422Z Etc/UTC)>}}
```

[退会済みユーザー]

しかし、なんとなくもやもやはしていて、アプリケーション内でも DB 内でも JST で日時を扱いたいんですが、Repo.insert のときだけ、UTC に戻してやらないといけないの？っていう素朴な疑問が残ります。

しかも、Timex.Ecto が推奨している DateTimeWithTimezone はタプルで表現すると、タイムスタンプとタイムゾーン名を持つタプルになるので、タイムスタンプには UTC の値が入り、タイムゾーン名には "Asia/Tokyo" と入っているっていう状態になってしまい、ちょっと気持ち悪いなぁ、と。

```
iex(11)> Timex.now("Asia/Tokyo")
#<DateTime(2017-06-02T15:10:51.979615+09:00 Asia/Tokyo)>
iex(12)> Timex.now("Asia/Tokyo") |> Timex.Ecto.DateTimeWithTimezone.dump
{:ok, {{{2017, 6, 2}, {15, 11, 19, 490587}}, "Asia/Tokyo"}}
```

最終的には Timex.Ecto.DateTimeWithTimezone を使わないでやるしかなさそうですね。

Answer 3

Elixir
{{{2017, 6, 2}, {11, 41, 57, 93698}}, "Asia/Tokyo"}

SQL
("2017-06-02 20:41:57.093698+09",Asia/Tokyo)

これらはズレていなく、一致しているかと思います。
SQLの方の

20:41:57.093698+09

の"+09"の部分は標準時間と比べて日本は9時間早いということを表しているので、データとしては

20:41:57.093698+09 = 11:41:57.093698

で、Modelの方と一致していると思います。
以下のサイトは参考になるかもしれません。
http://qiita.com/kidatti/items/272eb962b5e6025fc51e

Comments

[退会済みユーザー]

@Naoki_Tsuchi さん、回答ありがとうございます。

すみません、一致しているとのことですが、ちょっとピンとこないです。

試しに直接データベース操作して確認してみました。

```sql
testdb=# insert into test values(current_timestamp);
INSERT 0 1
testdb=# select * from test;
ts
-------------------------------
2017-06-02 14:29:14.588632+09
(1 row)
```

iex で確認すると、

```elixir
iex(1)> Timex.now()
#<DateTime(2017-06-02T05:48:05.476850Z Etc/UTC)>
iex(2)> Timex.now("Asia/Tokyo")
#<DateTime(2017-06-02T14:48:18.877824+09:00 Asia/Tokyo)>
```

UTC でインサートすると、 `2017-06-02 14:39:48.589422+09` と登録されたので、@mhashi さんからのコメントにもあるように、データ型は timestamp with timezone でつくりつつ、Elixir からは UTC 時間で入れるようにしないとダメってことでしょうか？（だとすると、Timex.Ecto.DateTimeWithTimezone の意味は。。