EllisLab text mark
Advanced Search
     
Setting timezone to UTC/GMT for Database
Posted: 30 March 2010 12:16 PM
Avatar
Joined: 2010-03-30
6 posts

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:

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?

 
Posted: 31 March 2010 05:25 AM   [ # 1 ]   [ Rating: 0 ]
Joined: 2009-11-01
296 posts

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.

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();
    
}
 Signature 

http://flakron.net

 
Posted: 31 March 2010 09:05 AM   [ # 2 ]   [ Rating: 0 ]
Avatar
Joined: 2010-03-30
6 posts

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!

 
Posted: 28 May 2010 09:18 PM   [ # 3 ]   [ Rating: 0 ]
Avatar
Joined: 2010-05-25
16 posts

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/date-and-time-functions.html#function_utc-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.  smile

 
Posted: 29 May 2010 05:30 AM   [ # 4 ]   [ Rating: 0 ]
Avatar
Joined: 2008-11-04
4489 posts

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

 Signature 

Me: WanWizard.eu | My company: Exite | Datamapper: DataMapper ORM <= LOOKING FOR A NEW MAINTAINER!

 
Posted: 23 March 2011 10:02 PM   [ # 5 ]   [ Rating: 0 ]
Joined: 2010-10-18
18 posts

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:

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.

 
Posted: 06 March 2013 03:54 PM   [ # 6 ]   [ Rating: 0 ]
Avatar
Joined: 2013-03-06
1 posts

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

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

$hook['post_controller_constructor'][] = array(
                                
'class'    => 'Timezone',
                                
'function' => 'set_timezone',
                                
'filename' => 'timezone.php',
                                
'filepath' => 'hooks'
                                
); 
 
Posted: 16 October 2013 03:55 AM   [ # 7 ]   [ Rating: 0 ]
Joined: 2013-10-16
1 posts
weboide - 30 March 2010 12:16 PM

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:

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?


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