2

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

Rahman Haroon
  • 1,088
  • 2
  • 12
  • 36
  • 3
    What is transforming the query results to json? – WillardSolutions Dec 08 '20 at 16:31
  • 3
    Seems to be timezone issue. Like UTC vs IST (+5:30) – Taavi Tiitsu Dec 08 '20 at 17:26
  • Timezones, your SQL server must be in a different timezone. You should get the timezone of that server and account for it in your code, or change the timezone – Joe Dec 08 '20 at 19:09
  • What's the DATATYPE in MySQL? `DATETIME` and `TIMESTAMP` work differently. `SHOW VARIABLES LIKE '%zone%';` What is the timezone for your computer? For the server? – Rick James Dec 09 '20 at 01:39
  • MYSQL DATATYPE is `DATETIME`. Timezone is in `UTC` System timezone is also showing `UTC` – Rahman Haroon Dec 09 '20 at 04:55
  • I need to get the timezone as `IST` on my response, how to resolve this issue in query – Rahman Haroon Dec 09 '20 at 05:03
  • 1
    Actually timezone is encoded in the json in the last bit `000Z`. So, the application loading the json can just covet it to any required time zone. – hkbharath Dec 09 '20 at 11:05
  • You might want to check your node.js conection to mysql. Similar question [here](https://stackoverflow.com/questions/23571110/nodejs-responded-mysql-timezone-is-different-when-i-fetch-directly-from-mysql) – Taavi Tiitsu Dec 09 '20 at 12:21

2 Answers2

3

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;
Taavi Tiitsu
  • 189
  • 1
  • 11
0

Your backend changes the time format. Check you API which connects to the database.

Paul
  • 81
  • 1
  • 2