I want to return from database just the rows added "this year" (current year). Creation date for each row is stored as a timestamp in a decimal format.
One method that i imagine is to get the timestamp range for current year and then select just the rows that fit this range. I have google it but with no success. Is there a way to compare the given year against that database stored timestamp?
The usual pattern is as you describe... comparing the bare column to a range.
We can use an expression to derive the start and end of the range. In this case, the beginning of the year and beginning of the next year.
EDIT
If datatype of creation_date column is DATE, DATETIME, or TIMESTAMP format, then, as an example:
WHERE t.creation_date >= DATE_FORMAT(NOW(),'%Y-01-01') + INTERVAL 0 MONTH
AND t.creation_date < DATE_FORMAT(NOW(),'%Y-01-01') + INTERVAL 12 MONTH
We can test the expressions with a SELECT statement
SELECT DATE_FORMAT(NOW(),'%Y-01-01') + INTERVAL 0 MONTH AS d1
, DATE_FORMAT(NOW(),'%Y-01-01') + INTERVAL 12 MONTH AS d2
returns
d1 d2
---------- ----------
2016-01-01 2017-01-01
Doing the comparison on the "bare" column is a pattern that will allow for MySQL to use a range scan operation on a suitable index, if one is available.
If we were to wrap the column in a function, that would force MySQL to evaluate the function for every row in the table. So I don't recommend this pattern. But as an example:
WHERE YEAR(t.creation_date) = YEAR(NOW())
EDIT
The edited question says created_date column is stored as a decimal datatype.
As long as the stored values are canonical (That is, the value of a later date will always compare as "greater than" the value of any earlier date) ...
then the same pattern applies... compare the bare column to the start and end of the range.
WHERE t.decimal_col >= beginning_of_this_year
AND t.decimal_col < beginning_of_next_year
Just need to provide decimal values (or expressions that return the decimal values) representing the beginning_of_this_year and beginning_of_next_year.
Absent a more precise definition of what values are stored, and what dates those values values represent, I can't give a more specific example.
You can use a str_to_date function
STR_TO_DATE('31/12/2016 8:06:26 AM', '%d/%m/%Y %r')
and for filter
where year( STR_TO_DATE('31/12/2016 8:06:26 AM', '%d/%m/%Y %r') ) = 2016