SELECT news_id, title, description, MONTH(FROM_UNIXTIME(date)) AS readableDate FROM re_news WHERE YEAR(FROM_UNIXTIME(date)) = MAX(YEAR(FROM_UNIXTIME(date)))

There is a SQL query, you need to select the news with the largest year, for example, all the news for the last year is sql. But this query works.

 SELECT news_id, title, description, MONTH(FROM_UNIXTIME(date)) AS readableDate FROM re_news WHERE YEAR(FROM_UNIXTIME(date)) = 2018
  • Select the maximum year in the subquery. - msi
  • one
    did it all the same So I did it wrong. WHERE YEAR(FROM_UNIXTIME(date)) = (SELECT MAX(YEAR(FROM_UNIXTIME(date))) FROM re_news) - Akina
  • one
    @Akina, in my opinion, here you can do with one “YEAR (FROM_UNIXTIME (date))” in the subquery - generate a date (01.01.year) and select everything from this date - this will avoid the second FTS (Full Table Scan) if “ date ”indexed - MaxU
  • one
    @MaxU If there is an index by date - it will not be FTS, but an index scan. And about the assembly date of the beginning of the year - I agree. Thanks for the amendment. And then, taking into account the storage format, also convert the resulting date to UNIX_TIMESTAMP in order not to have a function at all and use the index when selecting in full. - Akina
  • one
    @MaxU I will not. And for a strange at first glance reason. If to make out the answer - it is necessary to give a ready-made solution taking into account everything discussed. But IMHO the author can do it on his own, and it will be much more useful for him. And if he then publishes his decision as an answer, and he also provides a speed comparison, then it’s true that there’s a response. - Akina

0