SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2003
    Location
    UK
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to get end date onto previous row

    Hi,

    I'm trying to extract start and end dates as the delimiters of different periods. I would like them to be on the same row, but I can't figure out how to do it.

    The query I'm using is:

    Code:
    -- This select gets the start and end dates, but on different rows
        SELECT date AS start_date
             , date_sub(date, interval 1 second) AS end_date
          FROM log
         WHERE date(date) >= '2013-06-10 00:00:00'
           AND date(date)  < '2013-06-16 23:59:59'
           AND type in (1,2,3)
      GROUP BY date(date), type
         UNION
    -- This next one gets the end date for the last date in the period
        SELECT date_sub(date, interval 1 second) AS end_date
             , date
          from (
                 SELECT date
                   FROM log
                  WHERE date(date) > '2013-06-16 23:59:59'
                    AND type IN (1,2,3)
               GROUP BY date(date), type
                  LIMIT 1
               ) AS next_one
        ORDER BY start_date
    An example of the data I'm working with is:

    Code:
    date                type
    2013-06-06 13:05:44 1
    2013-06-13 15:34:45 2
    2013-06-13 15:31:08 1
    2013-06-20 13:10:32 1
    2013-06-27 14:05:16 1
    2013-07-04 13:58:14 1
    The result I'm currently getting is:

    Code:
    start_date          end_date 
    2013-06-13 15:31:08 2013-06-13 15:31:07
    2013-06-13 15:34:45 2013-06-13 15:34:44
    2013-06-20 13:10:31 2013-06-20 13:10:32
    So now the end date for row 1 is in row 2 and the one for row 2 is in row 3 etc.

    Does anyone know of a technique to get the end date in row 2 to appear in row 1 i.e. against the one it relates to?


    Debbie
    QuicknEasySalesPro.com
    - your quick and easy, yet powerful solution for managing your
    membership site sales, downloads and affiliates.

  2. #2
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    The way you do this is to do a join to the same table, joining on the earliest date after the date in the main table. And you get the earliest date by doing a subquery. It always feels like I'm doing one join too many when I do this, so I'm open to correction.

    Code:
    SELECT `log`.type, `log`.`date` AS start_date, next_log_entry.date AS endDate
    FROM `log` 
     LEFT JOIN `log` AS next_log_entry
    	ON next_log_entry.type = `log`.type AND next_log_entry.date = (
    		SELECT MIN(next_log_entry2.date) 
    		FROM 
    		`log` next_log_entry2
    		WHERE next_log_entry2.type=`log`.type 
    		AND next_log_entry2.date>log.date
    	)
    WHERE `log`.`date` >= '2013-06-01 00:00:00'
        AND `log`.`date`  < '2013-06-16 23:59:59'
        AND `log`.`type` IN (1,2,3)
    ORDER BY `log`.`type`, `log`.`date`
    Using this table data:
    Code:
    DATE                   TYPE  
    -------------------  --------
    2013-06-06 13:05:44         1
    2013-06-13 15:31:08         1
    2013-06-20 13:10:32         1
    2013-06-27 14:05:16         1
    2013-07-04 13:58:14         1
    2013-06-13 15:34:45         2
    And I get this result:
    Code:
      type  start_date           endDate              
    ------  -------------------  ---------------------
         1  2013-06-06 13:05:44  2013-06-13 15:31:08  
         1  2013-06-13 15:31:08  2013-06-20 13:10:32  
         2  2013-06-13 15:34:45  (NULL)
    I'd also be inclined to avoid using function calls in your WHERE statements (eg DATE(date)) because, as far as I know, MySql will have to apply that function to every row in the table, thus skipping indexes and slowing down the query.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    totally agree with you on the point about not using functions in the WHERE clause like DATE(date)

    however, i don't like your replacement --
    Code:
    WHERE `log`.`date` >= '2013-06-01 00:00:00'
      AND `log`.`date`  < '2013-06-16 23:59:59'
    leaving aside the obvious cheap shot about not using those horrid backticks, i prefer my version --
    Code:
    WHERE log.date >= '2013-06-01'
      AND log.date  < '2013-06-17'
    there, isn't that nicer?

    also, note that yours would fail if it is run at exactly 23:59:59

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Sep 2003
    Location
    UK
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you hessodreamy, that did the trick.

    Yes, I do agree about the use of the date function. That was an oversight, as I have to use it on another very similar table that has its dates stored in unix timestamp format in an integer column. I've never understood why people do that when there are perfectly good date data types available. Oh well, never mind.

    r937 - That's certainly another way to do it. Or one could use BETWEEN, which is what I did.

    Debbie
    QuicknEasySalesPro.com
    - your quick and easy, yet powerful solution for managing your
    membership site sales, downloads and affiliates.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Debbie-Leigh View Post
    Or one could use BETWEEN, which is what I did.
    no, don't do that

    BETWEEN will do an equals match at both end points of the range, and that would be wrong at the top end

    whereas what i posted will work correctly always, regardless of the precision of the column in question, whether there are seconds, milliseconds, or whatever

    the benefit of my technique (not specifying BETWEEN) becomes obvious when you are dealing with the last day in february

    convinced?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Debbie-Leigh View Post
    I've never understood why people [use unix timestamps] when there are perfectly good date data types available.
    storing data from a legacy unix application is one good reason

    making things easier or more familiar for php programmers would be a bad one

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Debbie-Leigh View Post
    Yes, I do agree about the use of the date function. That was an oversight, as I have to use it on another very similar table that has its dates stored in unix timestamp format in an integer column.
    If I understand you, in those cases apply your function call to the constant in the condition. So instead of saying
    Code:
    where from_unixtime(timestamp) > "2013-12-01"
    You'd use
    Code:
    where timestamp > unix_timestamp("2013-12-01")
    This allows MySQL to apply the function once, and use the index of the table (I'm assuming this field is indexed) for faster lookup.

  8. #8
    SitePoint Zealot
    Join Date
    Sep 2003
    Location
    UK
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All good points. Thanks.

    Re. the dates, 3 points:

    1. Unfortunately I have to work with the dates that are fed from the application, which means I need to include the upper one in the range, so the between will do that, although it may not be the most future proof method.

    2. The application is only a few years old, so no legacy data.

    3. Date data types give more scope for easy date manipulation than a straight integer does.

    But that doesn't detract from your points, though, as they all quite valid.

    Debbie
    QuicknEasySalesPro.com
    - your quick and easy, yet powerful solution for managing your
    membership site sales, downloads and affiliates.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Debbie-Leigh View Post
    Unfortunately I have to work with the dates that are fed from the application, which means I need to include the upper one in the range
    i still don't understand

    if you are tasked with returning data for the dates of january 27 through january 29, and given that your date column is a datetime and not just a simple date, would you please show me your BETWEEN code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Sep 2003
    Location
    UK
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    WHERE date BETWEEN '2013-01-01 00:00:00'
                   AND '2014-01-31 23:59:59'
    QuicknEasySalesPro.com
    - your quick and easy, yet powerful solution for managing your
    membership site sales, downloads and affiliates.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, at least you captured most of the datetimes on the 31st (you're missing any that took place between 23:59:59.000 and 00:00:00.000, which is admittedly a small window of error, but don't forget our dear cousin murphy)

    when you say the dates are fed from the application, are you saying that the application will already pre-calculate things like properly handling the last day of february?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Sep 2003
    Location
    UK
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, it gives the start and end dates for the period being requested.
    QuicknEasySalesPro.com
    - your quick and easy, yet powerful solution for managing your
    membership site sales, downloads and affiliates.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •