Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2013
    Posts
    2

    Unanswered: 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 exact 26 and 27 June 2013 hourly data. How can I extract all the hourly data?

    Thank you.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need to use the correct date time function when you do your join

    you need to define your hours table with the requisite time ONLY (ie exclude the date bit)
    and join to your data using the function time(mydatecolumn)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Clarification needed:
    Presumably the values for these "other columns" varies.

    Do you want some kind of aggregation of those columns, or are you only looking to see the row from the top of each hour?

  4. #4
    Join Date
    Oct 2013
    Posts
    2
    Thank you for your reply. Yes, I need to take the top row of each hour (first hour). The database is getting values from a monitoring device that monitors the load demand in every 2 mins. However, this is just too many values for analysis purposes hence only the the first row of each hour is needed.

    Is it possible to extract half-hourly interval (30 min) too?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so as suggested in post #2 use the appropriate date time function(s)
    that probably going to be minute

    eg where minute(mydatetimecolumn) = 30
    is you must extract those rows into another table then do so using a insert into
    eg
    Code:
    insert into mynewtable (my, comma, separated, column, list)
    select the, list, of, columns, required from myoldtable
    where minute(mydatecolumn) = 30
    personally I'd leave the data where it is and just extract as and when needed. If I had a performance problem then I'd consider extracting the minute as a separate column (and possibly the hour as well, BUT ONLY IF I KNEW I HAD a performance problem in the first place. I'd be very very reluctant to duplicate data in another table.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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