I am living in Sri Lanka. I have this code to record the current date and time when invalid login done.
$insert_query = "INSERT INTO LoginAttempt(IpAddress, LoginAt) VALUES('". $_SERVER['REMOTE_ADDR']. "', CURRENT_TIMESTAMP)";
CREATE TABLE LoginAttempt(
LoginId INT NOT NULL AUTO_INCREMENT,
IpAddress VARCHAR(20) NOT NULL,
LoginAt DATETIME NOT NULL DEFAULT NOW(),
$insert_query = "INSERT INTO LoginAttempt(IpAddress, LoginAt) VALUES('". $_SERVER['REMOTE_ADDR']. "', CONVERT_TZ(NOW(), 'UTC', 'Asia/Colombo') )";
This server in England is probably running on UTC time. Sri Lanka, is, not coincidentally, 5:30 ahead of UTC. Your server is certainly recording the correct time in your table. It's just in the wrong zone.
These people operating the shared server aren't going to change that just for you: you share the server with people from all around the globe. (Note that it's considered good practice to operate servers on UTC time everywhere, so you would have the same problem if your server were down the hall.)
LoginAt column's data type from
TIMESTAMP values are always recorded in UTC and always translated to local time when displayed.
Then, whenever you connect to MySQL (from a program or in PhpMyAdmin) issue the
SET time_zone = 'Asia/Colombo'
command you have already discovered, before you do anything else. Then you'll see your
TIMESTAMP data displayed in local time.
Alternatively, if you have existing
DATE time data you know is recorded respect to UTC, you can convert it in MySQL using
CONVERT_TZ(). For example,
SELECT CONVERT_TZ(LoginAt, 'UTC', 'Asia/Colombo') LoginAt, ...
will give you your information in local time.
The thing to know is that
TIMESTAMP data types,
CURRENT_TIMESTAMP and so forth are retrieved using the time zone setting.
DATE are not. So, your program gets the
CURRENT_TIMESTAMP implicitly transforming it to UTC local time, and stores it into your
DATETIME column, congealing the time to UTC.
If your program stored it into a
TIMESTAMP column the time zone of the stored time would not be congealed. Alternatively, if your program issued the
set time_zone = 'Asia/Colombo' command before issuing the query in your question, it would congeal the time into your local time before storing it into your