#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    3
    Rep Power
    0

    How to get all information from a table to a new table--help needed on MySql


    Hi

    I have a table named l1 which consist of several columns. One of the columns is Date_Time where it has the date and time in every 2 min interval.
    For example: Date_Time
    2013-06-26 00:00:30
    2013-06-26 00:02:30
    ....
    2013-06-26 01:46:30
    2013-06-26 01:48:30
    ....
    ....
    2013-09-30 23:58:30

    So, I wanted to create a new table called newl1 with all the columns but the date and time is to be in an hour interval.
    Date_Time
    2013-06-26 00:00:30
    2013-06-26 01:00:30
    2013-06-26 02:00:30
    .....
    2013-09-30 23:00:30
    Thus, there will be a total of 24 values per day.

    Currently, I have this code.

    CREATE TABLE load.newl1
    SELECT * FROM load.l1 GROUP BY HOUR(Date_Time);

    However, I can only extract 26 and 27 June 2013 hourly data. How can I extract all the hourly data?

    Thank you.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    does the resulting table have to have that initial 30 second time? if so, why? what's this all for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,481
    Rep Power
    1752
    I suspect you'll want to group by more than just the hour of the date_time column, you'll need to 'roll in' the actual date (yymmdd) part too.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by SimonJM
    I suspect you'll want to group by more than just the hour of the date_time column, you'll need to 'roll in' the actual date (yymmdd) part too.
    Yes. I need to include the date too with the hourly time. Thank you.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    does the resulting table have to have that initial 30 second time? if so, why? what's this all for?
    Yes, that is because the database is getting data from a monitoring device so that's why seconds were included. It is used to monitor the load demand every 2 min. However, I just need the hourly data for analysis purpose as the 2 min interval data is just too much. Thank you.

IMN logo majestic logo threadwatch logo seochat tools logo