Filter per year with a timestamp on SQL

user_17344316 0
Viewed 247

I'm having some trouble with filtering for a specific year with a timestamp.

This is the code I have to convert the timestamp into the yyyy-mm-dd format:

SELECT rating, timestamp, FROM_UNIXTIME(timestamp) as datetime, CAST(FROM_UNIXTIME(timestamp) as date) AS date_value FROM ratings;

Result from this query: result from this query

Then I tried to extract the ratings for the year 2000 with

  • WHERE year=2000
  • WHERE date_value >= 2000-01-01 AND date_value <= 2000-12-31
  • HAVING date_value >= 2000-01-01 AND date_value <= 2000-12-31
  • HAVING year=2000

Nothing seems to work. Could you help me with this please? Here is a picture of my database if it can help: db

unix-timestamp timestamp mysql sql
1 Answers

try this

SELECT rating, timestamp, FROM_UNIXTIME(timestamp) as datetime, 
CAST(FROM_UNIXTIME(timestamp) as date) AS date_value FROM ratings
WHERE year(CAST(FROM_UNIXTIME(timestamp) as date) )=2000  ;
user_39185420
Related