Drizzle ORM中JS与PostgreSQL日期转换及时区异常问题

背景信息 #

• 本地时区：GMT+3
• Drizzle模型定义：

export const events = pgTable('events', {
    id: serial('id').primaryKey(),
    name: varchar('name', {length: 256}).notNull(),
    date: date('date').notNull(),
});

• 插入记录的SQL语句：

insert into events (name, date) values ('Foobar', '2023-03-30');

• 直接查询数据库的结果：

id |  name   |    date
----+---------+------------
100 | Foobar  | 2023-03-30

异常表现 #

1. 代码查询返回的日期值
   执行以下Drizzle查询代码：

const foo = await db.query.events.findFirst({
    where: eq(events.id, 100),
});
console.log(foo.date);

控制台输出：

2023-03-30T00:00:00.000Z

2. Drizzle Studio中显示的日期

2023-03-29T21:00:00.000Z

3. 过滤查询结果异常
   使用返回的foo.date执行以下查询：

await db.query.events.findFirst({
    where: lt(events.date, foo.date),
    orderBy: desc(events.date),
});

结果返回了id=100的目标记录，而非预期中更早日期的记录。

原因解析 #

核心问题出在PostgreSQL date类型与时区的转换逻辑：

• PostgreSQL的date类型本身是无时区的纯日期值，但客户端（Drizzle、Drizzle Studio）连接数据库时，会根据自身时区设置对日期进行转换。
• 插入的2023-03-30是GMT+3时区的日期，Drizzle将其转换为UTC标准时间时，就变成了2023-03-29T21:00:00.000Z（GMT+3的0点对应UTC前一天的21点）。
• 当用转换后的UTC时间戳与数据库date类型做lt比较时，PostgreSQL会把UTC时间戳转换为数据库默认时区的日期，此时2023-03-29T21:00:00.000Z在GMT+3时区仍为2023-03-30，与数据库存储的日期值相等，因此lt条件会匹配到这条记录。

解决办法 #

方案1：统一使用UTC时区（推荐） #

将数据库、应用代码的时区统一为UTC，从根源上避免时区转换带来的歧义。

修改PostgreSQL时区为UTC的步骤：

1. 编辑PostgreSQL配置文件postgresql.conf：
   找到timezone配置项，修改为：

timezone = 'UTC'

若未找到该配置项，直接在文件末尾添加该行即可。

2. 重启PostgreSQL服务：

• Linux系统：执行命令 sudo systemctl restart postgresql
• Windows系统：在系统服务管理器中找到PostgreSQL服务并重启

3. 验证设置是否生效：
   连接数据库后执行以下SQL：

SHOW timezone;

返回结果为UTC即表示设置成功。

方案2：在Drizzle中显式处理时区转换 #

若无法修改数据库时区，可在代码中显式处理时区转换：

• 插入数据时，确保将本地时区日期转换为数据库时区的日期值；
• 查询过滤时，将UTC时间戳转换为本地时区的日期字符串再进行比较：

// 将UTC时间转换为GMT+3时区的日期字符串
const localDate = foo.date.toLocaleDateString('en-CA', { timeZone: 'GMT+3' });
await db.query.events.findFirst({
    where: lt(events.date, localDate),
    orderBy: desc(events.date),
});

方案3：改用带时区的日期类型 #

将Drizzle模型中的date字段改为带时区的时间戳类型，让数据库存储带时区的完整时间信息：

export const events = pgTable('events', {
    id: serial('id').primaryKey(),
    name: varchar('name', {length: 256}).notNull(),
    date: timestamp('date', { withTimezone: true }).notNull(),
});

此方案需注意插入和查询时保持时区一致，避免出现新的转换问题。

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