Parsing datetime from MySQL

[eluser]lenwood[/eluser]
I'm working to create a full featured blog with CI. I'm storing the published date & time in MySQL in the 'datetime' format. I'd like to separate the date and time within that string so that they can be used independently. I've read through the user guide and several tutorials as well, and this isn't coming together for me.

On most pages I'd like to show just the date. On the pages where I do include the time, I'll want it separate so that it can be formatted differently.

Can anyone help?

[eluser]xeroblast[/eluser]
you could use:

date only:

Code:

echo date("m/d/Y",strtotime($from_mysql_datetime));

time only:

Code:

echo date("H:i:s",strtotime($from_mysql_datetime));

both:

Code:

echo date("m/d/Y H:i:s",strtotime($from_mysql_datetime));

make sure your datatype in mysql is datetime or timestamps.

[eluser]mddd[/eluser]
Just use the Date functions from Mysql to get the time and date just the way you want them.
See http://dev.mysql.com/doc/refman/5.1/en/d...tions.html .

You can select the date and time and format it just the way you want. All in 1 simple query.
Use the DATE_FORMAT function to get the right format. You won't have to do anything in php.

[eluser]Zeeshan Rasool[/eluser]
In my blog, I use in my SQL query where i was using only month from date field:

Code:

WHERE MONTH(tbl_blog_post_data.blog_post_pub_date) = ".$month."

and if i need to get month as result then:

Code:

DATE_FORMAT(blog_post_pub_date, '%M') AS month

Hope it helps !

[eluser]lenwood[/eluser]
Thanks all, this got me fixed up. I went with xeroblast's method because it allows me to specify the format of the date & time as well. Took me about 5 minutes to plug in the code and set the formatting that I want.

[eluser]JoostV[/eluser]
If you are dealing with a lot of records you may want to switch to @Zeeshan's suggested method. It's faster to have the date formatted by MySQL.

[eluser]lenwood[/eluser]
Thanks JoostV, that's good feedback. I'll take a closer look at that.

[eluser]lenwood[/eluser]
Actually, I've given this a bit of thought, and I do have a follow up question. I'm using the active record class to pull an entire row from the table and store it in an array.

Model:

Code:

function single_post()
{
    $this->db->select('*');
    $this->db->from('blog');
    $this->db->where('slug', $this->uri->segment(3));
    
    $q = $this->db->get();
    
    if($q->num_rows() > 0) {
        $row = $q->row_array();
        
        return $row;
    }
}

My controller passes this array on to the view, which is where I've used the bit of code that xeroblast suggested to split 'datetime' into date and time.

View:

Code:

<h2>&lt;?php echo $title; ?&gt;</h2>
<p>&lt;?php echo date("M/d/Y", strtotime($datetime)); ?&gt;</p>
<div>&lt;?php echo $body; ?&gt;</div>
<p>&lt;?php echo date("H:i:s", strtotime($datetime)); ?&gt;</p>

I'm not confident in my code at all (other than the fact that it does what I'm looking for) and am eager to learn, so your feedback is welcome. Now that you see how I'm accessing the data, do you still think it would be better to use SQL statements for the date & time?

[eluser]mddd[/eluser]
When you are only working with one single record, the performance issue will be probably not be noticable. So you don't have to change it for the sake of performance. But I think it is not the best way to take a date as a string from Mysql only to have Php turn it back into a date (with the strtotime() command) and then turning that back into a readable date again (with the date() command). That is like taking a detour. You can get the formatting straight from Myqsl using the Date_format() function in Mysql. It works just like the date() command in Php.

Code:

// in model
$this->db->select("*, date_format(date, '%M/%d/%Y') as human_date, date_format(date, '%H:%i:%s') as human_time", false);

// in view
<h2>&lt;?php echo $title; ?&gt;</h2>
<p>&lt;?php echo $human_date; ?&gt;</p>
<div>&lt;?php echo $body; ?&gt;</div>
<p>&lt;?php echo $human_time; ?&gt;</p>

[eluser]lenwood[/eluser]
Thanks mddd. It took me a while of fiddling with syntax to get this to work, but now your code is in place and functioning. Can you tell me what the last param in the SQL query, false, is there for? I didn't find that in the documentation.