Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009

    Unanswered: Query with DateTime, "empty" time-values

    Good morning,

    while building a query from a table (t1) I need a sorted, formatted grouping
    (dt = datetime, formatted in minutes).

    Simple query:
    SELECT DATE_FORMAT(t1.dt,"%Y-%m-%d %H:%i") AS 'thetime', COUNT(*) AS "howmany"
    FROM t1
    GROUP BY thetime
    ORDER BY thetime ASC;
    So far so good.

    Result of that query (example):
    2009-03-06 00:30,  5
    2009-03-06 00:31,  4
    2009-03-06 00:33,  8
    ...but I prefer...
    2009-03-06 00:30,  5
    2009-03-06 00:31,  4
    2009-03-06 00:32,  0 <- !
    2009-03-06 00:33,  8
    2009-03-06 00:34,  0 <- !
    Why? The data will be processed into an image with a timeline. If the time isn't progressing continuously, the image gets incoherent if I have timespans (minutes) without any records.

    How can I account those ticks without entries in the table t1?

    RDBMS: MySQL (5.x).

    Two possible solutions from myself:
    1. using a 2nd table with "all" ticks and bound via JOIN to t1,
    2. processing the resultset with another programm to add the left-out ticks.

    Both would be a bit cumbersomely and I guess there are easier solutions which I just haven't found yet.

    Thanks in advance for any hint into the right direction!


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if you need to have a row for every minute then I think you are going to have to do a join to a table with minutes int (ie 0..59) and join on the minute element
    or handle it in the front end application
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    use an integers table --
    CREATE TABLE integers 
    INSERT INTO integers (i) VALUES 
    now you can cross join this table with itself to produce the numbers from 0 through 59 --
    SELECT 10*tens.i + units.i AS s 
      FROM integers AS tens
      JOIN integers AS units
     WHERE 10*tens.i + units.i BETWEEN 0 AND 59
    you can carry in in this direction, generating another series of integers for the range of minutes you want, and again another series of integers for the range of hours you want, all depending one how long a range for the datetimes you're interested in

    this method uses only one set of numbers from 0 through 9 to generate all the data points you need for the left join

    actually quite simple once you've seen how to approach it

    on the other hand, your second solution, to supply the missing values in your application language, that's pretty simple too | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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