Representing expiring records-CodePudding

I need to store tariffs connected to a port. So, the table can look like this:

create table tariffs(
    int NOT NULL AUTO_INCREMENT,
    price decimal(12,2),
    expiry bigint(11)
)

expiry represents a timestamp when that particular tariff will expire. So I might have data like this:

id | price    | expiry
1  | 11.00    | 30/Jan/2022
2  | 12.00    | 30/Feb/2022
3  | 13.00    | 30/Mar/2022
4  | 14.00    | 30/Apr/2022
5  | 15.00    | null

In this case, ID 5 isn't expired yet, meaning that it's current. (I realise I put dates, there, rather than timestamps; I did so that it's easier to read)

The problem I have is in the logic to figure out which tariff to use given a specific date. In an ideal world, if 5 were "Infinite", I could just do WHERE expiry > date_apply limit 1 -- however, I don't have that luxury since date_apply won't be returned at all.

I COULD assign a very big number to expiry for the "current" entry. It would make the query work regardless. But... it feels wrong.

Somebody recommended using TWO fields for each tariff, a "from" and a "to", telling me that otherwise querying will be a nightmare. I am beginning to see what they mean... but then I fear operators might unwillingly have "holes" in the timeframes for tariffs, which would be difficult to prevent.

How should I organise my table, and how should I query it? What's the best practices here?

CodePudding user response：

You can leave the ultimate price with an expiry null and we can use coalesce to assign a value according to the logic needed at the time.
Here we start with only an expired tarif and the expiry = null tarif. We create a view that gives us the expiry as undefined. We then add a tarif which is valid and it is correctly returned by the same view.

create table tariffs(
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    price decimal(12,2),
    expiry date);
insert into tariffs (price,expiry) values (11,'2022-01-30'),(12,null);

create view current_tarif as
select id, price, coalesce(expiry,'undefined') expiry
from tariffs
where coalesce(expiry,'3000-12-31') > curdate() 
order by coalesce(expiry,'3000-12-31')
limit 1;

select * from current_tarif;

id | price | expiry   
-: | ----: | :--------
 2 | 12.00 | undefined

insert into tariffs (price,expiry) values (15,'2022-12-30');

select * from current_tarif;

id | price | expiry    
-: | ----: | :---------
 3 | 15.00 | 2022-12-30

db<>fiddle here

CodePudding user response：

SELECT COALESCE(t2.price, t1.price) AS price
FROM (SELECT price FROM tariffs WHERE expiry IS NULL LIMIT 1) AS t1
LEFT OUTER JOIN (SELECT price FROM tariffs WHERE expiry > ? LIMIT 1) AS t2

Demo: https://www.db-fiddle.com/f/wykqR5X7B9S424AWkA4aQy/0

The first subquery is bound to return 1 row if you have at least one unexpired tariff.

The second subquery may not return 1 row, if you put in a date too late. So I change this join to LEFT OUTER JOIN. If there is no matching row for the condition on expiry, the subquery will return no rows, and the outer join will replace these with NULLs.

So if t2.* is NULL, then the COALESCE() defaults to the unexpired value in t1.price.

CodePudding user response：

Build your validity and expiry dates/timestamps as you go, using OLAP functions.

WITH                                                                                                                                                                                                                                   
indata(id,price,expiry) AS (
          SELECT 1,11.00,DATE '30-Jan-2022'
UNION ALL SELECT 2,12.00,DATE '28-Feb-2022'
UNION ALL SELECT 3,13.00,DATE '30-Mar-2022'
UNION ALL SELECT 4,14.00,DATE '30-Apr-2022'
UNION ALL SELECT 5,15.00,NULL
)
,
enriched AS (
  SELECT 
    id
  , price
  , LAG(NVL(expiry, '9999-12-31'),1,'0001-01-01') OVER(ORDER BY id)  AS   validity
  , NVL(expiry, '9999-12-31')                    AS expiry
  FROM indata
  -- chk  id | price |  validity  |   expiry   
  -- chk ---- ------- ------------ ------------
  -- chk   1 | 11.00 | 0001-01-01 | 2022-01-30
  -- chk   2 | 12.00 | 2022-01-30 | 2022-02-28
  -- chk   3 | 13.00 | 2022-02-28 | 2022-03-30
  -- chk   4 | 14.00 | 2022-03-30 | 2022-04-30
  -- chk   5 | 15.00 | 2022-04-30 | 9999-12-31
)
SELECT
  price
FROM enriched
WHERE '2022-04-22' >= validity
  AND '2022-04-22  < expiry
;