Date formatting doesnt work

idletask

How do you deal with dates in mySQL/PHP (I'm new to this).

My table has a DateTime field, and I insert the results of the time() function to it. The table always shows 00:00:00 etc for the value. (this is in phpMyAdmin).

I changed the table types and found that when I use something like bigint(20) I see the same value of the time() function in there as when I output my sql in the webpage to see what it's putting in the database.

When I apply the date formatting function from PHP on the number that was in the bigint field, (the thing that's suppose to be the date) with this type of code:

date("j-M-Y", $myrow['date_entered']) I get a date of dec 31 1969????

I'm confused. All I want to do is dump the date to a field in my DB and then get it back in a user readable format.

Sorry if I asked a question that's been asked a million times, but any answers will be welcome!

Thank you.

Just to recap in case my problem wasnt clear:

I am inserting the value returned from time() into a field in my table.

When the field type is datetime, the value in the field is always zeros. (00:00:00 00:00:00). When I change the type to bigint(20) and insert the same time() value I see the same number that I test-output to a web page.

When I use the bigint type, and pull it back out, and apply the date() function to it to format it, I get a year of 1969 and because of this I am stymied.

UGH!

drtebi

Hello,
you're on the right track as from what I can see.

I suggest you create a int() type field in your MySQL database, and then use the time() function from PHP and insert that timestamp into the int() field.

So, you simply generate your variable as in
$time_stamp = time();
And then enter it into the database, something like
$query = "INSERT INTO db VALUES('bla', 'bla', '$time_stamp')";

The trick is how to receive the data. On one hand, you could simply receive the same number and get the date through PHP, by using the strftime() function. Or, the way I prefer it, use a MySQL function like so:
$query = "SELECT FROM_UNIXTIME(time_stamp) from db";
this returs something like this:
2000-10-13 01:45:00

You can take it further and use the FROM_UNIXTIME function's formatting like this:
$query = "SELECT FROM_UNIXTIME(time_stamp, '%M %D, %Y');
and you get something like this:
October 13th, 2000
(for more formatting look at http://www.mysql.com/doc/en/Date_and_time_functions.html)

That way you write one PHP function less, and get the data formatted the way you want already from the database.
It's a matter of taste which method you prefer.

cheers