Amali Perera Amali Perera - 5 months ago 58
PHP Question

PHP mySQL CURRENT_TIMESTAMP incorrect time shown in database in cPanel

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


I have uploaded to hostinger.co.uk which is free webhosting service for educational purpose.

That inserted into table successfully. It shows the date correct. But time is about 5 hours and 30 minutes less than the actual time.
Is there any way to change timezone in cPanel or programmatically in the PHP script?

What I have tried;

1) addes this. but does not work! date_default_timezone_set('Asia/Colombo');

Another soulution to use
DATE_ADD
function to add the particular missing hours.

What are your another ideas? Thanks

MySQL table;

CREATE TABLE LoginAttempt(
LoginId INT NOT NULL AUTO_INCREMENT,
IpAddress VARCHAR(20) NOT NULL,
LoginAt DATETIME NOT NULL DEFAULT NOW(),
PRIMARY KEY(LoginId)
);


Solution that worked!!!!

$insert_query = "INSERT INTO LoginAttempt(IpAddress, LoginAt) VALUES('". $_SERVER['REMOTE_ADDR']. "', CONVERT_TZ(NOW(), 'UTC', 'Asia/Colombo') )";

Answer

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

Change your LoginAt column's data type from DATETIME to TIMESTAMP. 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 DATETIME or 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, NOW(), CURDATE(), CURRENT_TIMESTAMP and so forth are retrieved using the time zone setting. DATETIME and 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 DATETIME column.

Confusing? Yes.