Whether you’re a seasoned web developer or just starting out, handling time zones can be a tricky task. Time zone calculations are often done incorrectly, and even experienced developers can find them challenging. In this article, we won’t dive deep into the complexities of dates and time zone mathematics. Instead, we’ll share a simple trick that uses UTC and MySQL timestamps to help you display time accurately for your users. We’ll briefly cover how to store, retrieve, and properly display time on your website or in your application.
If you’ve worked with time in software development, you’ve probably encountered UTC, or “Coordinated Universal Time.” UTC is equivalent to Greenwich Mean Time (GMT), the time zone that covers Greenwich, England. It serves as the standard from which all other time zones around the world are calculated, with an offset of +00:00. By knowing the time zone offset (whether it’s east or west of UTC), you can accurately determine the local time anywhere.
But why is this important, and how does it help us display time correctly? Since UTC is the baseline for calculating time zones, we can store all our times in this universal format in the database. For instance, if you’re in the Pacific Standard Time zone, you can convert the current local time to UTC before storing it in the database. When you need to display the time, you retrieve the UTC value, adjust it based on the user’s time zone (and account for daylight savings if necessary), and then display it correctly.
In MySQL, there are two main types of fields for storing time: TIMESTAMP and DATETIME. When you insert a time into MySQL, the way it’s stored and later retrieved depends on the type of field you use.
When you store a TIMESTAMP, MySQL automatically converts the time to UTC. When you retrieve it, MySQL converts it back to the time zone of the server (for example, if you’re using a server-side language like PHP). On the other hand, with a DATETIME field, no conversion occurs—neither during storage nor retrieval. The date and time are stored exactly as you input them, similar to a plain text string.
To ensure consistent and accurate time display for users, it’s best to store dates and times in UTC format. This way, when you need to adjust the time for a user’s local time zone, it’s straightforward. If you store a raw DATETIME, you would need to know the time zone it was stored in and adjust it accordingly, or risk displaying an incorrect time to the user—something you want to avoid.
Now that we understand the importance of storing dates as UTC timestamps in the database, let’s focus on how to retrieve and display that time in a way that makes sense for the user. For example, if someone in Pacific Standard Time (PST) makes a comment at 1:00 PM, someone in Mountain Standard Time (MST) would see that same event as occurring at 2:00 PM. To display the correct time for users in different time zones, two key pieces of information are needed:
With these two pieces of information, you can accurately convert and display the time for the user. Daylight Saving Time (DST) might also come into play, but most programming languages handle this conversion automatically based on the user’s locale. If your language doesn’t account for DST, you may need to manually add or subtract the appropriate amount of time depending on whether DST is in effect for the user’s region.
To illustrate how to display time correctly, we’ll use three different programming languages: JavaScript, Python, and C#.
Starting with PHP, since it’s a server-side language, the user needs to specify their time zone, often by filling in this information in their profile on the website. PHP, like the other languages we’ll explore, typically handles DST calculations based on the user’s time zone and locale. However, it’s crucial that the user provides this information for accurate time conversion.
$query = "SELECT timestamp_column FROM your_table";
$result = $mysqli->query($query);
$row = $result->fetch_assoc();
$utc_timestamp = $row['timestamp_column'];
// Assume user's time zone is stored in $user_timezone
$user_timezone = new DateTimeZone('America/New_York'); // Example time zone
$utc_datetime = new DateTime($utc_timestamp, new DateTimeZone('UTC'));
$utc_datetime->setTimezone($user_timezone);
$user_time = $utc_datetime->format('Y-m-d H:i:s');
echo $user_time; // This will print the timestamp in the user's time zone
Explanation: The first part of this code simply fetches the time zone field from the database and sticks that into a variable called $utc_timestamp. The second part assumes that the user is in the America/New York (Eastern Standard Time) time zone and uses the DateTimeZone class to set the time zone on the DateTime object we made with the $utc_timesamp variable. We then format it to be whatever format we want to display it as. If we hadn’t gotten the user’s time zone from them, we would have to adjust the time based on the server time. If our website was hosted in a different time zone (or the server was set to a specific time zone) it would have used that.
Note: It might be smart to always set your server to be UTC no matter where it is hosted. This would make it easy, in the age of cloud computing, to deploy the code anywhere in the world.
Ok, let’s move on to JavaScript. Since JavaScript is typically rendered on the client side we can have JS detect some stuff automatically. The first part is to fetch our timestamp again from the database (using something like PHP) and display it as JavaScript…
$query = "SELECT timestamp_column FROM your_table"; $result = $mysqli->query($query); $row = $result->fetch_assoc(); $utc_timestamp = $row['timestamp_column']; echo "<script>const utcTimestamp = '$utc_timestamp';</script>";
Then we run some JavaScript to use this variable and make a conversion…
// Convert the UTC timestamp to a JavaScript Date object
const utcDate = new Date(utcTimestamp + 'Z'); // Tell JS that this is UTC with the 'Z' flag.
// Format the local date as needed
const options = {
year: 'numeric', month: 'numeric', day: 'numeric',
hour: 'numeric', minute: 'numeric', second: 'numeric'
};
const localTimeString = utcDate.toLocaleString(undefined, options); // Takes into account locale and DST
console.log(localTimeString); // This will print the timestamp in the user's local time zone
Explanation: As you can see from the JS, it is pretty clear what this is doing. We take in the timestamp and specify UTC by appending a ‘Z’ suffix. This will make utcDate recognizable as a UTC date.
Note: If you print or use any of the string functions on this date, it will convert it to a local time. So do this only when you ready to display at the end. For fun we also specify some formatting options and convert the UTC Date over to a local time string for display.
Finally we will show a simple example of how this can be done using something like a C# console app. We won’t show anything related to fetching from the database for brevity, but assume that utcTimestamp was the timestamp fetched from the database. Again it would be coming in as a UTC based timestamp.
// Example UTC timestamp as a string
string utcTimestamp = "2024-08-08 19:12:45";
// Parse the UTC timestamp into a DateTime object (make sure the format matches!)
DateTime utcDateTime = DateTime.ParseExact(utcTimestamp, "yyyy-MM-dd HH:mm:ss", null, System.Globalization.DateTimeStyles.AssumeUniversal);
// Convert UTC DateTime to local time
DateTime localDateTime = utcDateTime.ToLocalTime();
// Format the local time as needed
string localTimeString = localDateTime.ToString("yyyy-MM-dd HH:mm:ss");
// Display the local time
Console.WriteLine("Local Time: " + localTimeString);
Explanation: We start with the incoming UTC timestamp and use DateTime.ParseExact to parse that into a DateTime object. From there we can convert the time to local time and format it as we like before displaying.
A great advantage of storing times in UTC across your database and servers is the consistency it brings, especially when it comes to logging. Have you ever looked through a set of logs, noticed a timestamp without a time zone, and wondered, “Is this time in my local time, the server’s time, the user’s time, or UTC?” Wouldn’t it be simpler if you could always assume it’s UTC? Once you get used to it, converting UTC to your local time in your head becomes second nature. Regardless of the time format you choose, it’s crucial to maintain consistency across your systems. This ensures that when different systems communicate, everyone is on the same page—which is exactly why UTC was established.
In this article, we explored the concept of storing time as a UTC timestamp and then converting it for display using JavaScript, PHP, and C#. We discussed the importance of storing date and time in UTC format and only converting it when displaying it to the user based on their time zone and locale. This approach allows you to store time in a universal format and then tailor it to each user, ensuring consistency and smooth communication between systems.
I hope you found this article helpful. If you’d like to support future articles, please check out our Programmers Idea Book and make a purchase. It’s full of project ideas that can help take your skills to the next level and well worth the investment. Thank you! 🙂
