如何在C#中查询PostgreSQL-14的timestamptz字段指定日期数据

问题背景 #

我用的是PostgreSQL 14数据库，table1表里有个start_datetime字段，类型是timestamptz。执行select start_datetime from table1得到的数据示例如下：

2023-08-01 07:00:00.000 +0700
2023-08-01 07:00:00.000 +0700
2023-08-02 07:00:00.000 +0700
2023-08-03 07:00:00.000 +0700
2023-08-04 07:00:00.000 +0700
2023-08-04 07:00:00.000 +0700

我需要通过C#代码查询该字段对应2023-08-02和2023-08-03日期的数据（注：原提问中的2021应为笔误，数据示例均为2023年）。当前技术栈：

• .NET Core 6
• Dapper 2.0.143
• Npgsql 7.0.4
• Npgsql.EntityFrameworkCore.PostgreSQL 7.0.4

我试了下面的SQL语句，但查不到目标数据：

select
   t.start_datetime
from
    table1 t
where
   to_char(t.start_datetime at time zone 'UTC','YYYY-MM-DD') >= '2023-08-02'
    and to_char(c.start_datetime at time zone 'UTC','YYYY-MM-DD') <= '2023-08-03'

问题原因与解决方法 #

1. 直接错误：表别名写错了 #

SQL的where子句第二行用了c.start_datetime，但表的别名是t，这会直接触发语法错误，导致查询执行失败。

2. 性能与逻辑隐患：函数转换字段 #

用to_char把日期字段转成字符串再比较，会让PostgreSQL无法使用start_datetime上的索引，查询效率暴跌，还容易因为时区转换出现逻辑偏差。

正确的查询写法 #

方式1：直接用时间范围匹配（推荐）

利用PostgreSQL对timestamptz的时区处理能力，直接指定目标时区的时间范围，既能保证逻辑正确，又能用到索引：

SELECT t.start_datetime
FROM table1 t
WHERE t.start_datetime >= '2023-08-02T00:00:00+07:00'
  AND t.start_datetime < '2023-08-04T00:00:00+07:00';

这里用< '2023-08-04'而非<= '2023-08-03'，是为了完整包含2023-08-03当天所有时间（到23:59:59.999），避免遗漏或重复。

方式2：用date_trunc截断日期（不推荐，索引利用率低）

如果必须按日期截断后比较，可以用这个写法，但性能不如方式1：

SELECT t.start_datetime
FROM table1 t
WHERE date_trunc('day', t.start_datetime AT TIME ZONE 'UTC') >= '2023-08-02'::date
  AND date_trunc('day', t.start_datetime AT TIME ZONE 'UTC') <= '2023-08-03'::date;

C# Dapper代码实现 #

using Dapper;
using Npgsql;
using System;
using System.Collections.Generic;

public class DateTimeRecord
{
    public DateTimeOffset StartDateTime { get; set; }
}

public class DataHelper
{
    public IEnumerable<DateTimeRecord> GetRecordsByDateRange(DateTime startDate, DateTime endDate)
    {
        // 转换为+07时区的开始/结束时间
        var startTime = new DateTimeOffset(startDate, TimeSpan.FromHours(7));
        // 结束时间设为结束日期的下一天0点，确保覆盖当天所有时间
        var endTime = new DateTimeOffset(endDate.AddDays(1), TimeSpan.FromHours(7));

        using var conn = new NpgsqlConnection("你的数据库连接字符串");
        conn.Open();

        var sql = @"
            SELECT start_datetime AS StartDateTime
            FROM table1
            WHERE start_datetime >= @StartTime
              AND start_datetime < @EndTime";

        return conn.Query<DateTimeRecord>(sql, new { StartTime = startTime, EndTime = endTime });
    }
}

注意事项 #

• 确保数据库连接字符串或代码中时区处理一致，避免出现时间偏移
• 尽量不对查询字段做函数转换，保持原始类型比较，才能利用索引提升性能
• 原提问中的2021年日期为笔误，实际使用时替换为目标日期即可

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