0

Please help. I am kinda new to this database world. I am using PostgreSQL.

I have a table with defined shifts like this:

CREATE TEMPORARY TABLE shifts AS
SELECT id, start_hour::time, end_hour::time
FROM ( VALUES 
  ( 1,'06:00:01','14:00:00' ),
  ( 2,'14:00:01','22:00:00' ),
  ( 3,'22:00:01','06:00:00' )
) AS t(id,start_hour,end_hour);

I have been using the between operator to compare a given hour with the start_hour and end_hour so I can get the corresponding shift, like this

SELECT * from Shifts S where given_hour BETWEEN start_hour AND end_hour;

It works when it is shift 1 or 2 but not with shift 3. It returns no value. Can you please give me a little advice on how should i do this? Thank you!

  • so the ids above correspond to the employees that are working during those times, and you're trying to find what employees worked during a specific point in time? – Evan Carroll Jan 18 '17 at 18:55
  • You really should be storing date for this. It would make your life a thousand times easier. Does your original dataset have a date column? – Evan Carroll Jan 18 '17 at 19:27
  • @EvanCarroll yes, my table has an ID column, a start_hour column and a end_hour column both of type time. What i am trying to get is the shift for a given hour. In another table i got some records with the hour they were inserted, so i pick that value(hour) and use it in the select statement so i can get the corresponding shift. – Andy Jan 18 '17 at 19:39
  • But, you really need a timestamp and not a time. – Evan Carroll Jan 18 '17 at 19:50
1

You cannot have an interval defined by an "end" smaller than your "start". Your shift 3 does not work because you're crossing day boundaries, and your "end" (6 o'clock, is smaller than your end 22 o'clock).

If you want to define your shifts by hours, you would need to have, in general, 2 intervals per shift, and shift 3 should have:

+----+--------------+------------+--------------+------------+
| id | start_hour_1 | end_hour_1 | start_hour_2 | end_hour_2 |
+----+--------------+------------+--------------+------------+
|  1 |     06:00:00 |   14:00:00 |              |            |
+----+--------------+------------+--------------+------------+
|  2 |     14:00:00 |   22:00:00 |              |            |
+----+--------------+------------+--------------+------------+
|  3 |     22:00:00 |   24:00:00 |    00:00:00  |   06:00:00 |
+----+--------------+------------+--------------+------------+

And you should rewrite your query with:

SELECT 
    * 
FROM 
    Shifts S 
WHERE
       (given_hour >= start_hour_1 AND given_hour < end_hour_1)
    OR (given_hour >= start_hour_2 AND given_hour < end_hour_2) ;

As a little advice, I would use BETWEEN .. AND .. only with discrete variables (basically integers and texts). With continuous variables (and time is continuous), it's safer to include one of the ends and exclude the other (>= AND <): given_hour >= start_time AND given_hour < end_time. That's why I changed :01 by :00 to work this way (and avoid problems with 14:00:00.123, which could eventually happen).

  • This helped me out to solve my problem! Thank you, never thought about using additional columns =). I'll take your advice too. – Andy Jan 18 '17 at 19:45
  • 2
    Happy to help. If you think this is a valid answer to your question, mark it as such ("check it"), so that others can concentrate on the "not yet answered". – joanolo Jan 18 '17 at 19:50
  • 1
    @Andy if you're happy with this answer, please mark it as chosen. – Evan Carroll Jan 27 '17 at 18:51
0

You have a few options. In essence, this is a chicken-and-egg problem when it comes to solving it.

Let's say you've been in business one day and you started with this schema.. Whether or not 3:00:00 has been worked depends on what shift started.

  1. If you started the day with the morning shift that day, then no one worked 3 AM.
  2. If you started the day at midnight by calling in the graveyard shift to work 3 hours that day then 3 AM was worked.

That data is forever lost and the ambiguity can't be solved without going back to the first shift and adding a date.

At that point, time becomes timestamp (which carries a date element). Then it's easier like in your original query to know who worked what times because the date is attached to it. So as a general rule, never use time unless it's detached from real world dates or represents an abstract repeating event. You've violated that rule and now you're going to have loads of fun.

So what we can do is create a pseudo date-element to solve this problem. I'll solve it by assuming the first scenario.

SELECT 'yesterday'::date + start_hour AS start, 'yesterday'::date + start_hour + work_time AS end
FROM (
  SELECT start_hour,
    end_hour,
    CASE WHEN (end_hour<start_hour)
      THEN ('24:0:0' - start_hour) + (end_hour - 'ALLBALLS'::time)
      ELSE end_hour - start_hour
    END AS work_time
  FROM shifts
) AS t;

Now, we can query for 3 AM by doing..

-- change this to get the query result you want.
WHERE ('TODAY'::date + '3:00:00'::time)
  BETWEEN ('YESTERDAY'::date + start_hour)
    AND ('YESTERDAY'::date + start_hour + work_time);

Or you can query for 8 PM by doing..

-- change this to get the query result you want.
WHERE ('YESTERDAY'::date + '8:00:00'::time)
  BETWEEN ('YESTERDAY'::date + start_hour)
    AND ('YESTERDAY'::date + start_hour + work_time);

But ultimately you probably want to fix your schema to use time stamp

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.