get rows based on expiry time

Question

http://sqlfiddle.com/#!9/406cb/2

I have a table books with category 1, 2, 3, 4.

• Category 1 - will stay for 1 hr
• Category 2 - will stay for 2 hrs
• Category 3 - will stay for 4 hrs
• Category 4 - will stay for 6 hrs

How can I write a query to get desired results?

E.g.:

1. if current time is 1454411248 then it should show all books
2. if current time is 1454414847 then it should not show category 1 books with unixtime=1454411248(expiry after an hr)
3. if current time is 1454418448 the category - 1, 2, 3, 4 below 1, 2, 4, 6 hrs of expiry time should not show (unix_time will be dynamic. I have used static value for example).

so on..

Table:

CREATE TABLE `books` (
    `id` int(255) NOT NULL AUTO_INCREMENT,
    `name` longtext NOT NULL,
    `category` varchar(255) NOT NULL,
    `unix_time` bigint(20) NOT NULL,
    `time_data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;


INSERT INTO `books` (`id`, `name`, `category`, `unix_time`, `time_data`) VALUES
    (1, 'book1', '1', 1454411248, '2016-02-02 05:37:28'),
    (2, 'book2', '2', 1454411248, '2016-02-02 05:37:28'),
    (3, 'book3', '3', 1454411248, '2016-02-02 05:37:28'),
    (4, 'book4', '4', 1454411248, '2016-02-02 05:37:28'),
    (5, 'book5', '1', 1454411248, '2016-02-02 05:37:28'),
    (6, 'book6', '2', 1454411248, '2016-02-02 05:37:28'),
    (7, 'book7', '3', 1454411248, '2016-02-02 05:37:28'),
    (8, 'book8', '4', 1454411248, '2016-02-02 05:37:28'),
    (9, 'book9', '1', 1454497648, '2016-02-03 05:37:28'),
    (10, 'book10', '2', 1454497648, '2016-02-03 05:37:28'),
    (11, 'book11', '1', 1454497648, '2016-02-03 05:37:28'),
    (12, 'book12', '2', 1454497648, '2016-02-03 05:37:28'),
    (13, 'book13', '3', 1454497648, '2016-02-03 05:37:28'),
    (14, 'book14', '4', 1454497648, '2016-02-03 05:37:28'),
    (15, 'book15', '1', 1454497648, '2016-02-03 05:37:28'),
    (16, 'book16', '2', 1454497648, '2016-02-03 05:37:28'),
    (17, 'book17', '3', 1454497648, '2016-02-03 05:37:28'),
    (18, 'book18', '4', 1454497648, '2016-02-03 05:37:28');

Query:

SELECT *,
    CASE category
        WHEN '1' THEN '1454407648'
        WHEN '2' THEN '1454404048'
        WHEN '3' THEN '1454396848'
        WHEN '4' THEN '1454389648'
    END as category
from books
where unix_time >1454411248

Answer 1

First of all, you need a table to relate your category values to your duration values. You could create a physical table, or you could use a subquery like this to generate a virtual table.

   select 1 as category, 1 as duration UNION ALL
   select 2,2 UNION ALL select 3,4 UNION ALL select 4,6

This produces the following little lookup table.

|| *category* || *duration* ||
||          1 ||          1 ||
||          2 ||          2 ||
||          3 ||          4 ||
||          4 ||          6 ||

Next you need to join that lookup table to your books table, like so. This query can also include an expiration column, as shown.

select b.*,
       d.duration,
       FROM_UNIXTIME(unix_time) + INTERVAL d.duration HOUR expiration
  from books b
  join (select 1 as category, 1 as duration UNION ALL
        select 2,2 UNION ALL select 3,4 UNION ALL select 4,6
         ) d ON b.category = d.category

Finally, you append a WHERE clause to filter out the unexpired rows.

select b.*,
          d.duration,
         FROM_UNIXTIME(unix_time) + INTERVAL d.duration HOUR expiration
  from books b
  join (select 1 as category, 1 as duration UNION ALL
        select 2,2 UNION ALL select 3,4 UNION ALL select 4,6
         ) d ON b.category = d.category
  where FROM_UNIXTIME(unix_time) + INTERVAL d.duration HOUR >= '2016-02-03 08:00:00'

In this example I used 2016-02-03 08:00:00 as the current time. You could, in a production system, use NOW().

You would also be smart to use a sargable version of the where clause.

   where unix_time >= UNIX_TIMESTAMP('2016-02-03 08:00:00' - INTERVAL d.duration HOUR)

Finally, this is a design preference, but mixing DATETIME and unix timestamp columns is a little odd.

Answer 2

Create and populate a category table:

• category - id, hours_to_live

Change book to:

• book - id, name, category_id, unix_time, time_data

and reference the category table with the foreign key category_id.

Then you can achieve what you want with a simple JOIN:

SELECT b.*,
  FROM books b
  JOIN category c
    ON c.id = b.category_id
 WHERE b.unix_time >= NOW() - INTERVAL c.hours_to_live HOUR