Rajesh Rajesh - 26 days ago 9
MySQL Question

Codeigniter Default Timezone Issue

Brief: Am working on a CodeIgniter3.0.3 version application. It is a multi-tenant application. Single code base connects to multiple databases based on the user login (interacts with different database). Each user could be from different timezone across the globe. I am trying to apply the below code.

date_default_timezone_set("Africa/Accra");
$time = Date('Y-m-d h:i:s');echo $time;
$expiry=$this->db->get_where('settings', array('type' => 'registration_date'))->row()->description;
$timestamp=strtotime($expiry);
echo Date('Y-m-d h:i:s',$timestamp);


The second line of this code shows the time properly accordingly, whereas the last line is showing the server time.

The description column of the setting table is of varchar type which stored value like "2016-02-08 16:29:09".

Also, if I add date_default_timezone_set("Africa/Accra") just before the last line, its working fine.

Question : I have tried setting this property in index.php, config.php, controller construct method, created helper library, but none of them seems to have worked. How can I resolve it please?

Edit : The date value stored in the DB was before applying this timezone_set. There was no offset in the DB before.

As of now I don't know the timezone for each of the users. I can set it to GMT and update all the columns accordingly too.
Do I need to update all the existing date columns based on the timezone set? But, What will happen if any user changes their timezone prefer? Is there any other workaround to this?

Answer

The answer from Dhruv helped to some extent. But displaying previously stored dates (before implementing the timezone) was the issue. Then followed up several SO articles arrived the solution like below.

date_default_timezone_set("Asia/Kolkata"); 
$registered=$this->db->get_where('settings', array('type' => 'registration_date'))->row()->description;
$dt_obj = new DateTime($registered);
$dt_obj->setTimezone(new DateTimeZone('Africa/Accra'));
echo $dt_obj->format('Y-m-d H:i:s');

i.e., first setting the timezone as per the server, retrieve the data and convert it to display

Thanks to gzipp as mentioned in this question