define variable using conditional statement in MySQL

Question

I have a table "products" with columns like:

| price | discount_price | start_discount | end_discount | ... |

*start_discount and end_discount is a UNIX timestamp

Current price of a product depends on time - if 'time()' is between 'start_discount' and 'end_discount' then the current price is 'discount_price', otherwise is 'price'

I am trying to return "current_price" variable. In PHP, I would just write:

$current_price = (time() > $start_discount && time() < $end_discount)? $discount_price : $price;

How can I achieve the same as above in MySQL without involving PHP?

I have this so far:

SELECT *, (**price OR discount_price**) as current_price FROM products WHERE active = 1 AND current_price BETWEEN '100' AND '200' ORDER BY current_price;

Make sure to describe in your question the orphaned code at the end . — Thomas
– Thomas, Commented Oct 17, 2014 at 13:05

Abhik Chakraborty · Accepted Answer · 2014-10-17 13:33:22Z

1

Here is a way to do it, I am only selecting the price you can select whatever you want from the table

select
case when
 now() > start_discount AND now() < end_discount then discount_price
else price end as current_price
from 
products 
WHERE 
active = 1 
AND price BETWEEN '100' AND '200'
ORDER BY current_price

EDIT : Just noticed you are using current_price in the where condition, which you can not do since its an alias so you may need to use a table column in the where clause or use having clause as

select
case when
 now() > start_discount AND now() < end_discount then discount_price
else price end as current_price
from 
products 
WHERE 
active = 1 
having current_price BETWEEN '100' AND '200'
ORDER BY current_price

Here is a test I did

mysql> create table products (price int, discount_price int ,start_discount date ,end_discount date);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into products values (100,60,'2014-10-01','2014-11-01'),(200,160,'2014-10-01','2014-10-10'),(300,200,'2014-11-01','2014-11-15');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select case when  now() > start_discount AND now() < end_discount then discount_price else price end as current_price from  products order by current_price;
+---------------+
| current_price |
+---------------+
|            60 |
|           200 |
|           300 |
+---------------+
3 rows in set (0.00 sec)

edited Oct 17, 2014 at 13:33

answered Oct 17, 2014 at 13:10

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

trojan Over a year ago

Looks promising - but I have just tested it and current_price is returning 'price' on 'discount_price' products

Abhik Chakraborty Over a year ago

Check my update I just did a test and it works, make sure your start_discount and end_discount is in proper data type date,datetime or timestamp

trojan Over a year ago

That was the problem :) my date is in UNIX timestamp so I have to use UNIX_TIMESTAMP() instead of now(), thank you!

Brian Hoover · Accepted Answer · 2014-10-17 13:28:25Z

1

You could do this with an IF statement in mySQL

select id, name,
  if(startDiscount_at <= now() && endDiscount_at > now(), discountPrice, price) as current_price
from products
  where active = 1
  having current_price between 100 and 200
  order by current_price;

Link to SQLFiddle

answered Oct 17, 2014 at 13:28

Brian Hoover

7,9652 gold badges31 silver badges41 bronze badges

1 Comment

trojan Over a year ago

I have tested your answer as well and it is working too :) thanks

Collectives™ on Stack Overflow

define variable using conditional statement in MySQL

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related