October 3rd, 2013, 09:20 AM
How to get all information from a table to a new table--help needed on MySql
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
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.
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?
October 3rd, 2013, 09:33 AM
does the resulting table have to have that initial 30 second time? if so, why? what's this all for?
October 3rd, 2013, 12:33 PM
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
October 6th, 2013, 12:07 AM
Yes. I need to include the date too with the hourly time. Thank you.
Originally Posted by SimonJM
October 6th, 2013, 12:12 AM
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.
Originally Posted by r937