CODEWITHSUNDEEP
sqlmysql
n0cturnal
n0cturnal

Reputation: 1725

MySQL Join with timeline

i have a table with 2 colums, id and date.

supposing i have some holes (IE i do not have stored some days), which is the best way to retrive one row for each day of a certain period ?

I thought to do a LEFT JOIN with a generated date table, but i was wondering it is a better way to do this.

If not, which is a good way to generate a time table ?

Thank you!

Upvotes: 1

Views: 258

Answers (2)

davek
davek

Reputation: 22925

A generated data table is not a bad idea: the data is fixed, and you can take advantage of indexes, which you couldn't do if you persisted the data in memory (well, you could wit temporary tables, which is still using a generated data table).

Here's an example:

CREATE TABLE myDateTable (
      id INT(11) not null auto_increment,
      fullDate DATE NOT NULL,
      dYear SMALLINT(11) NOT NULL,
      dQuarter TINYINT(11) NOT NULL,
      dMonth TINYINT(11) NOT NULL,
      dDay TINYINT(11) NOT NULL
      PRIMARY KEY  (id),
      KEY idx_fullDate (fullDate)
) ENGINE=INNODB
;

DELIMITER //

CREATE PROCEDURE fillMyDateTable()
BEGIN

SET @startDate = '2011-01-01';
SET @endDate = '2012-12-31';

REPEAT 

     INSERT INTO myDateTable (fullDate, dYear, dQuarter, dMonth, dDay)

     SELECT thisDate
           , YEAR(thisDate)
           , QUARTER(thisDate)
           , MONTH(thisDate)              
           , DAYOFMONTH(thisDate)
     FROM (SELECT @startDate AS thisDate) f 
           WHERE NOT EXISTS 
           (SELECT 1 FROM myDateTable d WHERE d.fullDate = f.thisDate);

     SET @startDate = DATE_ADD(@startDate, INTERVAL 1 DAY);
     UNTIL (@startDate > @endDate AND @startDate > CURRENT_DATE()) 
END REPEAT;

END

//

Upvotes: 2

Spredzy
Spredzy

Reputation: 5174

Time table depends on your needs.

Do you need Quarter precision ? Fiscal year precision ?

If not just do a simple

CREATE TABLE time_dimension AS (SELECT
CURDATE()
DAY()
DAYNAME()
MONTH()
MONTHNAME()
YEAR());

Then add the precision you might need

And YES it sounds like the best approach.

This is an example provided for PostgreSQL. It is a really precise time table.

Upvotes: 1

Related Questions