使用psycopg2时带时区的日期对比结果不一致问题咨询

这不是psycopg2的缺陷，核心问题是psql会话与psycopg2连接的时区不一致。

原因分析 #

1. PostgreSQL中current_date是date类型，代表当前会话时区的日期；current_date AT TIME ZONE 'Asia/Kolkata'会返回timestamptz类型（带时区的时间戳），表示该日期在加尔各答时区的午夜时刻。
2. 执行current_date = current_date AT TIME ZONE 'Asia/Kolkata'时，PostgreSQL会隐式将左侧的date类型转换为timestamptz（使用当前会话时区的午夜时刻），再与右侧的timestamptz比较：
   • 若psql的会话时区是Asia/Kolkata，转换后的timestamptz与右侧完全一致，返回TRUE；
   • 若psycopg2连接的时区是UTC（psycopg2默认可能使用UTC，或连接时未指定时区），左侧date转换为UTC午夜的timestamptz，与右侧加尔各答午夜的timestamptz（对应UTC前一天的18:30）不相等，返回FALSE。
3. 转为date类型(current_date AT TIME ZONE 'Asia/Kolkata')::date时，是将右侧的timestamptz转换为当前会话时区的date，此时左右两侧都是date类型且值匹配，因此返回TRUE。
4. 使用UTC时区时，current_date AT TIME ZONE 'UTC'就是UTC午夜的timestamptz，与左侧date转换后的UTC午夜timestamptz完全一致，所以psql和psycopg2结果相同。

解决方法 #

• 方法一：在psycopg2连接时指定时区，比如在连接字符串中添加timezone=Asia/Kolkata：

  import psycopg2
  conn = psycopg2.connect(
      dbname="your_db",
      user="your_user",
      password="your_pass",
      host="your_host",
      timezone="Asia/Kolkata"
  )
  

• 方法二：连接后执行SQL语句设置时区：

  c = conn.cursor()
  c.execute("SET TIME ZONE 'Asia/Kolkata';")
  conn.commit()
  

内容的提问来源于stack exchange，提问作者Shh