Setting timezone to UTC/GMT for Database

[eluser]weboide[/eluser]
I would like to set up the database (MySQL) to use the UTC/GMT timezone (to store and retrieve dates). I found that I could use the following query:

Code:

SET time_zone='+0:00';

How can I integrate it into CodeIgniter so that it gets executed on every DB connection?
Can I extend a database class or something like that?
Any other idea?

[eluser]flaky[/eluser]
you could create a model that all other models extend, in this way all the queries run will have the time zone set to the zone you need.

Code:

class Master_model extends Model{
    public function __construct(){
        parent::__construct();
        
        $this->set_timezone();
    }
    
    public function set_timezone(){
        $this->db->query("SET time_zone='+0:00'");
    }
}

class Some_model extends Master_model{
    public function __construct(){
        parent::__construct();
    }
}

[eluser]weboide[/eluser]
flaky,

Thank you very much for the reply and the idea, that looks like a good way of doing it and there wouldn't need to modify CI core code.
I'll look into that, thanks again!

[eluser]timotheus[/eluser]
You could also use the MySQL UTC time functions:

-UTC_DATE()(v4.1.1) Return the current UTC date
-UTC_TIME()(v4.1.1) Return the current UTC time
-UTC_TIMESTAMP()(v4.1.1) Return the current UTC date and time

Check out the time and date functions in the MySQL documentation:

http://dev.mysql.com/doc/refman/5.1/en/d...-timestamp

I have struggled with this for ages, and I just found this in the documentation today, and it works. Kicking myself for not finding it sooner.

[eluser]WanWizard[/eluser]
Note that MySQL's timezone support is also flaky, especially if you use it to convert times in the past.

Don't rely on it, use PHP's DateTime class (PHP 5.2+), and store only GMT timestamps in the database. You can use my helper extension, for pre-PHP 5.2, you'll also need the timezone helper (and lots of memory).

[eluser]wapatv[/eluser]
I need to do this same thing (change DB time zone for every connection).

I was thinking the approach of doing the below code on every model:

Code:

public function __construct() {
        parent::__construct();
        $this->db->query("SET time_zone='-4:00'");
    }

But then saw flaky's approach of creating a "Master_model" and all other models extending to it.


What would be the better way in terms of performance between those two options?
or is there any difference in performance at all?


Thanks!

Edit: I'm using Reactor, but since this thread already existed, I replied it instead of creating a new one for a similar question.

[eluser]Unknown[/eluser]
I found this thread to be very useful in solving an issue I was having, and I thought I'd share it incase others find this useful or are in similar situations.

For my application I wanted to be able to define a different timezone for each organization entity in my database schema.

In addition to setting the Database connection to use a specific timezone I also wanted to set PHP to use the same timezone so I wasn't tied to the server timezone (and also because of the organization specific settings)

I ended up implementing this is a post_controller_constructor hook to set both PHP and Database settings.


./application/hooks/timezone.php

Code:

public function set_timezone()
{
      $CI =& get_instance();
      $CI->load->model('Organization');

      $orgID = $CI->Organization->GetOrgIDFromSubdomain();

      $query = $CI->db->query("
         SELECT `Timezone`
         FROM `organizations`
         WHERE `OrganizationID` = '$orgID'
      ");

      $row = $query->row();

      // Where TimeZone is something like America/Vancouver
      $timezone = $row->Timezone;

      $CI->db->query("SET time_zone='".$timezone."'");

      date_default_timezone_set($timezone);
}

./application/config/hooks.php

Code:

$hook['post_controller_constructor'][] = array(
                                'class'    => 'Timezone',
                                'function' => 'set_timezone',
                                'filename' => 'timezone.php',
                                'filepath' => 'hooks'
                                );

[eluser]Unknown[/eluser]
[quote author="weboide" date="1269965801"]I would like to set up the database (MySQL) to use the UTC/GMT timezone (to store and retrieve dates). I found that I could use the following query:

Code:

SET time_zone='+0:00';

How can I integrate it into CodeIgniter so that it gets executed on every DB connection?
Can I extend a database class or something like that?
Any other idea?[/quote]


I think its not correct way to do. You can set time zone in
index.php of root folder.

date_default_timezone_set('Asia/Kollkatta);