PHP MySQL query shows wrong datetime in result

Question

I am using workbench for accessing DB connection.

My problem is when I am running the direct query in workbench the result is correct but same query I am running using PHP function the DateTime is different. MySQL server timezone: EST Linux Server timezone: EST

but the result comes in GMT timezone.

All the data entry records show EST time.

eg. Mysql Workbench result: datetime: 19:00:00 PHP query result: 22:00:00

I already did that. When I am printing localtime it shows correct time — NDesai
– NDesai, Commented Oct 8, 2017 at 14:56
please give me 2 datetime one in GMT and other in EST ( expected output) — Mr.Geeker
– Mr.Geeker, Commented Oct 9, 2017 at 3:45
if MySQL datetime=10/10/2017 00:00:00(Stored in DB) then PHP is returning 04:00:00.(Returning after query excecution) — NDesai
– NDesai, Commented Oct 10, 2017 at 18:02

Mr.Geeker · Accepted Answer · 2017-10-11 06:05:14Z

1

Well , you can convert returned data to other timezone so if your time stored in EST but php showing it as GMT , you can convert it again

here is an example

// now
$date = date_create('2017-10-09 07:00:00', timezone_open('Asia/Dubai'));

function getDateByTimeZone($date,$TimeZone='US/Eastern'){
    date_timezone_set($date, timezone_open($TimeZone));
    return $date->format('Y-m-d H:i:s');
}
echo getDateByTimeZone($date);
echo "<br />";
echo getDateByTimeZone($date,'GMT');

// Output 

# 2017-10-08 23:00:00
# 2017-10-09 03:00:00

?>

assume $date is your returned data from Mysql which is in your case GMT , pass it to that function then you will get EST .

Also you can configure your PHP.init file by changing the following

date.timezone = "America/New_York"

and to make sure your php server is running under correct timezone

<?php
phpinfo();
?>

you will find Default timezone in Date Section from that information you should be able to see default timezone for your php server .

if you want to set Default timezone for single website in your server you can edit VirtualHost by adding the following

<IfModule php7_module>
php_admin_value date.timezone "America/New_York"
</IfModule>
<IfModule php5_module>
php_admin_value date.timezone "America/New_York"
</IfModule>

if you would like to set default timezone per folder you could edit htaccess file by adding the following

<IfModule !fcgid_module>
php_value date.timezone "America/New_York"
</IfModule>

edited Oct 11, 2017 at 6:05

answered Oct 9, 2017 at 4:00

Mr.Geeker

4454 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

NDesai Over a year ago

yes, That I am using for a temporary solution. but I want to know why this happening. My local time of PHP is already in EST format and my SQL server is also storing EST time then why PHP is passing UTC time.

Mr.Geeker Over a year ago

what is the output of echo date_default_timezone_get().'<br />';

NDesai Over a year ago

America/New_York

Mr.Geeker Over a year ago

check my new answer pls

NDesai Over a year ago

Nope. Still facing the same issue. I have to use temporary Timezone conversion for time issue.

Collectives™ on Stack Overflow

PHP MySQL query shows wrong datetime in result

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related