Convert datetime to local timezone – Javascript, PHP and MySQL
In this article, you will learn how to convert UTC datetime to user’s local timezone using Javascript.
Your database is centralized, but your user’s are located on different geolocations. Time stored in your database is usually UTC by default. But your users should see the time according to their timezone. So how are you going to do it ?
Table of contents:
Save datetime in UTC
First, we will create a users table. Run the following query in your phpMyAdmin.
CREATE TABLE IF NOT EXISTS users(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
name TEXT,
created_at DATETIME
)
Then we will insert a row in this table.
INSERT INTO users(name, created_at) VALUES ('Adnan', UTC_TIMESTAMP())
Note that we are using UTC_TIMESTAMP() MySQL built-in function. This will return the current UTC date and time.
Get user’s local timezone
To get the user’s local timezone, we will use the Intl object of Javascript. It stands for internationalization.
const timeZone = Intl.DateTimeFormat().resolvedOptions().timeZone
DateTimeFormat() creates a new DateTimeFormat object.
resolvedOptions() will return a new object. Its object’s properties reflects the locale and datetime format sets during the initialization of Intl.DateTimeFormat() object.
timeZone will return the timezone of user’s local computer.
We saved it in a variable. Now we will call an AJAX request to fetch the records from database using PHP.
const ajax = new XMLHttpRequest()
ajax.open("POST", "get-data.php", true)
ajax.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
console.log(this.responseText)
}
}
const timeZone = Intl.DateTimeFormat().resolvedOptions().timeZone
const formData = new FormData()
formData.append("time_zone", timeZone)
ajax.send()
This will call an AJAX request and attach the timezone value with the request.
Convert UTC datetime to local timezone
To convert the UTC value from database to user’s local timezone, first we need to get the value of user’s timezone from the AJAX request. So create a file named get-data.php and inside this file, creates a connection with the MySQL database.
<?php
$conn = new PDO("mysql:host=localhost;dbname=your_db_name", "db_user", "db_password");
Then we will get the timezone from AJAX request and fetch the single user’s record.
$time_zone = $_POST["time_zone"] ?? "";
if (!empty($time_zone))
{
date_default_timezone_set($time_zone);
}
$sql = "SELECT * FROM users";
$result = $conn->prepare($sql);
$result->execute([]);
$user = $result->fetchObject();
Finally, we will convert the timestamp from UTC to user’s local timezone and return it to the client.
echo date("d M, Y h:i:s a", strtotime($user->created_at . " UTC"));
If you open your browser’s console tab, you will see the date and time but in your own timezone.
Using DateTimeZone class
Calling date_default_timezone_set() will set the entire request’s timezone to the user timezone. If you want to convert only one column’s value to user’s local timezone, you can use PHP built-in DateTimeZone class.
First, you need to create its object:
$dateTimeZone = new DateTimeZone($time_zone);
Then, you can create a datetime object from timestamp stored in MySQL database and set it to use the timezone of user.
$dateTime = new DateTime($user->created_at);
$dateTime->setTimezone($dateTimeZone);
Finally, we can display the date and time in the format.
echo $dateTime->format("d M, Y h:i:s a");
That’s how you can convert UTC datetime to user’s local timezone using Javascript.
Server-side rendering
What if instead of calling an AJAX, you are doing server-side rendering ? In PHP, you cannot detect user’s timezone because PHP runs on the server while the timezone is a client side thing that can be done via Javascript. But there is still a way if you are displaying the data directly via PHP and not from an AJAX request.
First, you need to give a class to the DOM that is displaying the datetime field.
<td class="timezone"><?php echo $row->created_at; ?></td>
Right now the time is in UTC and not in user’s local timezone. So we are going to hide it.
<style>
.timezone {
display: none;
}
</style>
Finally, we will use Javascript to convert the UTC time to user’s local timezone.
// get user's timezone
const timeZone = Intl.DateTimeFormat().resolvedOptions().timeZone || "";
// get all DOM where class="timezone"
const timezones = document.querySelectorAll(".timezone");
// loop through all datetime nodes
for (let a = 0; a < timezones.length; a++) {
// get the datetime value
const time = timezones[a].innerHTML;
// convert the datetime to 24-hour format dd/MM/YYYY, H:i
const newTime = new Intl.DateTimeFormat("en-GB", {
timeZone: timeZone,
dateStyle: "short",
timeStyle: "short"
}).format(new Date(time + " UTC"));
// update the datetime value in DOM
timezones[a].innerHTML = newTime;
// show the DOM
timezones[a].style.display = "block";
}
Comments has been added with each line for explanation. If you run your code now, you will be able to see the time correctly in your own timezone.
