0

I have a three column startTime and EndTime as a Time datatype and amount as a int. I want to compare time if 10:00:00 is in between startTime and Endtime it should return the amount in that range

My startTime is 08:00:00 and endTime is 14:00:00

I tried 

SELECT amount
  from table 
 where startTime >='10:00:00' 
   AND endTime <='10:00:00'

But it does not work I even tried CAST as time, it did not work too.

I tried looking at other post but none of suggested solution worked.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
helpmeiamnoob1111
  • 3
  • 2
  • Possible duplicate of: [calculate-difference-between-two-datetimes-in-mysql](https://stackoverflow.com/questions/10907750/calculate-difference-between-two-datetimes-in-mysql) – Brien Foss Feb 24 '18 at 19:52
  • I dont think so, that post is to find difference between two datetimes, i am asking about comparison with TIME as a data type. – helpmeiamnoob1111 Feb 24 '18 at 19:55
  • You can glean the answer from that post. Using that post I found [TIMEDIFF()](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timediff) – Brien Foss Feb 24 '18 at 19:57
  • I'm sorry, the way you worded your question led me to believe that you wanted to find results where there were 10 hours **between** the startTime and endTime. Seems @Shadow understood what you meant. Surprised you hadn't tried what they proposed. Oh well – Brien Foss Feb 24 '18 at 20:01
  • 8 and 14. Really? – Strawberry Feb 25 '18 at 00:02

1 Answers1

-2

If you want '10:00:00' to be between start and end times, then you got the relations wrong because '10:00:00' should be after the start time and before the end time:

SELECT amount from table where startTime <='10:00:00' AND endTime >='10:00:00'
Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Thank you, but i am kinda confused the code u wrote kinda work but if the startTime is 23:00:00 and endTime is 02:00:00 and the time to compare is 00:00:00 is still return nothing. Otherwise with the example i provided above your code works.Sorry for bad english – helpmeiamnoob1111 Feb 24 '18 at 20:14
  • It should not return anything because your end time is earlier than your start time, screwing up the logic. You need date to indicate your end time is later than the start time and use 24 hours instead of 0. – Shadow Feb 24 '18 at 20:25