mysql - JSON 响应的日期时间更改

Question

我正在从 project_stage 命名表中选择项目的开始和结束日期。

这是表格元素

stage_start_date    stage_end_date       planned_start_date   planned_end_date
2019-01-28 10:12:01  2020-12-08 09:05:28 2019-01-12 01:01:00  2020-12-01 00:00:00

这里的数据类型是DATETIME

这是代码

SELECT ps.stage_start_date AS actual_start_date, 
       ps.stage_end_date AS actual_end_date, 
       ps.stage_planned_start_date AS planned_start_date, 
       ps.stage_planned_end_date AS planned_end_date 
FROM project_stage AS ps 
JOIN map_project_user AS mpu ON mpu.user_id = 22 AND mpu.project_id = 1 AND mpu.tenant_id = ps.tenant_id AND ps.project_id = mpu.project_id;

JSON 响应结果

{
   "actual_start_date": "2019-01-28T04:42:01.000Z",
   "actual_end_date": "2020-12-08T03:35:28.000Z",
   "planned_start_date": "2019-01-11T19:31:00.000Z",
   "planned_end_date": "2020-11-30T18:30:00.000Z"
}

这里的日期时间正在改变，它不是表中的实际日期时间，为什么日期在结果上改变。这是预期的输出

预期结果

{
   "actual_start_date": "2019-01-28 10:12:01",
   "actual_end_date": "2020-12-08 09:05:28",
   "planned_start_date": "2019-01-12 01:01:00",
   "planned_end_date": "2020-12-01 00:00:00"
}

MYSQL DATATYPE 是 DATETIME。数据库 timezone 是 UTC 并且系统 timezone 也显示 UTC，我如何隐藏这个对应于用户系统的timezone

Answer 1

根据数据示例，Timezone 问题似乎出现在将 SQL 结果转换为 JSON 的代码中。由于数据库和JSON的时间差是-05:30，看来“JSON转换器”假设SQL查询的结果是IST(UTC+05:30)，将时间转换为UTC(减去5 :30).

正确的修复应该在“JSON 转换器”中完成。但是，如果需求是通过修改 SQL 查询来实现“更正日期”，则可以使用 CONVERT_TZ (dt, from_tz, to_tz) 函数。这增加了 +5:30 和“JSON 转换器”减去 5:30 之后导致时间不变。

类似的东西:

SELECT DATE_FORMAT(CONVERT_TZ(ps.stage_start_date, '+00:00', '+05:30'), "%Y-%m-%d %H:%i:%s") AS actual_start_date, 
       DATE_FORMAT(CONVERT_TZ(ps.stage_end_date, '+00:00', '+05:30'), "%Y-%m-%d %H:%i:%s") AS actual_end_date, 
       DATE_FORMAT(CONVERT_TZ(ps.stage_planned_start_date, '+00:00', '+05:30'), "%Y-%m-%d %H:%i:%s") AS planned_start_date, 
       DATE_FORMAT(CONVERT_TZ(ps.stage_planned_end_date, '+00:00', '+05:30'), "%Y-%m-%d %H:%i:%s") AS planned_end_date 
FROM project_stage AS ps 
JOIN map_project_user AS mpu ON mpu.user_id = 22 
                             AND mpu.project_id = 1
                             AND mpu.tenant_id = ps.tenant_id
                             AND ps.project_id = mpu.project_id;

编辑:另一种选择:只需将 +5:30 添加到日期:

SELECT ps.stage_start_date + interval 5 hour + 30 minute AS actual_start_date, 
       ps.stage_end_date + interval 5 hour + 30 minute AS actual_end_date, 
       ps.stage_planned_start_date + interval 5 hour + 30 minute AS planned_start_date, 
       ps.stage_planned_end_date + interval 5 hour + 30 minute AS planned_end_date 
FROM project_stage AS ps 
JOIN map_project_user AS mpu ON mpu.user_id = 22 
                             AND mpu.project_id = 1
                             AND mpu.tenant_id = ps.tenant_id
                             AND ps.project_id = mpu.project_id;