CODEWITHSUNDEEP
mysqlsql
west
west

Reputation: 25

Mysql Database query with if condition

I have a table name video

table video

id  name   upload_date

1   one     1408336348
2   two     1409884215

now i want to select all data also calculate if video uploaded between last 2 days then yes or no

result like 

 id  name   upload_date   new

1   one     1408336348    no
2   two     1409884215    yes

I am using this query but not work

SELECT v.*,( if(from_unixtime(v.upload_date) < NOW() - INTERVAL 2 DAY) then 'no' else 'yes') 
 AS new FROM `video` as v

Query return error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') then 'no' else 'yes') 
AS new FROM `video` as v
LIMIT 0, 25' at line 1

Upvotes: 0

Views: 63

Answers (3)

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

Try using case

SELECT v.*
    ,case
        when (from_unixtime(v.upload_date) < NOW() - INTERVAL 2 DAY) then 'yes'
        else 'no')
     end AS new
FROM `video` as v

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415800

Try a CASE statement:

SELECT v.*, 
    CASE WHEN from_unixtime(v.upload_date) < NOW() - INTERVAL 2 DAY 
         THEN 'Yes' 
         ELSE 'No' 
    END AS new 
FROM `video`

This is the ansi-standard way to do it in SQL.

Upvotes: 0

gen_Eric
gen_Eric

Reputation: 227240

That's not the correct syntax for an IF in MySQL. Try it like this:

SELECT v.*,
    IF(from_unixtime(v.upload_date) < NOW() - INTERVAL 2 DAY, 'yes', 'no') AS new
FROM `video` as v

Upvotes: 1

Related Questions