The Best Way to Store User Time in Database Regarding Server Locale Time zone

On May 8, 2015, in Other Online Technology, by James Liu

When we are designing an app for worldwide customers, it is always a headache problem for dealing with time for customers from different countries and different time zone. It is quite easy to get confused with our server locale and how to store the time data in local server for each customers. This problem description is a little bit abstract. Please let me give you a real example to explain the problem in details and I am gonna show you several ways to handle the user date and time in database server.

Several days ago, I am developing my project “Google Translate Voice Mp3 Downloader” and redesign the database which will record the user request for each word and store the download record. Here is how my database looks like:

timezone design in db

These two tables are very simple. Each tablet will store a date in DateTime format. Actually, I am using Varchar format to store the date and time in my first version, which is the most popular way to store user datetime in the old age. Here is a code example to show you how to store timestamp in String or Varchar format in database. This is the php code, but the concept shall be the same in different program languages.

$currentTime = time(); //E.g. 2147483646; It will return the current time measured in the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT).

By default, it will be a integer, for example “2147483646”. But in a 32 bit system, it can only encode the datetime to 03:14:07 UTC on 19 January 2038. This is the famous Year 2038 problem. But this problem can be easily avoided if you are using 64 bit server. Before some people like to store this int value in mysql in TIMESTAMP type. My suggestion is never do that. You can store it as Varchar or String in mysql. However, when you store time value in string formate, you will get another problem when you try to fetch data by date range from database or sort data by time in database.

The Best Way to Store User Timestamps in Database

Now I am using another way to store user time value in database, and I believe that could be the best way (if you know a better way, please let me know!). In MySQL (or their database I guess), there is a new date type DATETIME, which will store both date and time value. You can check how C# saves Datetime value in MSSQL. It displays the value in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. Here is how I store the customer timestamp by PHP and MySQL.

$currentTime = date("Y-m-d H:i:s", time());
//then save the $currentTime into database

The above code example will get the current UTC timestamp and output as ‘YYYY-MM-DD HH:MM:SS’ format, for example, 2015-05-05 05:11:19. Then you can save this value into database by DATETIME format.

The Best Way to Solve Timezone Problem When Users and Server Has Different Locale

For most of the worldwide applications, no matter they are iPhone app, Android app, or web app, all of them are facing a problem, how to save time for their customers. As you know that our server has a local timezone, but all the customers are from different place in the world, with different timezone. Let’s say your server is based in Hong Kong for example, then its time zone is UTC +08:00. So when your server local time is 8 am on Friday, the customers in New York are still at 8 pm on Thursday. Then the question is which time you should save in your server database, 8am on Friday or 8pm on Thursday.

Solution 1: Save Timestamp as Local Time
This is the most easy way to think. In one project, my college is using this way to save user timestamp, I don’t like this approach though. So in the database, all timestamp is basing on HK time, UTC +08:00. When you fetch the time value from the database and show it to your users, you have to convert the timestamp from HK time to NY time, or UK time if your users are from UK. It seems to be a straight forward solution. However, there is a critical problem in this solution, what if you plan to move your server or data center from HK to US. Of course you can set your server locale as HK even it is located in US. But it may cause lots of confusion when you maintain your server log files.

Solution 2: Convert to UTC Time Stamp Before Saving
Instead of saving server local time, you can save your user timestamp in UTC time. When your user is living in US, but your server is located in HK, they have the same UTC time. When you fetch the time stamp from database, you can easily convert it back to your user local time basing on their timezone. This is the best way I prefer. It doesn’t require you to make any changes on your servers locale, no matter where your servers are located. Currently, I don’t see any problems in this way to store user time. If you know that, please let me know.

2 Ways To Get User Time Zone

The most accurate way to get your users time zone is letting your users choose the time zone or asking them where they are living. There are lots of examples when you register account in web applications. But it may cause your register process a little bit user unfriendly.

So, is there a way to get user time zone from their browsers by Javascript? Yes, of course. But it may not be very accurate as the first solution. I am using a JS library, jsTimezoneDetect, to get the user timezone, then pass it to my back end server silently by Ajax calling. Let’s see an example to get timezone by Javascript:

<script src="./res/jstz-1.0.4.min.js"></script>
<script type="text/javascript">
    $(document).ready(function() {
		var tz = jstz.determine();
			type: "GET",
			url: "./gettimezone.php",
			data: 'timezone='+

gettimezone.php is my back end server php script to save the user timezone temporarily, in the session. Here is the source code:

	ini_set("display_errors", 1);
	if(!empty($_GET&#91;'timezone'&#93;)) {
		$_SESSION&#91;'timezone'&#93; = $_GET&#91;'timezone'&#93;;

Leave a Reply

WordPress Themes

Free WordPress Theme