PHP, MySQL and Date and datetime

[Stefano Mtangoo]

This have been a problem to me for long now and I want to settle it once and for all.
PHP and MySQL DateTime and date fields have been headache to me.
Suppose I have fields
------Name--------------BirthDate--------LastTimeYouAte--
------VARCHAR(255)------DATE-------------DATETIME--------

Now, When I do insert, How do I format PHP Date and DateTime to insert into db when:
1. I want to use current date
2. I want to enter the date/datetime manually (Like JQuery Calender)

Thanks

[cjohnweb]

Hey, This might not be the 100% correct way to do this, but I don't use mysql to create dates. I use PHP's built in date functions. Which ever columns are going to hold a date, set to varchar (20) or so.

You should check http://php.net/manual/en/function.strtotime.php, check out what time formats are valid. Let's say you have a calendar that returns "mm/dd/yyyy h:m:s", you can pass that to strtotime(); and get a timestamp back.

See code example:

<?php
include("config.php"); // connect to db

$current_date = date("U"); // timestamp for right now

$date_from_form = $_GET['date']; // get date from the url, or a form, etc
$past_date = strtotime($date_from_form); // convert the time from the form to a timestamp


//insert to database
$ins = mysql_query("INSERT INTO table (id,lastmeal) VALUES (id,'$past_date')");
if($ins){
echo "Added to database!";
}else{
echo "There was an error: ".mysql_error();
}

echo "<br /><hr /><br />";


// Now lets pull out all the results and convert back to a readable time format:

$gd = mysql_query("SELECT * FROM table");
$cgd = mysql_num_rows($gd);
if($cgd > 0){
while($agd = mysql_fetch_array($gd)){
extract($agd);// extract will set $id and $lastmeal

echo "ID: $id, LastMeal: ".date("r",$lastmeal)."<br />";

}
}else{
echo "There are no results!";
}


?>

Hope that helps

[Stefano Mtangoo]

great trick. Unless there is simpler way to deal with that, making chars instead of datetime is great idea.
I will leave open for more ideas.

[pritaeas]

You can insert date columns as 'yyyy-mm-dd' and datetime columns as 'yyyy-mm-dd hh:nn:ss'. This will only fail if the date is invalid. In the mysql doc comments there is a work-around for this.

As a sidenote: I disagree on the use of storing dates as chars in mysql. For one, mysql has a lot of powerful date functions, to be used in your queries. Using char columns will mean you need to do a lot of casting should you want to use them.

[cjohnweb]

Good point, but I've never had a project where any of the MySQL date functions were needed. Just need a time stamp to keep track of when they were posted, or to find out when the last time a user logged in, etc - simple stuff. I think that using some of the MySQL date functions in a query can save you a couple lines of PHP code, but rarely would it be a game breaker. If you have to do complicated SQL to accomplish a simple task I'd consider redesigning the database. Also, if you use a format other than unix timestamp, you need to do more PHP work to convert the date into something workable. I guess it really depends on the needs of the specific project.

[Stefano Mtangoo]

Thanks guys. I have found timezones, Unix timestamps et al little bit confusing...and since I don't usually deal with Date/Times a lot...

[cjohnweb]

The unix time stamp is the number of seconds since Jan 1st, 1970 midnight (something like that). Because we are dealing with numbers like this, you can add and subtract seconds, minutes, hours, days, weeks, months and years - do whatever you need to do with the date - and display the proper date What if it's a leap year? Using the time stamp lets you do what you need to do with the date, then display it without worrying about things like "how many days were in that month, is it June 31st, or July 1st". You know, what if it's a leap year, how are you going to know? Unix time stamp keeps track of the relative date to Jan 1st 1970 12am, and then when you convert the time stamp to a date, PHP handles all the leap year stuff, etc.

Does that help with time stamps?

So timezones.

First read this: http://en.wikipedia.org/wiki/Time_zone

Your PHP system has a timezone set for where the server is, but your users might be in a different timezone and see the wrong time. So you should use Javascript for displaying dates the user will see.

Note Do not use Javascript / AJAX to change PHP's timezone users. For example, If you timestamp when a failed login takes place and limit how many login attempts the user can do within that time, a hacker might set a different timezone with every few attempts and get around it.

Does this help?

[Stefano Mtangoo]

The unix time stamp is the number of seconds since Jan 1st, 1970 midnight (something like that). Because we are dealing with numbers like this, you can add and subtract seconds, minutes, hours, days, weeks, months and years - do whatever you need to do with the date - and display the proper date What if it's a leap year? Using the time stamp lets you do what you need to do with the date, then display it without worrying about things like "how many days were in that month, is it June 31st, or July 1st". You know, what if it's a leap year, how are you going to know? Unix time stamp keeps track of the relative date to Jan 1st 1970 12am, and then when you convert the time stamp to a date, PHP handles all the leap year stuff, etc.

Does that help with time stamps?

So timezones.

First read this: http://en.wikipedia.org/wiki/Time_zone

Your PHP system has a timezone set for where the server is, but your users might be in a different timezone and see the wrong time. So you should use Javascript for displaying dates the user will see.

Note Do not use Javascript / AJAX to change PHP's timezone users. For example, If you timestamp when a failed login takes place and limit how many login attempts the user can do within that time, a hacker might set a different timezone with every few attempts and get around it.

Does this help?

Thanks a lot. It have helped me a lot with dates

Note Do not use Javascript / AJAX to change PHP's timezone users. For example, If you timestamp when a failed login takes place and limit how many login attempts the user can do within that time, a hacker might set a different timezone with every few attempts and get around it.

It is a good warning without suggestion :)

[Stefano Mtangoo]

It is a good warning without suggestion :)

I will mark it solved, but you can add anything :)