Storing and retrieving dates and times in MySQL

Trying to understand best way to select a date/time and save that data?

[mylungsarempty]

To all who read: thank you for your attention and assistance.

I have been away from PHP for several years now and perhaps could have figured this out back then, but what I'm trying to realize is what is the most effective/efficient way to select a date and time in the future, store that data, and then retrieve the data and display it in a common sense way on a webpage.

Now, I know all about connecting to databases and uploading data, its this date/time thing I'm hung up on.

I want an administrative page where an administrator can quickly choose a date in the future, and a time of day, and have that data (along with other data) stored in the table.

Then, I want the date/time retrieved from the table and displayed like "Wednesday, January 23, 2010 ... 7:30PM".

If it helps to understand what I'm trying to do, the purpose of the site is about live music shows around town, and displaying them by their date and time, and by venue, etc.

I'm just trying to avoid printing something out like "01:23:2010 19:30" and avoid having to type something like that in, as well. Advice on how to lay out the form, so that anyone could understand how to use it, not just someone with knowledge of how the data is stored?

Thank you so much for reading about my dilemma and seeing if you can help me out. I truly appreciate it!

[rocknbil]

Since you're using mysql, everything you need, including formatting, can be done directly in mysql. execute the following selects via command line, or put it in a script.

Use curdate() just for DATE, now for date/time:

Raw date/time:

select now();

--> 2009-12-27 09:55:35

Now with formatting:

select date_format(now(),"%W, %M %D, %Y %l:%i:%s%p");

-->Sunday, December 27th, 2009 9:55:35AM

To do this on database values, just use the database date or datetime field instead of curdate() or now().

date_format() [dev.mysql.com]

For your future dates, make it easy, but it depends on the interface the user needs. If you want something "X days and Y hours into the future," create select lists for this - and I'd not recommend textual inputs, if a user is allowed to mess it up, they will. You will need more error checking in programming.

If you need specific dates and hours, same thing, but a set of select lists. You'd generate them problematically with loops (don't hard code them, not expandable at all) and use some naming convention like "future_month, future_day, future_year" . . . etc. Then on the input side, you'd construct a mysql insert string:

$future = "$_POST['future_year']-$_POST['future_month']-$_POST['future_day'] $_POST['future_hour']:$_POST['future_minute']:$_POST['future_second']";

$query = "insert into table(future) values('$future')";

Of course don't directly use input values, cleanse them first . . .

I'd go with the first case, as again, the tools are right in mySQL - you could use the date_add() function:

$days_in = 6; // for example only, your input form vals
$hours_in = 3;
$total_hours = int($days_in*24 + $hours_in);

$query = "insert into table (future) values (date_add(now(), interval $total_hours hour))";

All found here:
Date and Time Functions [dev.mysql.com]

[rocknbil]

^ ^ Whoops, if you're using PHP, that's intval(), not int.