[SOLVED] Help using dates

[kjtocool]

I am pretty new to dates, and have never really worked with them before.  Now I am building an application which will need to compare dates, and where the date will play an important role, I am hoping you guys can help me with some simple questions I come upon as I go.

 

The first thing I am trying to do is create an Admin section which lets someone select a date at which to stop allowing users to predict something, and then select a date range which is used for something else.

 

All three dates are sent to PHP via post variables in the format DD/MMM/YYYY.  Examples:

 

Prediction End: 29/DEC/2007

Weekend Start: 31/DEC/2007

Weekend End: 31/DEC/2007

 

 

I then need to add a time (12:01 am) to the Prediction End time.  So I want it to be 29/DEC/2007 00:01:00.  And then I want to store all my values in my database.

 

So my first issue is, how should I go about re-formatting the dates my form is sending so that they will fit into valid MySQL date formats?  If I understand correctly, the values should be like '2007-12-29 00:01:00' right?

 

 

Questions:

 

1) Is 00:01:00 equal to 12:01 am?

2) How should I go about re-fomrating the given dates?  Should I write a function which takes DEC, JAN, etc and turns them to 12, 01, etc?  Is the easiest way to do that creating an exploding an array on "/"?  Can you explode as an associative array?

 

 

 

 

 

[p2grace]

I would use a JS Calendar to submit your dates through, that way you can ensure the data being inserted is correctly formatted.  You'll still have to validate the data just in case they have JS disabled.  The calendar I typically use is called jsCalendar at http://sourceforge.net/projects/jscalendar, and it supports selecting the time as well and will put it in any format you specify.

[revraz]

The best option is either using Unix Timestamp or MYSQL Timestamp (if you are using a DB).  That will give you the best options for dates.

[kjtocool]

I actually do use a JS calendar:

 

 

But the format it outputs is as I specified above.  I'll download the one you linked to and give it a look, I assume it will output in the correct format?

[p2grace]

It'll output in the correct format yes, but it'll only use one text field instead of 2 drop downs and a text field.  Which is actually better because there's less string manipulation.

[kjtocool]

Yeah, I am looking at it now, and it just uses one text field to show the date in, which, as you said, is better.  I'll give this a try and see if I can't get it to work.

 

In the meantime, can anyone tell me:

 

1) Is 00:01:00 equal to 12:01 am?

2) Is '2007-12-29 00:01:00' the format I should be storing if my MySQL database is expecting a DATETIME variable?

[p2grace]

Both of your questions are correct.

[kjtocool]

With the calender you linked to, how do I properly change the format so that it will be as above?

 

When it's set as:

 

ifFormat       :    "%m-%d-%Y %I:%M %p",       // format of the input field

 

It returns: 12-12-2007 01:33 AM

 

I tried changing it to:

 

ifFormat       :    "%m-%d-%Y %H:%i:%s",       // format of the input field

 

But that didn't work.  Any suggestions, I didn't see any relevant examples in their documentation.

[p2grace]

Hmm try changing it to this and let me know what it returns.

 

ifFormat       :    "%Y-%m-%d %i:%M",       // format of the input field

[kjtocool]

ifFormat       :    "%Y-%m-%d %i:%M",       // format of the input field

 

Returns: 12-13-2007 %i:34

 

 

I tried this:

 

ifFormat       :    "%Y-%m-%d %I:%M",       // format of the input field

 

Returns: 12-13-2007 11:34

 

 

But in all honesty, I don't know why I'm even bothering, I should just take the time out, as I really want it hard coded as 12:01 am.  Nifty feature though.  Thanks for the help.

[p2grace]

Oh then yeah in that case just remove the time.  If this solves your problem can you please update the post to "SOLVED".  Thank you 

[kjtocool]

OK, so I went ahead and utilized the date script you suggested.  It looks like this:

 

 

The script is:

 

<?php
$prediction_end = $_POST['prediction_end'];
$prediction_end .= " 00:01:00";
$weekend_start = $_POST['weekend_start'];
$weekend_end = $_POST['weekend_end'];

$databaseConnect = mysqli_connect("localhost", "user", "password", "database")
		Or die("Unable to connect to the database.");

$query = "INSERT INTO bonanza_weeks VALUES (NULL, '$prediction_end', '$weekend_start', '$weekend_end')";
echo $query;

if (mysqli_query($databaseConnect, $query)) {
	echo "New week successfully created with values: <br /><br />";
	echo "Prediction End: " . $prediction_end . "<br />";
	echo "Weekend Start: " . $weekend_start . "<br />";
	echo "Weekend End: " . $weekend_end . "<br />";
}
else {
	echo "Unable to create new week.";
}

mysqli_close($databaseConnect);
?>

 

 

Output of the script is:

 

INSERT INTO bonanza_weeks VALUES (NULL, '12-28-2007 00:01:00', '12-28-2007', '12-30-2007')

New week successfully created with values:

Prediction End: 12-28-2007 00:01:00
Weekend Start: 12-28-2007
Weekend End: 12-30-2007

 

But the values entered into the database are:

 

1  	0000-00-00 00:00:00  	0000-00-00  	0000-00-00

 

The values are obviously out of order.

 

Should I just switch the year to first position?  Is the second value the month or the day?

[p2grace]

That's correct mysql needs the dates to be inserted like this:

 

INSERT INTO bonanza_weeks VALUES (NULL, '2007-12-28 00:01:00', '2007-12-28', '2007-12-30')

[kjtocool]

Yep, worked great, thanks.