Storing dates

[Mutley]

Which is the best method to select and store Dates.

 

I have 3 dropdowns, for Day, Month and Year. So do I save these to 3 separate fields or use 1 field and convert it into a "date" style format.

 

What I'm wanting to down the line, is compare the date entered to the current date (using a simple date('d-M-Y') style thing and I imagine using 3 separate fields wouldn't work as how would I convert it to a date for use in comparing to the PHP date function?

 

Thanks,

Nick.

[Mark Baker]

Store it as a datestamp type field in your database of choice. It'll make things far, far easier further down the line

[flyhoney]

Store it as a date in the MySQL table.  That way you can do easy comparisons in your SQL statements in the future:

$query = "SELECT * FROM table WHERE the_date < '" . date('Y-m-d') . "'";

 

Just join the three values from your form into a string when you insert.

[Mutley]

Store it as a datestamp type field in your database of choice. It'll make things far, far easier further down the line

 

I agree with a timestamp but how would I convert the day/month/year figures into that?

[flyhoney]

There is probably a better way to do this, but this should work:

$year = $_POST['year'];
$month = $_POST['month'];
$day = $_POST['day'];

$timestamp = strtotime("$year-$month-$day");

$datetime = date('Y-m-d H:i:s', $timestamp);

$query = "INSERT INTO table SET datetime_col = '$datetime'";

[Mutley]

Thanks a lot, now how would I match these results?

 

Say in a SQL query I want to find a certain row of a certian Day/Month/Year, say 10th Jan 2003. I guess you would do a similar thing but in reverse.

 

Kind Regards,

Nick.

[GingerRobot]

If you're only storing dates you might as well use a DATE type rather than a DATETIME  field - it'd use less space and it would make searching even easier as you can just check for equality.

[Mutley]

Ah, of course.

 

So it's just a simple insertion of:

$date = '$year-$month-$day';

 

Then can you use date() to select them? If I wanted to select just the month would I just do date('m'); or doest that only work with stamps?

 

Thanks a lot,

Nick.

[GingerRobot]

So it's just a simple insertion of:

$date = '$year-$month-$day';

 

Yep - though you'd obviously want to sanitize that data.

 

Then can you use date() to select them? If I wanted to select just the month would I just do date('m'); or doest that only work with stamps?

 

What do you mean? Are you trying to format the data when you grab it from the database? If so, then use the MySQL DATE_FORMAT() function : http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

 

 

[Mutley]

Thanks, I've nearly got it! I filter the dates like this:

 

<?php
$sql  = "SELECT date, event FROM calendar WHERE YEAR(date) >= $thisyear AND MONTH(date) >= $thismonth ORDER BY date DESC LIMIT 10";
$result = mysql_query($sql);
	while(list($date, $event) = mysql_fetch_row($result)) {
?>

 

Which works great, just how do I with PHP select just the 'day' from the date SQL field?

 

If I do

date('j',$date);

 

It doesn't work. Which I guess is because it isn't a time stamp. I can set new DATE_FORMAT in the query but it's a lot of extra code to do it for each day/month/year.