Query with DateTime, "empty" time-values

arathorn2005 · #1 (**permalink**) 03-06-09, 04:05

Good morning,

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

Simple query:

Code:

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):

Code:

2009-03-06 00:30,  5
2009-03-06 00:31,  4
2009-03-06 00:33,  8

...but I prefer...

Code:

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!

Björn

healdem · #2 (**permalink**) 03-06-09, 04:47

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

r937 · #3 (**permalink**) 03-06-09, 05:20

use an integers table --

Code:

CREATE TABLE integers 
(i INTEGER NOT NULL PRIMARY KEY);
INSERT INTO integers (i) VALUES 
 (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

now you can cross join this table with itself to produce the numbers from 0 through 59 --

Code:

SELECT 10*tens.i + units.i AS s 
  FROM integers AS tens
CROSS
  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

Internet Services

Web Development

Digital Marketing

Consumer Tech