Good day.

It is necessary to make a sample of the number from the database.

At once I will say the field is the date has the format not date, but int.

Everything is recorded from the time () function.

The database has approximately the structure

id time 1 1394553008 2 1394553008 3 1396553308 4 1324233008 ...

I tried to make such a request

 SELECT * FROM `data` WHERE YEAR(time)='2014';

The result was received, but this is so strange, about 100 records are stored in the database, and all were entered this year, but only 6 were received as a result.

So to be in such cases?

  • And I would have stored in the database DATETIME and already in the code if necessary I would translate it into a timestamp, especially in php for example there are standard methods for this. Just so you will not rape mysql when sampling - Snow

1 answer 1

I don’t know whether mysql optimizes YEAR (time) at all for reliability I would do WHERE time<UNIX_TIMESTAMP("2015/01/01") AND time>=UNIX_TIMESTAMP("2014/01/01");

and so, YEAR is for datetime, so you need YEAR (FROM_UNIXTIME (time)) but something seems to me to be much slower than if you immediately count the boundaries