CODEWITHSUNDEEP
mysqlif-statementconditional-statements
BigDistance
BigDistance

Reputation: 135

MYSQL Simple IF statement based on CURRENT_TIME

I'd like to run an SQL statement, to return the price of a product, but if the current_time is within start / end date, return promo price. 

| id  |  price  |  promo_price  | promo_start | promo_end  |
+-----+---------+---------------+-------------+------------+
|  1  |   5     |      4        | 1375709473  | 1375709473 |

SQL to return JUST product price, regardless of promo:

SELECT p.`price` 
FROM products p 
WHERE p.`id` = 1

How would I create this as an SQL-IF statement?

Many thanks.

Upvotes: 2

Views: 252

Answers (2)

Code Lღver
Code Lღver

Reputation: 15603

SELECT IF(p.`promo_start` <= unix_timestamp() AND p.`promo_end` >= unix_timestamp(), p.`promo_price`,p.`price`) as price
FROM products p 
WHERE p.`id` = 1

Use above query.

OR you can use CASE:

SELECT 
   CASE WHEN p.`promo_start` <= unix_timestamp() AND p.`promo_end` >= unix_timestamp() 
         THEN p.`promo_price` 
         ELSE p.`price` END 
   FROM products p 
      WHERE p.`id` = 1;

Tutorials is here.

Upvotes: 1

Vatev
Vatev

Reputation: 7590

SELECT
    IF(unix_timestamp() BETWEEN p.promo_start AND p.promo_end, p.promo_price, p.price) as real_price
FROM products p 
WHERE p.id = 1

Upvotes: 3

Related Questions