-1

I have a SQL column like this:

| id | name | date | hour |

I want to select all names between specified start_date, start_hour and end_date, end_hour.

For data like this:

| 1 | one   | 2014-12-29 | 11:00 |  
| 2 | two   | 2014-12-30 | 09:00 |  
| 3 | three | 2014-12-30 | 11:00 |

Values: 

start_date = 2014-12-29
start_hour = 11:00
end_date = 2014-12-30
end_hour =  10:00`

It should return: one, two.

trox
  • 1
  • If you can save your date time as a timestamp then http://stackoverflow.com/questions/10907750/calculate-difference-between-two-datetimes-in-mysql link would help you. – jewelhuq Dec 29 '14 at 22:26
  • If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Dec 29 '14 at 23:24

2 Answers2

-1
SELECT *
FROM   table
WHERE  date > '2014-12-28'
       AND hour > '10:59'
        AND date < '2014-12-31'
           AND hour < '10:01'
-1

This looks like a basic sql query. try the following:

select a.name 
       from (select * from data_table 
                      where date >= start_date
                      and   date <= end_date) as a
       where a.hour >= start_hour
       and   a.hour <= end_hour

the basic idea is to first form a data set of all valid days for all times and then from there pull out the valid times. The other possibly more direct method would be to create a datetime field and then pull on that key.

Let me know if this works for you.

DCR
  • 14,737
  • 12
  • 52
  • 115
  • 1
    Thanks for reply. It works only for the same hours in all days and that's not exactly what I want. Creating a new field will be propably the best solution. – trox Dec 29 '14 at 23:04
  • are you trying to do this in sql or are you programming in something like php or c? If you're in a programming language you could try using a for loop. That way for each start and end date you could have a unique start and end time. – DCR Dec 30 '14 at 16:04