MySQL "Date math" problem

[jrandomhacker]

Hello all,

I apologize if this has been discussed before, I searched the forum and didn't find anything relevant. I've narrowed down the problem to my MySQL SELECT statement. I haven't even tried this in PHP yet, this is still on the MySQL CLI. First some basic info:
Mysql 4.1.11 Standard
PHP 4.3.2-19

I'm writing a scheduling application for some of our employees. I'm currently stuck in a section of code that checks a range of days to see whether or not a particular employee is already scheduled. Relevant columns are listed below:
TABLE JOB_DETAIL
START_DATE date
END_DATE date
EMPLOYEE_1 int(11) unsigned

EMPLOYEE_1 is an int that I join to another table with more employee detail. I have an example job in the database with a START_DATE of '2005-05-08' and an END_DATE of '2005-05-12'.

My SELECT statement is as follows:
SELECT * FROM JOB_DETAIL WHERE START_DATE >= '2005-05-08' and END_DATE <= '2005-05-09' AND EMPLOYEE_1 = 1;

and I will always get "0 rows returned".

This is an example query if I were trying to schedule an employee for a job and he was already scheduled (for May 5th through the 12th, my example data). In my mind this should return the example job listed in the database. In my PHP code this would return a row and I would flag it as a conflict.

MySQL doesn't seem to really support the >= and <= when operating on dates. I've tried using 20050508 and variations but it still doesn't seem to be working. Does anyone know of a workaround to this problem? I tried using UNIX_TIMESTAMP, but I can't figure out how to get the START_DATE and END_DATE columns into the UNIX_TIMESTAMP function. Any ideas?

Thanks,
Eric

[devinemke]

SELECT * FROM JOB_DETAIL WHERE START_DATE >= '2005-05-08' and END_DATE <= '2005-05-09' AND EMPLOYEE_1 = 1; 

if you are getting 0 results then there are obviously no records matching that exact criteria.

BTW: you absolutely CAN use the less than/greater than operators with dates in mySQL.

[jrandomhacker]

I'm sorry, you are absolutley right!. My code had a logic error in it. The query I think I need to use is:

SELECT *
FROM JOB_DETAIL
WHERE (START_DATE >= '$start_date'
AND END_DATE <= '$end_date')
OR (START_DATE <= '$start_date'
AND END_DATE >= '$end_date')
AND EMPLOYEE_1 = 1;

This seems to work, but I don't understand why. I just want to see if either the $start_date or $end_date passed to the database via a form fall within any date range in the database specified by the columns START_DATE and END_DATE. Again, the primary goal of this query is to check for a conflict in dates for an employee.

Is this right, or is there a more elegant query? Here is a psuedocode query that may help to understand what I'm looking for.

SELECT * FROM JOB_DETAIL WHERE THE REQUESTED_START_DATE IS BETWEEN START_DATE AND END_DATE
OR
WHERE THE REQUESTED_END_DATE IS BETWEEN THE START_DATE AND THE END_DATE
WHERE EMPLOYEES = 1;

My head hurts! Thanks for the responses so far!

--Eric

[tekky]

why not use BETWEEN?

[jrandomhacker]

Tekky,

I looked at the BETWEEN syntax in the manual. I didn't even know that existed. I like your thinking here, it sounds like this may be a good solution. I'm working up some queries now.

[tekky]

Originally posted by jrandomhacker
Tekky,

I looked at the BETWEEN syntax in the manual. I didn't even know that existed. I like your thinking here, it sounds like this may be a good solution. I'm working up some queries now.

Hehe, I'm sure its slightly faster then multiple >= <= comparisons and it's designed for things like that 😉

[jrandomhacker]

I looked at between, but the same logic problem still gets me. If my employee is scheduled for 2005-05-08 through 2005-05-12, and I attempt to schedule him for 2005-05-09 through 2005-05-10 using this query:

SELECT *
FROM JOB_DETAIL
WHERE (START_DATE BETWEEN '2005-05-09' AND '2005-05-10')
OR (END_DATE BETWEEN '2005-05-09' AND '2005-05-10') AND EMPLOYEE_1 = 1;

This clearly will not work. Is there a query that will check to see whether or not the dates I submit fall within a pre-existing date range? I swear I'm not a total n00b, but I just cannot seem to wrap my head around this one. I wish I could just take a day off to get a fresh start, but I'm on a schedule. Any ideas?

--Eric (slowly going crazy!)

[tekky]

whats your schema look like? that would help make this easier to figure out 😉

[edit]
On second thought.... try this...

SELECT * 
FROM JOB_DETAIL 
WHERE (START_DATE BETWEEN '2005-05-09' AND '2005-05-10' 
OR END_DATE BETWEEN '2005-05-09' AND '2005-05-10') AND EMPLOYEE_1 = 1; 

proper parenthesis 😉
[/edit]

[jrandomhacker]

The schema itself is pretty big, this particular table is JOB_DETAIL, and it holds all sorts of information on a particular job. The relevant tables are as follows:

JOB_ID INT NOT NULL AUTO_INCREMENT
START_DATE date NOT NULL
END_DATE date NOT NULL
[lots of other stuff]
EMPLOYEE_1 int
EMPLOYEE_2 int
EMPLOYEE_3 int
EMPLOYEE_4 int
EMPLOYEE_5 int
EMPLOYEE_6 int

The EMPLOYEE_X values will join to another Employee detail table that has more information (name, etc).

So if I have a job in the database for may 9th through the 12 using employee 1, and I want to add another job for him via a for (and using PHP), I need to check for a conflict first. I want to query the database to see if the dates I entered for him (for the new job) conflict with any other job in the database that may already exist. If the mysql_num_rows($query) >= 1, then I know there is a conflict and I can do additional queries to find the information. So I use the query in the previous post to look for conflicts, but this is where I'm stuck.

I can't seem to find a suitable query that will see if the passed start and end dates from the form (in the format of YYYY-MM-DD) for a given employee conflict with the dates of a job already in the database. I know I'm making this harder than it is. Ack!

--Eric

[jrandomhacker]

Ugh. I solved it. It's ugly, and I'm embarassed to even post it. Not very hackish, and not very elegant:

SELECT *
FROM JOB_DETAIL
WHERE ('2005-05-01' BETWEEN START_DATE
AND END_DATE
OR '2005-05-02' BETWEEN START_DATE
AND END_DATE
OR (START_DATE BETWEEN '2005-05-01' AND '2005-05-02')
OR (END_DATE BETWEEN '2005-05-01' AND '2005-05-02'))
AND EMPLOYEE_1 = 1

Works great. Thanks everyone for the help!

--Eric

[tekky]

you should be able to use only half of that query....

logic:

1 and 5 is a job
4 and 9 is the new job

if (4 between 1 and 5 or 9 between 1 and 5) == true
now why bother doing this added comparison...
if (1 between 4 and 9 or 5 between 4 and 9) == true also ? 😉

[jrandomhacker]

Hmm. What about this?

2 and 5 is a job
1 and 9 is the new job.

The second job is longer than the first, and begins before and ends after (whew).

if (1 between 2 and 5 or 9 between 1 and 5) == false
if (2 between 1 and 9 or 5 between 1 and 9) == true

The second half is designed to catch those instances...that's the only way I could think of to catch those circumstances.

--Eric

[tekky]

Simple... dont schedule any jobs like that 😃 Ok so you got me... didn't think of that one 😉

Originally posted by jrandomhacker
Hmm. What about this?

2 and 5 is a job
1 and 9 is the new job.

The second job is longer than the first, and begins before and ends after (whew).....
--Eric