0

I'm facing a problem with handling different timezones in MySQL. I have looked into similar questions that were previously raised, but I couldn't find a proper answer.

The issue I'm encountering is as follows: I have multiple microservices and a MySQL server. For instance, my mobile application runs in the timezone "+05:30," and the Node.js service runs in the same timezone. The problem arises when I fetch data from the mobile application, as it updates the database record with the current time without any adjustments. However, when data is received from the Node.js service on the backend, it subtracts "-5:30" from the stored value. Consequently, I end up with an incorrect value in the mobile application.

I need a way to handle DateTime properly for any timezone.

3
  • 4
    zulu time is the only time. Other times are just local stylings. Commented Jun 1, 2023 at 17:21
  • Set everything (like everything) to UTC.... Date formats for the rest
    – Laiv
    Commented Jun 1, 2023 at 17:56
  • 2
    please don't cross-post: stackoverflow.com/questions/76384158/… "Cross-posting is frowned upon as it leads to fragmented answers splattered all over the network..."
    – gnat
    Commented Jun 1, 2023 at 18:13

2 Answers 2

3

Like you would any other value with a unit - store it in a known "unit" system (e.g., UTC). Just like if you were storing distances (in meters) or masses (in kg) or anything else, always work with your values in a known system.

Any time you go to store the timestamp, you convert it to UTC first. Any time you retrieve the value, you immediately convert it from UTC - although, ask yourself if you really need timestamps in your local timezone, or if you can always work in UTC, and then you don't need to worry about converting the value you retrieve from the database.

1

As others already stated, ALWAYS store your dates in UTC in the database.

But your real problem seems to be that your mobile app is not capable of providing dates with timezones.

When you do it right, and store everything in UTC, you also have to provide everything in UTC or at least provide timezone information so you can calculate the UTC equivalent before storing the date in the database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.