Professional Web Applications Themes

Create Table of Sequential Hours - MySQL

Hi all, I was wondering if anyone knew of a way to fill a table with rows of sequential hours spanning a number of years using a query or set of queries. What I am looking for is a way to mimic this VBA code I use in Access to create the table currently. I'll post the VBA at the bottom of the post. What I am looking for is the following: CREATE TABLE tbluhahours ( StartDate datetime NOT NULL, EndDate datetime NOT NULL, PRIMARY KEY (`StartDate`,`EndDate`) ) ENGINE=MyISAM DEFAULT CHT=latin1; INSERT INTO tblUHAHours (StartDate, EndDate) VALUES ('2000-01-01 00:00:00' , ...

  1. #1

    Default Create Table of Sequential Hours

    Hi all,

    I was wondering if anyone knew of a way to fill a table with rows of
    sequential hours spanning a number of years using a query or set of
    queries.

    What I am looking for is a way to mimic this VBA code I use in Access
    to create the table currently. I'll post the VBA at the bottom of the
    post. What I am looking for is the following:

    CREATE TABLE tbluhahours (
    StartDate datetime NOT NULL,
    EndDate datetime NOT NULL,
    PRIMARY KEY (`StartDate`,`EndDate`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1;

    INSERT INTO tblUHAHours (StartDate, EndDate)
    VALUES ('2000-01-01 00:00:00' , '2000-01-01 00:59:59');
    INSERT INTO tblUHAHours (StartDate, EndDate)
    VALUES ('2000-01-01 01:00:00' , '2000-01-01 01:59:59');

    But would l need a row for every hour of every day for say, from 2000
    to 2010. Is it possible to script this somehow?

    This is the VBA I used:

    Private Sub makeTable_Click()
    Dim theDate As Date
    Dim theTempDate As Date
    Dim endDate As Date
    Dim rsInsert As Recordset
    Dim strSQL As String
    Dim db As Database

    Set db = CurrentDb

    theDate = "2000-12-01 00:00:00"
    theTempDate = "2000-12-01 00:59:59"

    endDate = "2007-01-01 00:00:00"

    While theDate < endDate
    strSQL = "INSERT INTO tblUHAHours (StartDate, EndDate) VALUES
    (#" & _
    Format(theDate, "yyyy-mm-dd hh:mm:ss") & "#, #" & _
    Format(theTempDate, "yyyy-mm-dd hh:mm:ss") & "#)"
    If DatePart("yyyy", theDate) < DatePart("yyyy", endDate) Then
    db.Execute (strSQL)
    End If
    theDate = DateAdd("s", 3600, theDate)
    theTempDate = DateAdd("s", 3600, theTempDate)
    ' MsgBox strSQL & " | " & theDate & " | " & theTempDate
    Wend
    End Sub

    Thanks so much for any ideas, or advice you can share!

    - Jake

    stevensjn@gmail.com Guest

  2. #2

    Default Re: Create Table of Sequential Hours

    On 2 Feb, 16:04, com wrote: 

    What's wrong with the script you've got?

    Captain Guest

  3. #3

    Default Re: Create Table of Sequential Hours

    Just for the record, I use the hours table for joining against to
    create a report of used hours of activity in a log of calls. So, if
    you have 20 scheduled hours during a particular hour (say 20 staff
    from 12-1), how much of that time is actually recorded as an active
    call. Then I use that data and group it by hours etc for statistical
    ysis.

    Its really quite complicated. If anyone is interested in the whole
    scenario I would love to share because I think there is a lot of room
    for improvement in my current solution, but it might be a time sink
    (or simple to some!).

    The volume of call data makes some of my queries run overnight. :/

    Here is the gist of it.
    CREATE TABLE tblUHADurations (
    SELECT
    tblUHAData.CallId,
    tblUHAData.CallStart,
    tblUHAData.CallEnd,
    tblUHAHours.StartDate,
    DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
    UNIX_TIMESTAMP(tblUHAHours.EndDate)+1 -
    UNIX_TIMESTAMP(tblUHAData.CallStart) AS Duration
    FROM
    tblUHAData INNER JOIN tblUHAHours ON
    tblUHAHours.StartDate <= tblUHAData.CallStart AND
    tblUHAData.CallStart <= tblUHAHours.EndDate AND
    tblUHAHours.EndDate < tblUHAData.CallEnd
    );


    INSERT INTO tblUHADurations (
    SELECT
    tblUHAData.CallId,
    tblUHAData.CallStart,
    tblUHAData.CallEnd,
    tblUHAHours.StartDate,
    DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
    3600 AS Duration
    FROM
    tblUHAData INNER JOIN tblUHAHours ON
    tblUHAHours.StartDate > tblUHAData.CallStart AND
    tblUHAHours.EndDate < tblUHAData.CallEnd
    );


    INSERT INTO tblUHADurations (
    SELECT
    tblUHAData.CallId,
    tblUHAData.CallStart,
    tblUHAData.CallEnd,
    tblUHAHours.StartDate,
    DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
    UNIX_TIMESTAMP(tblUHAData.CallEnd)+1 -
    UNIX_TIMESTAMP(tblUHAHours.StartDate) AS Duration
    FROM
    tblUHAData INNER JOIN tblUHAHours ON
    tblUHAHours.StartDate <= tblUHAData.CallEnd AND
    tblUHAData.CallEnd <= tblUHAHours.EndDate AND
    tblUHAHours.StartDate > tblUHAData.CallStart
    );


    INSERT INTO tblUHADurations (
    SELECT
    tblUHAData.CallId,
    tblUHAData.CallStart,
    tblUHAData.CallEnd,
    tblUHAHours.StartDate,
    DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
    UNIX_TIMESTAMP(tblUHAData.CallEnd)+1 -
    UNIX_TIMESTAMP(tblUHAData.CallStart) AS Duration
    FROM
    tblUHAData INNER JOIN tblUHAHours ON
    tblUHAHours.StartDate <= tblUHAData.CallStart AND
    tblUHAData.CallEnd <= tblUHAHours.EndDate
    )

    stevensjn@gmail.com Guest

  4. #4

    Default Re: Create Table of Sequential Hours

    On Feb 2, 11:18 am, "Captain Paralytic" <com>
    wrote: 













    >
    > What's wrong with the script you've got?[/ref]

    Well I need Access to run it and then I have to export the table to
    MySQL. I would prefer to do everything in MySQL with a script or query
    if possible.

    Other parts of the solution I have to a larger problem includes
    queries that never complete in Access. I have another reply post that
    outlines the bigger problem below.

    stevensjn@gmail.com Guest

  5. #5

    Default Re: Create Table of Sequential Hours

    On 2 Feb, 16:21, com wrote: 

    I have always used GROUP BY for stuff like that rather than joins to a
    table containing all the hours.

    Captain Guest

  6. #6

    Default Re: Create Table of Sequential Hours

    com wrote in news:1170432275.685818.65760
    p10g2000cwp.googlegroups.com:
     

    USE test;

    DROP TABLE IF EXISTS tbluhahours;
    CREATE TABLE tbluhahours (
    StartDate datetime NOT NULL,
    EndDate datetime NOT NULL,
    PRIMARY KEY (`StartDate`,`EndDate`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1;

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

    SET counter := -1;
    SET start := '2000-01-01 00:00:00';
    SET finish := '2010-12-31 23:59:00';
    INSERT INTO tbluhahours (StartDate, EndDate)
    SELECT
    DATE(start) + INTERVAL H.c HOUR,
    DATE(start) + INTERVAL H.c + 1 HOUR - INTERVAL 1 SECOND

    FROM (
    SELECT counter := counter + 1 AS c
    FROM _helper h1 -- 10
    CROSS JOIN _helper h2 -- 100
    CROSS JOIN _helper h3 -- 1000
    CROSS JOIN _helper h4 -- 10000
    CROSS JOIN _helper h5 -- 100000
    ) H

    WHERE
    start + INTERVAL H.c HOUR < finish;



    --
    felix
    Felix Guest

  7. #7

    Default Re: Create Table of Sequential Hours

    Thank you felix, that is a fantastic script.

    I'll go look up how this works.

    Thank you very much.

    stevensjn@gmail.com Guest

  8. #8

    Default Re: Create Table of Sequential Hours

    com wrote in news:1170440839.065111.183270
    a75g2000cwd.googlegroups.com:

     

    Unfortunately, there is a (small) bug. You should leave out the DATE()
    calls (leftovers from testing).
    And the finish datetime should be '2010-12-31 23:59:59'.

    --
    felix
    Felix Guest

Similar Threads

  1. Create Table
    By KevinBarbee in forum Coldfusion Database Access
    Replies: 4
    Last Post: December 15th, 04:24 AM
  2. Create table overwrites existing table in mssql
    By bmyers in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 25th, 10:00 AM
  3. Create Sequential Numbers
    By Steven Rudolph in forum FileMaker
    Replies: 1
    Last Post: September 25th, 10:45 AM
  4. How to create a table with dynamic table name
    By cathy wang in forum Informix
    Replies: 3
    Last Post: July 17th, 12:46 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139