CODEWITHSUNDEEP
phpmysqlcodeigniter-2
aspirinemaga
aspirinemaga

Reputation: 3947

How to handle dates and times in MySQL and PHP?

I'am using a CodeIgniter 2.1.x and MySQL.
I found a lot of advices between using mysql column type DATETIME and TIMESTAMP, but I'm not sure which is really correct to use with what I want to build.

I have an application, where user can add markers to google maps throught the <form></form>. After each marker submission, a current time should be recorded for the mysql's column date_created and date_end which should be +1day by using this function:

class Maps extends CI_Controller{
    ...
    public function marker($action = NULL, $slug = 'marker_add'){
        ...
        if($this->form_validation->run() == TRUE){
            $tomorrow = now() + 86400;
            $data_markers = array(
                'tid'           => $this->input->post('formMarkType'),
                'lat'           => $this->input->post('formMarkLat'),
                'lng'           => $this->input->post('formMarkLng'),
                'state'         => 1,
                'description'   => $this->input->post('formMarkDescription'),
                'date_end'      => $tomorrow
            );
            $this->map_model->markers_add($data_markers);
            redirect(site_url());
        }
        ...
    }

}

But it never updates correctly when I'am using column types set as TIMESTAMP or DATETIME.

Any suggestion what I'm doing wrong here ?

Upvotes: 1

Views: 288

Answers (3)

pgee70
pgee70

Reputation: 4034

It can also depend if you care about timezones. If one user say in australia does something, and other user in america needs to see that thing, then you need to store the datetime in the context of the users' individual timezones.

If this is not an issue for you this will work.

$dt = new DateTime ('+1 day' , new DateTimeZone('Australia/Hobart')); 
$tomorrow = $dt->format('Y-m-d H:i:s');

just use the continent/city nearest to your server for the timezone.

if you have users in different timezones. it is probably better to save the $datetime in 'UTC' timezone (ie new DateTimeZone('UTC') Then you can render the datetime in the user timezone of the user who requests the data.

eg,

$dt = new DateTime('stored datetime', new DateTimeZone('UTC'));
$dt->setTimeZone(new DateTimeZone('users timezone'));

Upvotes: 0

Abhishek Salian
Abhishek Salian

Reputation: 934

var dump $tomorrow before inserting. Check its format, if not proper load date helper and use mdate() http://ellislab.com/codeigniter/user-guide/helpers/date_helper.html

Upvotes: 0

Rafael
Rafael

Reputation: 2817

If the now() function exists on CodeIgniter (it doesn't on vanilla PHP), then check that it's output matches the following format: Y-m-d H:i:s, otherwise, this question could help you: Inserting NOW() into Database with CodeIgniter's Active Record, also, you can use PHP standard date and time functions instead of now()

Upvotes: 1

Related Questions