将当前年份时间戳与mysql存储的时间戳进行比较

Question

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?

Answer 1

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.