Date Dimension SQL Scripts – MySQL

Post Info: 6,233 views 16 Comments Post a comment

Date Dimension is one of important Time series dimension tables.

Here are the sample scripts in MySQL to create that.

  • There are two temporary tables created for key/id sequencing used in this.
  • Small-numbers table

DROP TABLE IF EXISTS numbers_small;
CREATE TABLE numbers_small (number INT);
INSERT INTO numbers_small VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

  • Main-numbers table

DROP TABLE IF EXISTS numbers;
CREATE TABLE numbers (number BIGINT);
INSERT INTO numbers
SELECT thousands.number * 1000 + hundreds.number * 100 + tens.number * 10 + ones.number
FROM numbers_small thousands, numbers_small hundreds, numbers_small tens, numbers_small ones
LIMIT 1000000;

  • Create Date Dimension table

DROP TABLE IF EXISTS Dates_D;
CREATE TABLE Dates_D (
date_id          BIGINT PRIMARY KEY,
date             DATE NOT NULL,
day              CHAR(10),
day_of_week      INT,
day_of_month     INT,
day_of_year      INT,
previous_day     date NOT NULL default '0000-00-00',
next_day         date NOT NULL default '0000-00-00',
weekend          CHAR(10) NOT NULL DEFAULT "Weekday",
week_of_year     CHAR(2),
month            CHAR(10),
month_of_year    CHAR(2),
quarter_of_year INT,
year             INT,
UNIQUE KEY `date` (`date`));

  • First populate with ids and Date

INSERT INTO Dates_D (date_id, date)
SELECT number, DATE_ADD( '2010-01-01', INTERVAL number DAY )
FROM numbers
WHERE DATE_ADD( '2010-01-01', INTERVAL number DAY ) BETWEEN '2010-01-01' AND '2010-12-31'
ORDER BY number;

Change year start and end to match your needs. The above sql creates records for year 2010.

  • Update other columns based on the date.

UPDATE Dates_D SET
day             = DATE_FORMAT( date, "%W" ),
day_of_week     = DAYOFWEEK(date),
day_of_month    = DATE_FORMAT( date, "%d" ),
day_of_year     = DATE_FORMAT( date, "%j" ),
previous_day    = DATE_ADD(date, INTERVAL -1 DAY),
next_day        = DATE_ADD(date, INTERVAL 1 DAY),
weekend         = IF( DATE_FORMAT( date, "%W" ) IN ('Saturday','Sunday'), 'Weekend', 'Weekday'),
week_of_year    = DATE_FORMAT( date, "%V" ),
month           = DATE_FORMAT( date, "%M"),
month_of_year   = DATE_FORMAT( date, "%m"),
quarter_of_year = QUARTER(date),
year            = DATE_FORMAT( date, "%Y" );

If more date columns are required refer to MySQL documentation for Date Formats. <MySQL Date/Time Fuctions>

Return to top

16 Comments

  • UseDB123 says on November 17, 2010 at 9:00 am | Permalink

    Thanks for the script.

    • Drew says on November 8, 2013 at 10:35 am | Permalink

      Hi, I was hoping to create some 65,535 rows, to go a bit farther into the future; this script creates 10,000 rows. How can I modify it to create more rows? Thank You :)

      • admin says on November 8, 2013 at 4:01 pm | Permalink

        Change the SQL to create 100,000 and you have more numbers to create.

        Here is the SQL:

        DROP TABLE numbers;

        CREATE TABLE numbers (number BIGINT);

        INSERT INTO numbers
        SELECT ten_thousands.number * 10000 + thousands.number * 1000 + hundreds.number * 100 + tens.number * 10 + ones.number
        FROM numbers_small ten_thousands, numbers_small thousands, numbers_small hundreds, numbers_small tens, numbers_small ones
        LIMIT 1000000;

        • Drew says on November 8, 2013 at 4:03 pm | Permalink

          Ok cool, thank You again :)

  • Drew says on October 24, 2013 at 9:20 am | Permalink

    Hi, thank you for sharing this. Can I delete numbers and numbers_small after creating the Dates_D table?

    • admin says on October 24, 2013 at 9:28 am | Permalink

      Yes, you can. They are just for generating the dates.

      • Drew says on October 24, 2013 at 9:29 am | Permalink

        Ok, thanks :)

        • Popacha says on May 5, 2014 at 6:31 am | Permalink

          Hi,
          I’m trying to insert values into three my columns- day, month, year. I have ID, and timestamp(like date in this example).
          day CHAR(10)
          month CHAR(10)
          year int

          When I try to insert values into columns with this:
          UPDATE TIME SET
          DAY = DATE_FORMAT( TIME_STAMP, “%W” ),
          MONTH = DATE_FORMAT(TIME_STAMP, “%M”),
          YEAR = DATE_FORMAT(TIME_STAMP, “%Y” );

          it says: you are using safe update mode and you tried to update table without a WHERE that uses a KEY column.

          What am I doing wrong?

  • Popacha says on May 5, 2014 at 7:31 am | Permalink

    I forgot to mention that I use MySql Workbench 6.1

    • admin says on May 6, 2014 at 12:48 am | Permalink

      Looks like you are trying to update the full table without where clause.

      You can try adding this before your sql.

      SET SQL_SAFE_UPDATES=0;
      < Your SQL goes here >

      • Popacha says on May 6, 2014 at 5:26 am | Permalink

        Works, thanks :)

Pings and Trackbacks

Leave a Reply

 

Are you finished?

Return to top