Node应用中JavaScript Date结合PostgreSQL按月分组结果异常

问题背景 #

CSV原始交易数据：

08/08/2022;620,00
01/08/2022;-73,41
01/08/2022;600,00
01/08/2022;-341,36

存储配置 #

Prisma Schema定义：

model Transaction {
  id                Int      @id @default(autoincrement())
  amount            Float   
  date              DateTime @db.Date
}

对应PostgreSQL迁移语句：

ALTER TABLE "Transaction" ALTER COLUMN "date" SET DATA TYPE DATE;

数据存储现状 #

通过new Date(year, month, day)手动转换CSV日期为JS Date对象后，存储到数据库，查询返回的日期数据出现偏移：

[
    {
        "id": 9205,
        "date": "2022-08-07T22:00:00.000Z"
    },
    {
        "id": 9206,
        "amount": -73.41,
        "date": "2022-07-31T22:00:00.000Z"
    },
    {
        "id": 9207,
        "amount": 600,
        "date": "2022-07-31T22:00:00.000Z"
    },
    {
        "id": 9208,
        "amount": -341.36,
        "date": "2022-07-31T22:00:00.000Z"
    }
]

异常查询结果 #

执行Prisma原生分组查询：

const expensesByMonths: any[] = await this.prisma.$queryRaw`
  SELECT 
    date_trunc('month', date) as date_month,
    sum(amount)
  FROM "public"."Transaction"
  GROUP BY
    date_month
`;

得到的分组结果不符合预期：

{
    "expensesByMonths": [
        {
            "date_month": "2022-07-01T00:00:00.000Z",
            "sum": -414.77
        }
    ],
    "incomesByMonths": [
        {
            "date_month": "2022-07-01T00:00:00.000Z",
            "sum": 600
        },
        {
            "date_month": "2022-08-01T00:00:00.000Z",
            "sum": 620
        }
    ]
}

问题根源 #

问题出在时区偏移导致的日期存储偏差：

1. JavaScript的Date对象本质是带时区的UTC时间戳，默认使用本地时区创建实例。比如在UTC+2时区创建2022-08-01的Date对象，对应的UTC时间是2022-07-31T22:00:00.000Z。
2. PostgreSQL的DATE类型存储的是无时区的纯日期（年-月-日），写入时会截取UTC时间的日期部分，导致原本的8月1日被存储为7月31日，8月8日被存储为8月7日。
3. 后续分组查询时，date_trunc基于存储的UTC日期分组，自然得到错误的月份结果。

解决方法 #

1. 直接传递PostgreSQL兼容的纯日期字符串 #

跳过JS Date对象，把CSV的dd/mm/yyyy格式直接转换为yyyy-mm-dd字符串，Prisma会自动正确写入DATE字段：

// 处理CSV日期字符串 "08/08/2022"
const [day, month, year] = "08/08/2022".split("/");
const postgresDate = `${year}-${month}-${day}`; // 生成 "2022-08-08"

2. 若使用Date对象，强制基于UTC创建 #

创建Date对象时使用Date.UTC方法，确保UTC日期部分与目标日期一致：

const [day, month, year] = "08/08/2022".split("/");
// 注意：JS月份是0基，需减1
const date = new Date(Date.UTC(year, month - 1, day));
// 该对象的UTC日期为2022-08-08，写入PostgreSQL后会被正确存储

3. 修正现有数据的查询逻辑 #

如果已经存储了偏移数据，可以通过时区转换修正查询结果：

SELECT 
  date_trunc('month', (date AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Berlin')) as date_month,
  sum(amount)
FROM "public"."Transaction"
GROUP BY date_month;

将Europe/Berlin替换为你的实际本地时区。

PostgreSQL日期的正确存储形式 #

PostgreSQL的DATE类型仅存储ISO 8601格式的纯日期（yyyy-mm-dd），无时间和时区信息。正确的写入方式是：

• 传递yyyy-mm-dd格式的字符串
• 传递基于UTC创建、且UTC日期部分与目标日期一致的Date对象

避免传递带本地时区偏移的Date对象，防止UTC转换导致日期偏差。

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