CODEWITHSUNDEEP
mysql
Nikk
Nikk

Reputation: 7889

Selecting time-based data

What I am trying to achieve is grab data from the database, depending on the time.

For example, I may have multiple prices for an item, and I would like the new price to be effective based on the time and date. So I can schedule price changes in advance.

id   link_id   datetime               price
-------------------------------------------
2    11        2016-11-03 00:00:00    1020
3    11        2016-11-03 01:00:00    1050
4    11        2016-11-03 03:00:00    1090

Let's say the time is 2016-11-03 00:59:00, when a user queries the db they will se the price-1020. But when they query the db a minute later at 2016-11-03 01:00:00 they should get price-1050.

Have tried this WHERE datetime < UTC_TIMESTAMP(), however this does not solve my problem. Also it only needs to select one entry, this selects multiple.

Is there a way MySQLi can do this?

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If you are only looking for one item, I would expect something like this:

select p.*
from prices p
where p.item_id = $item_id and  -- however you are representing items
      p.datetime <= now()  -- or UTC Timestamp if that is how the date/time is represented
order by p.datetime desc
limit 1;

I could speculate that "link_id" refers to "items".

Upvotes: 0

Related Questions