2

I have a table like this

UserID INT resetTime VARCHAR
2 20:00
3 21:00
4 22:00
5 23:30
6 23:50
7 0:00
8 0:10
9 5:00

I want to select all userID that have "resetTime" between 23:50 AND 0:10.

example here: http://sqlfiddle.com/#!9/174565/1

Kaii
  • 20,122
  • 3
  • 38
  • 60
TheCesco1988
  • 280
  • 1
  • 2
  • 10
  • 1
    `select * from t where timetoreset >= '23:30' or timetoreset <= '0:20'` maybe? – The Impaler Aug 11 '22 at 15:22
  • Does this answer your question? [How do I query between two dates using MySQL?](https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql) Though I guess it depends what type your data is. If you have stored it as a string, it might make sense to cast it to a duration. – Aaron Morefield Aug 11 '22 at 15:22
  • @TheImpaler not works. That returns 0 rows – TheCesco1988 Aug 11 '22 at 15:25
  • @AaronMorefield The problem is that I don't have a date but only hours and minutes. Every day a cron job runs and if the datetime is between the result cron erase all userdatas else no – TheCesco1988 Aug 11 '22 at 15:28
  • @TheCesco1988 Can you provide the example in a fiddle? https://dbfiddle.uk/?rdbms=mysql_8.0 – The Impaler Aug 11 '22 at 15:55
  • @TheImpaler http://sqlfiddle.com/#!9/174565/1 – TheCesco1988 Aug 11 '22 at 16:18

1 Answers1

1

Declare the column as TIME instead of VARCHAR:

ALTER TABLE test MODIFY resetTime TIME;

Then you can query like this:

SELECT * from test 
WHERE 
  resetTime >= "23:50"
  OR resetTime <= "0:10"

Or:

SELECT * from test 
WHERE 
  resetTime >= "05:00"
  AND resetTime <= "05:30"

Note the different and/or logic, depending on wether end of your timeframe is after midnight or not.

See updated SQL fiddle

Alternatively, you can also convert the strings on the fly for each query, but it unneccessarily costs performance. If you can, modify the column definition. With explicit type conversions, a query would look like this:

SELECT * from test 
WHERE 
  TIME(resetTime) >= TIME("23:50")
  OR TIME(resetTime) <= TIME("00:10")

See this working in in SQL fiddle too

Kaii
  • 20,122
  • 3
  • 38
  • 60
  • If you run this ( SELECT * from test WHERE resetTime >= "5:00" OR resetTime <= "5:30" ) there's a problem – TheCesco1988 Aug 11 '22 at 16:38
  • Plus 1 for using the `TIME` data type. – The Impaler Aug 11 '22 at 18:55
  • This solution doesn't work for me, because in the example above, you want to return the row where resetTime is 0:00, however 0:00 is not greater than 23:50, it is less. – Alex Aug 07 '23 at 21:12
  • @Alex just use a DATETIME then, store date and time in the same field, and you can use a similar logic as in this post. If you can't figure this out please post a seperate question. – Kaii Aug 08 '23 at 14:59
  • @Kaii - problem is that there is no date in my database, just the fact that the date cannot last longer than 24 hours. The way would be to work out the number of seconds from midnight before the start_time then if end_time is after the next midnight, to work it out from that. – Alex Aug 10 '23 at 23:40