-1

I have a web API to store some values in a table with datetime. Server db has UTC timezone and thus stores the datetime in UTC format.Is it correct to store server time in the datetime field or need to take the client time as input?

Next the other API gives a list of data in json format with some datetime values. Since the response is in json format, it will convert the datetime values to milliseconds.In jQuery we use moment.js to format the datetime values and display to user. It automatically displays the datetime based on the browser timezone to user but sometimes displays wrong value.

Is the above approach of handling timezone correct? What's the best approach if my web application is to be accessed from different parts of the world, and suppose the same request has to be updated from different parts.?

Monika
  • 135
  • 1
  • 12
  • This is too vague. "Is it correct to store time as timestamp" -> Yes, if the value is indeed timestamp. There is also at least a `DATE` type, possibly some others. "sometimes [it] displays wrong value" -> what does that mean? When is "sometimes"? – M. Prokhorov Jan 29 '18 at 18:32
  • I meant to ask whether it is correct to server time or get user input for datetime field.Next it has to be stored in server timezone format or client timezone format – Monika Jan 29 '18 at 18:40

1 Answers1

0

The most common methodology being followed by many applications that cater to a global environment is to store Server's UTC time. There I would say the mechanism you are following to store date time is nothing unusual and pretty much ok.

When retrieving data, different situations cater to different requirements. In this case you may benefit from sending a string representation of the persisted UTC Date time via json and use javascript/ jQuery date functions to convert to a javascript friendly date time object and use moment.js for conversions or convert back to local datetime using Javascript itself.

Take a look at the following post for conversion from UTC to local using JS: Convert UTC date time to local date time

Hope this helps!

Diablo
  • 169
  • 1
  • 8
  • Thanks but how to handle datetime at server end if the server db timezone is set to timezone other than UTC say 'Asia/Singapore'? Do we still need to convert the datetime to UTC and store in db? – Monika Jan 31 '18 at 04:36
  • 1
    Yes, you would still need to store in UTC. You are not saving the time on server - rather saving the UTC time based on server's timezone. For example, even if your server's timezone is Asia/Singapore, your web application/ API must convert the current server time to UTC and that's what you should save. The reason behind all this is, imagine a scenario someone creating a record in Singapore but a user from US tries to find out the time it was created based on US local time. If you don't have it saved in UTC, it would make it difficult to map to US local timezone. – Diablo Jan 31 '18 at 13:19