mysql – time dimension

time-dimension


CREATE TABLE IF NOT EXISTS time_dimension (
  `date` date DEFAULT NULL,        
  `id` int NOT NULL,            
  `y` smallint DEFAULT NULL,    
  `m` smallint DEFAULT NULL,    
  `d` smallint DEFAULT NULL,    
  `yw` smallint DEFAULT NULL,    
  `w` smallint DEFAULT NULL,    
  `q` smallint DEFAULT NULL,    
  `wd` smallint DEFAULT NULL,    
  `m_name`  char(10) DEFAULT NULL,    
  `wd_name` char(10) DEFAULT NULL,    
  PRIMARY KEY (`id`)
);

DROP PROCEDURE IF EXISTS time_dimension_build;

DELIMITER $$
CREATE PROCEDURE time_dimension_build (p_start_date DATE,p_end_date DATE)
BEGIN
    DECLARE v_full_date DATE;
    SET v_full_date = p_start_date;
    WHILE v_full_date < p_end_date DO
        INSERT INTO time_dimension (
          date ,
          id ,    
          y ,    
          m ,    
          d ,    
          yw ,    
          w ,    
          q ,    
          wd ,    
          m_name  ,
          wd_name    
        ) VALUES (
            v_full_date,        
            date_format(v_full_date, "%Y%m%d"),
            year(v_full_date),    
            month(v_full_date),    
            day(v_full_date),    
            date_format(v_full_date, "%x"),
            week(v_full_date, 3),    
            quarter(v_full_date),    
            weekday(v_full_date)+1,    
            monthname(v_full_date),    
            dayname(v_full_date)    
        );
        SET v_full_date = DATE_ADD(v_full_date, INTERVAL 1 DAY);
    END WHILE;
END $$

use with


call time_dimension_build('2014-01-01','2014-12-31');

 

source

http://tech.akom.net/archives/36-Creating-A-Basic-Date-Dimension-Table-in-MySQL.html

http://www.joyofdata.de/blog/setting-up-a-time-dimension-table-in-mysql/

Published by

G3n1k

just to remember what i had known :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s