Convert datetime to local timezone - Javascript, PHP and MySQL

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:

1. Save datetime in UTC
2. Get user's local timezone
3. Convert UTC to timezone

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 formData = new FormData()
formData.append("timeZone", timeZone)
ajax.send()

This will call an AJAX request and attach the timezone value with the request.

Convert UTC to 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.

$timeZone = $_POST["timeZone"] ?? "";

date_default_timezone_set($timeZone);

$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($timeZone);

Note: If you are working in classes or namespaces, use: new \DateTimeZone($timeZone);

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);

For classes or namespaces use: $dateTime = new \DateTime($user->created_at);

Finally, we can display the date and time in format.

echo $dateTime->format("d M, Y h:i:s a");