Datetime changing on JSON response

I am selecting start and end date of a project from project_stage named table.

Here is the table elements

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

Here datatype is DATETIME

Here is the code

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;

Result on JSON response

{
   "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"
}

Here date time is changing its not the actual datetime which is in the table,why the date is changing on result.Here is the expected output

Expected Result

{
   "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 is DATETIME. Data base timezone is in UTC and System timezone is also showing UTC, How can I covert this datetime corresponding to timezone of users system

Answer

According to the data examples, the Timezone issue appears to be in the code that converts the SQL result to JSON. Since the time difference between the database and the JSON is -05:30, it seems that the “JSON transformer” assumes that the result of the SQL query is IST (UTC +05: 30) and converts the time to UTC (subtracts 5:30).

The correct fix should be done in the “JSON transformer”. However, if the requirement is to achieve the “corrected date” by modifying the SQL query, you can use the CONVERT_TZ (dt, from_tz, to_tz) function. This adds +5:30 and “JSON transformer” subtracts 5:30 later resulting the time being unchanged.

Something like that:

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;

Edit: Another option: simply add +5:30 to the dates:

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;