0

I've got a following database entry:

id                date                start_time 
1                 2015-12-25          08:00:00
2                 2015-12-30          08:00:00
3                 2015-12-30          09:00:00

Now I just want to select the date of those entries where both start_time entries 08:00:00 and 09:00:00 exists.

I tried to use this SQL query:

$sqlquery = mysqli_query($myconnection,"SELECT date 
                                        FROM mytable 
                                        WHERE start_time LIKE '08:00:00' 
                                        AND '09:00:00'") or die ("crashed");

But it returns me both dates 2015-12-25 and 2015-12-30. It should only return 2015-12-30 because for this date 08:00:00 and 09:00:00 exists.

I want to select those dates which have an entry for 08:00:00 and 09:00:00 too.

It should not select dates with only an entry for 08:00:00 but none for 09:00:00 and also not those which have an entry for 09:00:00 but none for 08:00:00.

Improve this question
13
  • 2
    Do you want only rows where start_time = '08:00:00' OR start_time = '09:00:00' OR dates where the time in start_time is anything BETWEEN 08:00:00 and 09:00:00 This may sound like a subtile difference but it is not Commented Nov 12, 2015 at 14:25
  • will the times always be the same (i.e. 8:00 and 9:00)? or do you need to see a count of the start times of a specific date? Commented Nov 12, 2015 at 14:26
  • Okay, I see I need to explain more clear. I want to select those dates which have an entry for 08:00:00 and 09:00:00 too. It should not select dates with only an entry for 08:00:00 but none for 09:00:00 and also not those which have an entry for 09:00:00 but none for 08:00:00. Commented Nov 12, 2015 at 14:31
  • Why only 08:00:00 or 09:00:00 ?? Not 08:00:01 or 09:00:02 Commented Nov 12, 2015 at 14:33
  • Thats not important @NanaPartykar I need this strict declarations for my further code. Commented Nov 12, 2015 at 14:34

3 Answers 3

Reset to default
2

Don't use like for date/time columns. Here, you seem to want between:

SELECT date
FROM mytable
WHERE start_time BETWEEN '08:00:00' AND '09:00:00';

Your original formulation is parsed like this:

WHERE (start_time LIKE '08:00:00') AND '09:00:00'

The second part is a string value in a boolean/integer context. It gets converted to 9, which is always true. So, the where clause ends up being equivalent to:

WHERE start_time = '08:00:00'

EDIT:

Your clarification changes my understanding of the question. If you want days that have both times, use aggregation:

SELECT date 
FROM mytable 
WHERE start_time IN ('08:00:00', '09:00:00')
GROUP BY date
HAVING COUNT(*) = 2;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I think you are assuming too much by coding a BETWEEN as it is far from obvious from the question. (not my dv)
@coder . . . I misinterpreted the original question (my answer was an intelligent guess as to what you really want). A simple aggregation with HAVING should solve your problem.
1

i assume that you basically want to select date that has both '08:00:00' and '09:00:00', then you should not use 'BETWEEN'. try this query:

SELECT t1.date
FROM mytable AS t1
INNER JOIN mytable AS t2 ON t1.date = t2.date
INNER JOIN mytable AS t3 ON t1.date = t3.date
INNER JOIN mytable AS t4 ON t1.date = t4.date
WHERE t1.start_time = '08:00:00' 
    AND t2.start_time = '09:00:00'
    AND t3.start_time = '10:00:00'
    AND t4.start_time = '11:00:00'
GROUP BY t1.date

or you can try another approach

SELECT t1.date
FROM mytable AS t1
GROUP BY t1.date
HAVING SUM(IF(t1.start_time = '08:00:00', 1, 0)) > 0
AND SUM(IF(t1.start_time = '09:00:00', 1, 0)) > 0
AND SUM(IF(t1.start_time = '10:00:00', 1, 0)) > 0
AND SUM(IF(t1.start_time = '11:00:00', 1, 0)) > 0
Improve this answer

4 Comments

Except you can use = instead of LIKE
This works just fine! Thank you. Could you please tell me how the code would look like if I had 5 times and only those dates which have all 5 times get selected? Like 08:00:00, 09:00:00, 10:00:00, 11:00:00, 12:00:00
This is exactly what I warned about in your question, now 8:00:00 is hard coded
yes, it would be long query as the start_time conditions is added :). i added another approach
0

As mentioned in the comments there are different ways to achieve it depending on what you actually want to do with the result.

  1. Easy->Just count the records with specific dates

    select date, count(start_time) 
from mytable 
group by date 
having count(start_time) > 1

2.Advanced->Display the records by using a case

   select * 
   from (
          Select date,
                case when start_time = '08:00:00' then 1 end as startat8, 
                case when start_time = '09:00:00' then 1 end as startat9 
           from mytable
         ) a 
    where a.startat8=1 and a.startat9=1;
Improve this answer

6 Comments

option 1 will not look at the specific times in the table just if there are more then 1 entry and option 2 will specifically look for 8:00 and 9:00, be sure the time is written in the db exactly like it is in the query
there was an error with option 1, I updated the answer
I'm sorry both options do not work. The first one delivers no result and when running the second one the mysqli query dies.
hold on I will test option 2, but have you tried option 1 after my update?
@Riggsfolly thanks, but now the time in the first case is not right
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.