Skip to content
Advertisement

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

Here datatype is DATETIME

Here is the code

Result on JSON response

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

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

Advertisement

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:

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

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement