Good day, there is a table with data, the interval of records varies from 1 minute to several days. You need to upload data for the entire time period at intervals of 1 hour (where there is nothing more to do, but you need to somehow lower those records that are more than 1 hour ) Whether prompt really to make request to the subd or it is necessary to form the separate table?

    1 answer 1

    You can do the following:

    SELECT * FROM tbl GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d %H') ORDER BY created_at DESC;

    Here, created_at is a DATETIME type field, using the DATE_FORMAT() function, DATE_FORMAT() cut off the time of the minute and second '2016-07-21 01' and group the table entries by the received row. MySQL returns one record from the group.

    • Or an error if sql_mode ONLY_FULL_GROUP_BY . The default is 5.7. - Small
    • @ Small SQL mode is included in the configuration file level, it is not enabled in the server itself by default. Including sql_mode or not in my.cnf is decided by the developers of distributions and packages (I’ll remind you in the documentation, and MySQL itself is not recommended to compile). Very often, the sql_mode in my.cnf is not in principle (or is, but not the one in the documentation), and if so, the developers for some reason seek to remove it from my.cnf for compatibility with the rest of the MySQL community and then it remains naked the server and in it this request works, works including in MySQL 5.7. - cheops
    • Already included. More specifically, since 5.7.5 ONLY_FULL_GROUP_BY is included in the default value. - Fine
    • Of course, I’ll not say for all distributors - but the official deb packages from repo.mysql.com are with ONLY_FULL_GROUP_BY active. In my.cnf no sql_mode is specified. dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-5.html SQL Mode Notes Incompatible Change - Minor
    • Just getting the error only_full_group_by, I found solutions with ANY_VALUE (), but the function does not return FLOAT fields correctly, disable ONLY_FULL_GROUP_BY is also not a way out - user21736