The database has a table with currency rates and fields with a timestamp, but data is collected several times on one day, so there are many duplicates, how can I write a request to select data with grouping by date, not date-time, for example, for the last year

  • one
    By grouping by date, do you mean a table with averages (for example) for 2016? or the application of a function to all types of currencies on a certain date? To select any period in mysql there are functions: year() month() week() ... - Sanek Zhitnik
  • Do you need courses at the end of each day? - artoodetoo
  • Question already resolved SELECT * FROM currency WHERE date> = DATE_SUB (CURRENT_DATE, INTERVAL 1 YEAR) AND chcode =? GROUP BY date, chcode - Anton Veselov
  • one
    You could post your own answer and announce it as a decision. This is a normal practice on SO But to add an answer to the question is NOT the recommended way. - artoodetoo
  • So did spsb - Anton Veselov

3 answers 3

You can use the GROUP BY operator, on a calendar field, which is passed through the MySQL function DATE()

 SELECT * FROM tbl GROUP BY DATE(created_at)

However, it is best to get rid of duplicates by simply adding a unique index for the date field and using the IGNORE keyword in the INSERT to ignore duplicate inserts.

  • And why did you decide that several entries per day is a mistake? The course can actually change several times a day. - artoodetoo
  • Because I take it from the Central Bank site once a day, I am not interested in dynamic - Anton Veselov
  • @ Anton Veselov Ok, then. It would be nice if you could add this to the question text. - artoodetoo

Timestamp range:

 $at = strtotime('25-04-2016'); //1461535200 $to = strtotime('26-04-2016'); //1461621600 SELECT * FROM table WHERE timestamp >= $at AND timestamp < $to

Date Range:

 SELECT * FROM table WHERE DATE(timestamp) = '2016-05-20' SELECT * FROM table WHERE timestamp >= '2016-05-20 00:00:00' AND timestamp <= '2016-05-20 23:59:59'
  • Psb, and if so? WHERE YEAR ( date ) = YEAR (DATE_ADD (NOW (), INTERVAL -1 YEAR)) - Anton Veselov

Solved the problem with the following SQL query

 SELECT * FROM currency WHERE date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR) AND chcode = ? GROUP BY date, chcode