PHP mySQL CURRENT_TIMESTAMP incorrect time shown in database in cPanel

Question

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

so you wanted to update the datetime or just for "SELECT" query? — Renjith V R, Jun 25 '16 at 11:10
@YahooI wanted to update DB table with correct timezone, not select. So finally, I found solution by Ollie Jones user — Amali Perera, Jun 27 '16 at 10:22

O. Jones · Accepted Answer · 2016-06-25T11:35:25.190

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.

Thanks for the comprehensive answer. I will try all these step by step taking enough time. Then I will mark your answer accepted. — Amali Perera, Jun 25 '16 at 14:20
Thanks finally I found solution. See my updated answer in my question. — Amali Perera, Jun 27 '16 at 10:23

PHP mySQL CURRENT_TIMESTAMP incorrect time shown in database in cPanel

1 Answers1