mdate returning 12/31/1969 in date_helper.php

[eluser]johnnyForums[/eluser]
I am trying to use the date_helper as per the documentation. I have this date:

1999-10-20 00:00:00

from my mysql database.

I tried in my view: <?php echo 'the dob :' . mdate('%m' . '/' . '%d' . '/' . '%Y', $row->DOB);?>

but I get back:

12/31/1969

However, if I change date_helper.php to have:

$date = date($datestr,strtotime($time)); //added this line
return $date; //date($datestr, $time); //line 95 now; broken returns 12/31/1969 each time;

I get the right thing back.

What is special about 12/31/1969 php and mysql? I have seen a lot of threads about mysql from lots of different apps and the 1969 date. What if the person's dob is 12/31/1969?

Is this a bug? Also, how can I get something more graceful if the date is empty instead of 12/31/1969? I can put in an if endif in my view but I'm starting to mix a little too much code into my view. Are there any ramifications to putting the strtotime there in date_helper.php?

Also, curious. How would I view the SQL that was really used here?

Thank you.

[eluser]David Johansson[/eluser]
The time functions in php is built on unix timestamps which stores the seconds since 1970-01-01 00:00:00 in an integer. The fact that you get 1969-12-31 is probably due to some timezone setting. When you receive an integer from mysql it is always returned as a string and the date function is apparently unable to work with the string.
Also if there is someting wrong with your database query the value might be undefined. To view your last query use the command:

Code:

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

There might be some problem with the active record class trying to escape your query. This can easily be solved.

If you cant get it to work (which you should) you can use the mysql function date instead. Though i don't remeber the syntax of the function right now...

[eluser]johnnyForums[/eluser]
[quote author="David Johansson" date="1248316017"]The time functions in php is built on unix timestamps which stores the seconds since 1970-01-01 00:00:00 in an integer. The fact that you get 1969-12-31 is probably due to some timezone setting. When you receive an integer from mysql it is always returned as a string and the date function is apparently unable to work with the string.
Also if there is someting wrong with your database query the value might be undefined. To view your last query use the command:

Code:

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

There might be some problem with the active record class trying to escape your query. This can easily be solved.

If you cant get it to work (which you should) you can use the mysql function date instead. Though i don't remeber the syntax of the function right now...[/quote]

That gives me select * so I don't really know what is being passed back and forth.

[eluser]David Johansson[/eluser]
Are you pulling the date from the database?
could you show some code?

[eluser]johnnyForums[/eluser]
[quote author="David Johansson" date="1248316620"]Are you pulling the date from the database?
could you show some code?[/quote]

In my controller:

Code:

$data['title'] = 'My New Title';
        $data['heading'] = 'My New Heading';
        $this->db->select('*');
        $this->db->from('tblProducts');
        $this->db->join('tblProductDetails', 'tblproducts.ProductID = tblProductDetails.ProductID');

        
        $this->db->where(''tblProductDetails.ProductID',$this->uri->segment(3));
        $data['query'] = $this->db->get();
        $this->load->view('product_view',$data);

And in my view, I now have:

Code:

<?php if ($row->purchaseDate !='') {echo 'purchase date ' . mdate('%m' . '/' . '%d' .  '/' . '%Y', strtotime($row->purchaseDate));}?>

I had, which prompted the initial post:

Code:

<?php echo 'purchaseDate ' . mdate('%m' . '/' . '%d' .  '/' . '%Y', $row->purchaseDate);?>

This last line would not work unless I changed date_helper to have strtotime or use it in my view.

[eluser]David Johansson[/eluser]
Ah, now i understand (i just slow, it's past midnight here). since the mdate function should be almost identical to the native php date function there won't be any changes in the date helper by adding strtotime. if you don't want to add strtotime manually you should probably just create your own helper function.

[eluser]sophistry[/eluser]
you could just use the sql date_format function and avoid all of that PHP code. you have the date, just format it in sql and all you have to do in the view is echo it.

not tested code:

Code:

$this->db->select("*, DATE_FORMAT(purchaseDate, '%m/%d/%Y') AS formatted_date");

BTW, mdate() needs a timestamp, not a date string, that's why you were able to "fix" it by using strtotime().