Time-Based Entries

[MinusMyThoughts]

hey, long time no questions! ...i'm looking for a good reference on using date and time in MySQL to keep track of event entries......i want to store a time, day, month, and year, and display the events in chronological order. when the date passes, i'd like to have that event move to an archive......really, all i need to figure out is how to store and sort by time and date. i found a bunch of references, but none of them really seemed clear. maybe i'm looking in the wrong places......help?...thanks!love,jason

[paramasivan]

hey, long time no questions! ...i'm looking for a good reference on using date and time in MySQL to keep track of event entries......i want to store a time, day, month, and year, and display the events in chronological order. when the date passes, i'd like to have that event move to an archive......really, all i need to figure out is how to store and sort by time and date. i found a bunch of references, but none of them really seemed clear. maybe i'm looking in the wrong places......help?...thanks!love,jason

ORDER BY date_field ASC/DESC may do.i THINK.

[MinusMyThoughts]

alright, let me rephrase; i think i was unclear......i'm hoping to have a backend page for the user where they can add an event using a dropdown for Day, Month, Year, Hour, Minutes. how do i take the contents of those dropdowns and put them into MySQL as a valid date entry?...then, how do i pull them out and display them in chronological order, starting with the next future event......does that make a little more sense than my first question?love,jason

[justsomeguy]

When I do that, I use Unix timestamps. Check this function:http://www.php.net/manual/en/function.mktime.phpIt will return an integer, which is the number of seconds since 1/1/1970. You can get the current timestamp using the time() function. If you store the timestamps in your database, then sorting and stuff becomes easy because they are all just numbers. You can get information about a timestamp using the getdate function, and you can pass a timestamp to the date function to format it.

[MinusMyThoughts]

ok, that makes sense, i think......one quick question, though: according to the format of that function, do i need to pass the seconds through if i want to add the date?so, if i have a dropdown for Hour, Minutes, Month, Day, Year, declared as variables of the same names, i would need to pass the following to the mktime() function:int mktime($hour, $minutes, '00', $month, $day, $year);...also, i'm a little confused about things like daylight savings and leap year......if i'm saving a date (say February 29th), will i need an additional function to make that work correctly? or if it's not a leap year and my user puts in February 29th, do i need an additional function to turn that to March 1st?...this time stuff really confuses me. sorry for all of the questions...love,jason

[justsomeguy]

With regard to the seconds, you're right, just use a 0. You don't need the quotes though, a simple 0 will work.With regard to the leap years, some people think the way this is handled is wrong and should result in an error, but I think it's right. For example, I could say I want the timestamp for March 40th, and it would give me the timestamp for the 40th day after March 1st (whatever that is). To me, that makes sense. So, you can put in February 29, 2004 and it will use that. Or, you can put in February 29, 2005 and it will use March 1. Basically, the way to think about it is in seconds. If I say March 40th, then I want 40 days worth of seconds after March 1st, whatever day that happens to fall on. You can say you want January 217th to get the 217th day of the year, whatever it happens to be. Like I said, some people think things like this should result in an error, but I prefer it. If you want to do an additional check, to make sure that the numbers they give are valid for an actual calendar day, you can use this function:http://www.php.net/manual/en/function.checkdate.phpAlso, it may be useful to remember that there are 86,400 seconds in a day. So if you want to get the value for a week from now, you could use:time() + (86400 * 7)

[MinusMyThoughts]

now, i read something about that a half hour ago that left me just as confused as when i hadn't read it......have you considered writing manuals? ...thanks, you've been a huge help, as always...love,jason

[MinusMyThoughts]

oh, one more quick question: what's the right starting place to re-select the values from my dropdowns when my user edits a date entry?...i'll be saving a timestamp and an eventID, plus a table entry for eventMonth, eventDay, eventYear, eventHour, and eventMinutes. each of those table entries will be determined by the value of a dropdown box with the same name as its corresponding table entry......is there a way to use the eventID to pull the entry off the database and cause my dropdowns to select the proper values to match up with the database entries? or will my user have to re-enter all the information when they edit an entry?...thanks!love,jason

[justsomeguy]

Yeah, if you get the timestamp out of the database then you can convert it to its parts and use those to select the right options in the dropdowns. You use this function to do that:http://www.php.net/manual/en/function.getdate.phpSo, something like this would work:

$dateinfo = getdate($timestamp);...<select name="month"><option value="1" <?php if ($dateinfo['mon'] == 1) echo "selected"; ?> >January</option><select name="year"><option value="2006" <?php if ($dateinfo['year'] == 2006) echo "selected"; ?> >2006</option>

Check the getdate page to see what is available. You have seconds, minutes, hours, mday, mon, and year, plus a few others.