CODEWITHSUNDEEP
mysqldatedatetime
Joe
Joe

Reputation: 279

mysql select on datetime objects within range

I have a table 'appointments' that contains, among other things, two datetime fields labeled 'start' and 'end'. I also have date in local time that is converted as a range of a full day into UTC (which is what the SQL table stores the datetimes as). I need to select all the (business) times between 00:00:00 and 08:00:00 UTC that also fall in the range of my local time conversion.

An example, A user in PST (pacific standard time) picks December 1st, 2018. The dates between December 1st at 00:00:00 and December 2nd 00:00:00 are converted to UTC which would be December 1st 08:00:00 to December 2nd 08:00:00. I need to select all appointments between 00:00:00 and 8:00:00 any given day in the previous range (dec 1 - dec 2).

All of my datetimes/queries are in the form 'yyyy-MM-dd HH:mm:ss'.

I know that I can select all of the times between two times rather simply like so:

SELECT start, end 
FROM appointment 
WHERE start>='2018-12-01 00:00:00' 
AND end<='2018-12-02 08:00:00'

But I'm unsure as to how to trim these down to only between business hours.

I'm looking for something like 

SELECT start, end 
FROM appointment 
WHERE (start>='2018-12-01 00:00:00' 
AND end<='2018-12-02 08:00:00') 
AND (start.substring(11, start.end) >= '00:00:00' 
AND end.substring(11, end.end) <= '08:00:00')

Where a call like start.substring(11, start.end) would return the time in 'HH:mm:ss' format

Upvotes: 0

Views: 466

Answers (1)

Rahul Patel
Rahul Patel

Reputation: 639

Try using the TIME function in MySQL.

SELECT start, end FROM appointment WHERE TIME(start) >= '00:00:00' AND TIME(end) <= '08:00:00' AND ... //other conditions

Upvotes: 1

Related Questions