【Elixir】使用Ecto操作PostgreSQL数据库，时区

1. 配置数据库

查看当前数据库时区：
SHOW TIMEZONE;
设置为中国的时区：
SET TIMEZONE TO 'Asia/Shanghai';

2. 配置字段

migrations设置如下：

def change do
    create table(:people) do
      add :first_name, :string, size: 100
      add :last_name, :string, size: 100
      add :age, :integer, default: 0
      add :create_time, :timestamptz, default: fragment("now()")
   end
end

schema设置如下：

schema "people" do
    field(:first_name, :string)
    field(:last_name, :string)
    field(:age, :integer, default: 0)
    field(:create_time, :utc_datetime_usec)
end

3. 编写时间转换方法

defmodule Friends.DatetimeFormat do
  @spec to_local_datetime(NaiveDateTime.t()) :: {:error, nil} | {:ok, DateTime.t()}
  @doc """
  转换NaiveDateTime(without a time zone)为中国时区的DateTime
  """
  def to_local_datetime(native_datetime) when is_struct(native_datetime, NaiveDateTime) do
    with {:ok, datetime} <- DateTime.from_naive(native_datetime, "Etc/UTC") do
      DateTime.shift_zone(datetime, "Asia/Shanghai")
    end
  end

  def to_local_datetime(_), do: {:error, nil}

  @doc """
  格式话DateTime为字符串
  """
  @spec format_datetime(DateTime.t()) :: {:error, String.t()} | {:ok, String.t()}
  def format_datetime(datetime) when is_struct(datetime, DateTime) do
    Timex.format(datetime, "%F %T", :strftime)
  end

  def format_datetime(_), do: {:error, ""}
end

备注

timex_ecto 3.4.0 兼容 ecto ~> 2.2，不兼容最新的## ecto 3.10.2