How to handle dates and times in MySQL and PHP?

Question

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 ?

Previous post that might help: http://stackoverflow.com/questions/409286/datetime-vs-timestamp — Revent, Aug 26 '13 at 21:15

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

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

edited May 23 '17 at 12:29

Community

1
1

answered Aug 26 '13 at 19:28

Rafael

2,827
1
16
17

yes, its some kind of date helper provided by ci, while doing `now()`, this is outputed `1377551478`, i want to store the data in this format (timestamp), but, it just won't let it be, I have always this in `markers.date_end` -> `0000-00-00 00:00:00` even when column's type is set to `TIMESTAMP`... ? Thanks for the link, checking it currently – aspirinemaga Aug 26 '13 at 21:14
btw, the link you provided, I already tried it, and its now working, i think the problem is somewhere in MYSQL processing, because it just wont update the data while doing as suggested in another post – aspirinemaga Aug 26 '13 at 21:18
I would like to add this to your answer, pls consider to update your post, so I could accept it. I did it with the use of MySQL, instead of PHP-way (http://stackoverflow.com/questions/3887509/mysqls-now-1-day) – aspirinemaga Aug 27 '13 at 09:24

score 0 · Answer 2 · answered Aug 29 '13 at 13:36

0

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

answered Aug 29 '13 at 13:36

Abhishek Salian

928
2
10
27

pgee70 · Answer 3 · 2013-09-19T11:55:52.283

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

How to handle dates and times in MySQL and PHP?

3 Answers3