Codeigniter timezone mysql settings

Question

Just realised WHY my site is now showing all datetime variables as -1 hr... I'm using Codeigniter for the first time! (Never had this problem before)

So, I have included the following code in my main index.php file

/*
|---------------------------------------------------------------
| DEFAULT TIMEZONE
|---------------------------------------------------------------
|
| Set the default timezone for date/time functions to use if
| none is set on the server.
|
*/

if( ! ini_get('date.timezone') )
{
   date_default_timezone_set('GMT');
}

However, it's still showing as -1 hr, so I'm assuming I need to set some sort of default setting for MySQL...

I have included the following line of code in my model:

   function __construct()
    {
        // Call the Model constructor
        parent::__construct();
        $this->db->query("SET time_zone='+0:00'");
    }

Still no difference... Help!

My code is:

<h3><?=date('D, jS F @ g:ia', strtotime($row->datetime))?></h3>

The $row->datetime variable is nothing more than a DATETIME column value from my MySQL database. The echoed variable in view is ALWAYS 1 hour less than the value in my database...

My model code is:

function coming_up()
{
    $this->db->query("SET time_zone='+0:00'");
$query = $this->db->query('SELECT * FROM events1 WHERE datetime >= NOW() ORDER BY datetime LIMIT 2');
return $query->result();
}

Cubit

Answer 1

In config/autoload.php, set a model to load on each page load. then call $this->db->query("SET time_zone='+0:00'"); in that model constructor.

config/autoload.php

$autoload['model'] = array('default_model');// for ex, "say default_model"

In application/models, create a new model file with name of "default_model.php" and add below code.

application/models/default_model.php

class Default_model extends CI_Model {

     function __construct()
     {
         // Call the Model constructor
         parent::__construct();
         $this->db->query("SET time_zone='+0:00'");
     }
 }

On each page load, this constructor will be called and mysql timezone will be set to +0:00.

Minnaminnaminnie

If you need to deal with DST, you can load the named zone $this->db->query("SET time_zone='America/Chicago'"); note you'll need to have the mysql time zones setup mysql_tzinfo_to_sql – Girth

It would also be a good idea to add something like date_default_timezone_set('TIMEZONE_SET_FOR_DATABASE'); to application/config/config.php just below the line defined('BASEPATH') OR exit('No direct script access allowed.'); – Sagacity

date_default_timezone_set is upto php level. If you db and php files are located at same server and mysql uses same timezone, then you can use it but not sure about mysql behavior. If database is in another server, then you have to follow my solution. – Minnaminnaminnie

Answer 2

Add these line in your config file and then check, it is working for me

$config['time_reference'] = 'gmt';# Default should be GMT
date_default_timezone_set('UTC');# Add this line after creating timezone to GMT for reflecting

Stocktonontees

No change I'm afraid... @Rohan Kumar – Cubit

Check your insert query it should look like $this->db->query("INSERT INTO TABLE SET time_zone='+0:00'"); – Stocktonontees

See above, I've edited my post to include my query code @Rohan Kumar Still, no difference! – Cubit

Answer 3

am also face this problem. i tried this way..

$this->db->query("SET LOCAL time_zone='Asia/Kolkata'");
$query="SELECT * FROM offers where NOW() between offfer_from and offer_to";
$res=$this->db->query($query);

it will work fine in my project and i'm not using anything new default model.. if you want global solution means you can use auto-load some model.

Duplication

Answer 4

SET default time_zone for MySQL : Go to : Your_Project/system/core/Model.php And then update this function :

public function __construct() {
    $this->db->query("SET time_zone='YOUR_TIME_ZONE'");     
}

SET default_time_zone for PHP : Go to Your_Project/index.php And then update here :

define('ENVIRONMENT', isset($_SERVER['CI_ENV']) ? $_SERVER['CI_ENV'] : 'development');
date_default_timezone_set('YOUR_TIME_ZONE');

Boyer

Answer 5

I like the solution proposed by Kumar however, I didn't need to set this globally, only when showing dates stored in UTC time in my db.

In my api model I have the following function.

  public function setDBTimeOffset(){
    $dt = new DateTime();
    $offset = $dt->format("P");
    $result = $this->db->query("SET time_zone='$offset';");
    return $result;
  } # END FUNCTION setDBTimeOffset

From my controller, I call the following first.

  $this->api->setDBTimeOffset();

Followed by the call to the function that queries the db. For example, I am fetching some user history.

  $data["viewData"]["allHistory"] = $this->api->getUserHistory("ALL", 0, 10000);

When I display the output, the dates are in my timezone. I hope this helps someone, somewhere in time (couldn't resist).

Whippletree

Answer 6

Simply open MY_Model.php at location application/core/MY_Model.php, and then try to set timezone in model constructor.

public function __construct()
{
    parent::__construct();
    $this->load->library('user_agent');
    $this->db->query("SET time_zone='+05:00'"); // Here set your timezone, i used +05:00 for Pakistan

}

Beckman

Answer 7

You can set it in the index.php file in your project folder on top and after <?php

<?php
date_default_timezone_set('Asia/Bangkok');

Lucaslucca