One of the issues that I haven’t found a consensus on is how to send dates and times between an app and the server, and then how to store them in the database. As always, there is no one-size-fit-all solution, but depending on your needs you might prefer one solution over an other. The following method has proven to be good for most of our use cases.
Transmission
Your app can be used from all around the world and thus, you need to include the timezone when you send it. You can use ISO 8601 format which would look something like this, YYYY-MM-DDThh:mm:ssTZD (eg “2018-08-20T08:20:30+01:00”). However, we instead prefer to send it as UNIX milliseconds (time in milliseconds elapsed since the UNIX epoch (Jan 1, 1970)) so the same time as before will look like 1535264378514. Not human readable but perfect for our APIs.
However, if you need to only send the date (e.g. birth day), we simply send it as “yyyy-MM-dd”. Similarly, for time only we send it as “hh:mm:ss”.
Storing in the database
In MySql, there are four types, Datetime, Timestamp, Time, Date. The last two are easy; when we need to store date only, we use Date, and when we need time, we use Time.
Datetime, and Timestamp are a little more complicated. Timestamp internally “holds” the timezone info, where datetime does not (Timestamp does not actually store the timezone info but it is easier to think of it as such). When you save Timestamp to the database, it is automatically converted to UTC time, and when you read it, it is converted back to your timezone. It sounds convenience, but you need to be careful. Depending on your timezone (server timezone) you will get a different values when you retrieve it. To ensure you get a known value regardless of your server timezone, you need to explicitly set the timezone for the db connection. As such, we do not prefer to use Timestamp for anything other than tracking the changes in columns.
Datetime on the other hand does not store the timezone info. You simply store “2018-08-20 08:20:30” and you will always get “2018-08-20 08:20:30”. Though it sounds limited, but it let you have full control on how to store time and date. What we do is we store all datetime info in UTC time and we do the conversion on the server before we store it in the database. This way we always know that the value we get from the database is in UTC.
Summary
All request to/from the server are in UNIX milliseconds. The clients display it for the users using the device timezone. The server converts the milliseconds to UTC time and save it to the database.
Friendly links
If you are looking into hiring developers, you might want to check this guide by our friends at Toptal, and let them know that we sent you. Stay safe.
