Manage date as UTC

Hi there,

I'm setting up a website that provide access/translation for different regions.

The system should show users the same date/time, example:
User 1 is from USA, Washington (GMT-5) -> 2th Jan, 13:30
User 2 is form Australia, Canberra (GMT+11) -> 3th Jan, 05:30

I want to show GMT+0 (18:30) -> 2th Jan, 13:30 for all users.

And I want avoid DLS changes. I've read that there are 2 GMT types:
- GMT as UTC (no DLS changes) - I want this
- GMT as region (DLS changes)


Now, in CodeIgniter, what I should do to collect that? I see in config.php an option ($config['time_reference'] = 'local'.
I should change it or not? And that change give me chances to show the same date/time to all users?

I know, I'm newbie here, but if you have simple practice example on how-to-do it as best practice, would be perfect.

Thank you all!

Hi there,

I'm setting up a website that provide access/translation for different regions.

The system should show users the same date/time, example:
User 1 is from USA, Washington (GMT-5) -> 2th Jan, 13:30
User 2 is form Australia, Canberra (GMT+11) -> 3th Jan, 05:30

I want to show GMT+0 (18:30) -> 2th Jan, 13:30 for all users.

And I want avoid DLS changes. I've read that there are 2 GMT types:
- GMT as UTC (no DLS changes) - I want this
- GMT as region (DLS changes)


Now, in CodeIgniter, what I should do to collect that? I see in config.php an option ($config['time_reference'] = 'local'.
I should change it or not? And that change give me chances to show the same date/time to all users?

I know, I'm newbie here, but if you have simple practice example on how-to-do it as best practice, would be perfect.

Thank you all!

The only instance where time_reference is used is in the now() function of the date helper. The now() function is really just an adjusted time() function, so not likely to handle all of your needs. For instance, if you are using CURRENT_TIMESTAMP as the default or update value in a MySQL table, that time will be the server's local time unless you have specified the timezone to MySQL. I believe this is also true for the MySQL NOW() function. If you need MySQL to always have a set timezone, then you will want to run the following before doing any queries that have time related inserts or updates:

PHP Code:

$this->db->query('SET time_zone = "00:00"'); 


Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

PHP Code:

$this->db->query('SET time_zone = "00:00"'); 


I've no chance to check it. I've some problem to retrieve database data.
Config/database.php seems ok with my db data information.

I've in controller Homepage.php:

PHP Code:

    public function index()
    {
        $this->load->model('auth');
        $data['results'] = $this->auth->example();
        $this->load->view('homepage', $data);
    } 


Model Auth.php:

PHP Code:

function example()
    {
        $query = $this->db->query("SELECT * FROM tablename WHERE user_id = 1 LIMIT 1");
        if($query->num_rows == 0) { return false; }
        else { return $query->result(); }
    } 


View homepage.php:

PHP Code:

print_r($results); 


Database library load in config/autoload.php:

PHP Code:

$autoload['libraries'] = array('encrypt', 'session', 'database'); 


Page load give me blank page.. No errors, nothing. How can I check if database connection work fine?


About initial problem, I want try before by myself to find solution to implement the suggested command in all queries, but at all, now I can't try.

Thanks!

You might try autoloading "database" before "encrypt" and "session".

If you want to see if the db is working, then after your query do something like this:

PHP Code:

echo $this->db->last_query(); 


It should show you the last query that was ran.

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

I've put database first of the list.

The command to retrieve last query print me this: SELECT * FROM tablename WHERE user_id = 1 LIMIT 1

But asyesterday it not shows the array results on the view.

Try adding parenthesis to num_rows:

Code:

if($query->num_rows() == 0) { return false; }

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

Try adding parenthesis to num_rows:

Code:

if($query->num_rows() == 0) { return false; }

Uhm, it works..


Back to UTC, I've tried now both with local and an online server.
First without $this->db->query('SET time_zone = "00:00"');

Read data:
Retrieve mysql stored in TIMESTAMP colum "YYYY-MM-DD HH:MM:SS.000000", it show the same when I print out the input on the view. It's ok.

On write there's the issue, by PHP. Using for insert this:

PHP Code:

date( 'Y-m-d H:i:s', time() ) 


It uses the local datetime. IIt works as I want with this:

PHP Code:

date_default_timezone_set('UTC'); 


Above the query, or in CI index.php on the top to use it in all website/db queries.

Seems there's no chance to set it in php.ini, beacuse there are no UTC in supported list of timezones: http://www.php.net/manual/en/timezones.php

I've tried also your code, maybe I don't uderstand how to use it in single query (it gives me error). But, in any case, I need the setting in all queries. So, i think to go with the code above in index.php (if there are no specific/performance contraindications).

I only have to understand why not let me use the SQL milliseconds. I've tried to add data in MySQL with this:

PHP Code:

date( 'Y-m-d H:i:s', time() ) . "." . substr(microtime(TRUE), -4) . "00" 


But it still save data with 000000 milliseconds after dot.


Thank you!