CODEWITHSUNDEEP
mysqlsql
Daniel Mason
Daniel Mason

Reputation: 280

SQL SELECT logic

I've been having a bit of trouble thinking this problem through. I can't seem to define a SELECT query which is accurate enough to give me the result I want.

I am storing shift patterns in a table. These shift patterns don't have any restrictions on when they can start and finish (except that they cannot overlap each other per machine)

This is the structure of the table (5 rows of example data) Table

The only information I have to select with is:

The issue is when a shift overlaps 00:00. So my question is this:

How would I select the current shift based on the current time and weekday?

Here is an SQL Fiddle of the scenario

Thanks!

Upvotes: 2

Views: 178

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You can do this with simple logic. If StartTime < EndTime, then you want to test for times between the two values. If Startime > EndTime, then you want to test for times not between the two values. So this solves the time problem:

SELECT *
FROM webreportshiftsetup
WHERE (StartTime < EndTime and time(now()) between StartTime and EndTime or
       StartTime > EndTime and time(now()) not between StartTime and EndTime
      ) and
      dayname(now()) in (StartWeekDay, EndWeekDay)

You have a similar problem with the weekdays. But your question is specifically about times and not weekdays. (That should perhaps be another question.)

Upvotes: 2

Douglas Barbin
Douglas Barbin

Reputation: 3615

I would suggest that you change the schema. One option is to use the datetime type for start/end, instead of using varchar for everything.

Then you can query it like:

SELECT *
FROM webreportshiftsetup
WHERE NOW() BETWEEN StartDateTime AND EndDateTime

and so forth.

If you need this to be a repeating thing to where specific dates won't work, then you might make the StartDay/EndDay columns tinyint and give them a value of 1-7, with the smallest number being the first day of the week and the largest number representing the last day of the week. StartTime/EndTime would be date type. Querying that would look like:

SELECT *
FROM webreportshiftsetup
WHERE StartDay >=2 AND EndDay <=4 AND StartTime >= '2:00' AND EndTime <= '13:00'

Upvotes: 0

dkasipovic
dkasipovic

Reputation: 6120

If your shifts are one-day (i.e. you need to select only current day) you can do something like

SELECT * FROM shifts
WHERE startWeekDay = DATE_FORMAT(CURDATE(),'%W') AND NOW() BETWEEN startTime AND endTime

Otherwise, if your shift starts on Monday and finishes on Wednesday, and today is Tuesday, you will have trouble finding todays shift with a query. For that you should store days as number: 1- Monday, 2- Friday, ...

Upvotes: 1

Related Questions