Selecting dates using PHP/MySQL and UNIX Timestamps

[cutley]

I'm building a scheduling module for a large project. I am storing a START time and STOP time in UNIX timestamp format in MySQL INT fields. I didn't use the formal timestamp field type, as I didn't want to deal with the automatic update feature associated with it. Furthremore, (I thought - not so sure now) that storing them in UNIX timestamp format would save me some steps in terms of converting back and forth.

So my table looks like this:

CREATE TABLE sched_entries (
entry_id mediumint(8) NOT NULL auto_increment,
emp_id tinyint(8) default NULL,
start int(12) default NULL,
stop int(12) default NULL,
PRIMARY KEY (entry_id)
) TYPE=MyISAM;

Just to be crystal clear, I store UNIX timestamps in the START and STOP fields. I simply need a method to test these for matches against specific days. So if the end user has asked to look at March 2003, I need to form a query that is run once for each day of March - looking for matches for that particular day. I guess the only other catch may be that I do need to order them by time so I can output them in chronological order on the calendar, but I assume I can order the results with ease - it's the select that's got me pulling my hair out all night!

If this is super simple, I apologize. I am coming from a PERL and ColdFusion background, and this is my first encounter with a calendar/schedule type application in PHP/MySQL. I did look in the PHP Function reference and the

 

[r937]

in order to determine if a unix timestamp value is within a particular month, you must convert it to a date, and then perhaps also perform a function on the date

one of the options of the FROM_UNIXTIME function is to convert the unix number to a string in the same manner as DATE_FORMAT

thus you can isolate the year and month rather easily, and do a simple string comparison

WHERE
FROM_UNIXTIME(start,'%b %Y') = 'Mar 2003'

if you are looking for schedule entries which start before but stop within that month, or start within but stop after that month, or start before and end after that month, then of course you will need ANDs and ORs

something bothers me about a comment you made in your question --

"So if the end user has asked to look at March 2003,
I need to form a query that is run once for each day
of March - looking for matches for that particular
day"

you don't actually ever do that, do you? run a query inside a loop?



rudy