I am working on buy order table for store client's record from frontend side and my case:

1. Client would which come from different countries, different timezones
2. My database server timezone is on UTC 1
3. Daylight saving time might apply in some countries
4. Some clients might buy a product from country A, then buy another product from country B the next day but on different timezones
5. Some client (travelers) which come from country A (UTC 2) and bought items in country B (UTC 0) via his phone (UTC 3, I don't know why but they are)

Here are my questions:

1. What is the best practice to store the datetime in the database?
2. Does frontend side need to convert timezones from client to database?
3. Does backend side need to convert timezones from database to client? How do I determine a client's current timezone?
4. What is the best practice to show datetime from server to website for client review?
5. (New) How do I handle available_delivery_date and available_delivery_time which those fields input by client?

==================

[ Update 1 ] An addition description about question point 5, my order table have 2 fields named available_delivery_date and available_delivery_time which input by client with their timezone, what is the best practice to save the record?

CodePudding user response：

Everything is depending on future use cases and your requirement. No format is standard whatever works best for you.

What is the best practice to store the datetime in the database? -If you need to perform calculation like days from purchase using date store it in either datetime or timestamp. Storing as varchar and parsing before calculation also can be done.

Does frontend side need to convert timezones from client to database? -Make backend use one standard time format and on response pass date from server date and time so that your time would be standard for all users.

Does backend side need to convert timezones from database to client? How do I determine a client's current timezone? -Use servers date and time to standarize your time formats.

What is the best practice to show datetime from server to website for client review? -Best practice is to use backend to insert server time on insert query so that server time would be standard for all users. You can fetch the row and pass it to frontend which is based on server time