SQL: How to group by day, but from 8AM-2AM the next day... after midnight?

Discussion in 'OT Technology' started by Peyomp, Jan 29, 2007.

  1. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    The thread title says it all. Currently in my query I group by day. But a business day in this case is from 8AM until 2AM the next day.

    How the fuck do I do that?
     
  2. Supergeek

    Supergeek New Member

    Joined:
    Jan 23, 2007
    Messages:
    1,855
    Likes Received:
    0
    Location:
    Colorado
    Convert the timestamps to Unix time?

    SELECT FROM Table WHERE UTimestamp > (07:59:59 that date in Unix time) AND UTimestamp < (02:01:00 the next day in Unix time)

    Something like that should work. Unix timestamp conversion is built into PHP, I'm not sure if it's built into SQL. (I use MySQL.)
     
  3. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    No, that will not work.

    And that is not the question I asked. I am doing a GROUP BY.

    There is a further constraint in the query in the WHERE part that defines the scope within which we GROUP.

    I know what i have to do. I have to write a stored procedure, and group by it. The stored procedure will return yesterday, or today, depending on the time. If its 1AM or 2AM, then yesterday. Otherwise today.
     
  4. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    The stored procedure looks like this:
    DELIMITER //

    # This procedure handles grouping by the Foo Bars business day: 8AM-2AM, so if a date is 2AM, we return the previous day
    CREATE FUNCTION GR_BUSINESS_DAY
    (
    myDate DATETIME
    )

    RETURNS DATETIME DETERMINISTIC

    BEGIN

    IF HOUR(myDate) <= 2
    THEN
    RETURN DATE_SUB(myDate, INTERVAL 1 DAY);
    ELSE
    RETURN myDate;
    END IF;

    END//

    I group by that function and it works...

    But now since I also select the actual day part of the datetime, I am getting duplicate days in some cases.

    Fuck.
     

Share This Page