CODEWITHSUNDEEP
mysqlsqldatabase
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40643

SQL: how to get last items?

I have a log for documents that go through my application. The log looks like this:

TABLE: log
==================================================
| log_id | document_id | status_code | timestamp |
==================================================
| 1      | 10          | 100         | 12345     |
--------------------------------------------------
| 2      | 10          | 200         | 23456     |
--------------------------------------------------

I need a list of document_id that have been "stuck" in a certain status_code for a given duration (say 10 minutes; timestamp is Unix timestamp, btw). If a particular document_id is "stuck" in a certain status_code, that status_code will be the last status_code for that document_id.

How do I query this? The 2 things I'm not sure of:

  1. How do I check if the document has been in a certain status for a certain period of time? I'm guessing I can use some sort of function/formula in my SQL (e.g. now - 10 minutes), but I don't know how to do that.
  2. How do I check the duration of only the last status code?

Upvotes: 1

Views: 348

Answers (3)

Corina
Corina

Reputation: 1545

SELECT log.document_id, log.status_code, max(log.timestamp) - min(log.timestamp)
FROM ( 
  SELECT MAX(log_id) as log_id
  FROM log
  GROUP BY document_id) latestLog
    INNER JOIN log latestStatus ON latestStatus.log_id = latestLog.log_id
    INNER JOIN log on latestStatus.status_code = log.status_code
GROUP BY log.document_id, log.status_code
HAVING (max(log.timestamp) - min(log.timestamp)) > 600

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115560

SELECT log.document_id
     , (UNIX_TIMESTAMP() - log.timestamp) / 60
       AS MinutesSinceLastChange 
FROM log
  JOIN
    ( SELECT document_id
           , MAX(timestamp) AS last_change
      FROM log
      GROUP BY document_id
      HAVING (last_change < (UNIX_TIMESTAMP() - 60 * 10))  <-- that is 10 minutes
    ) AS grp
    ON  grp.document_id = log.document_id
    AND grp.last_change = log.timestamp
WHERE log.status_code = "200"             <-- code you want to check

Upvotes: 2

Piotr Salaciak
Piotr Salaciak

Reputation: 1663

For Your no. one and no. two question, this is my proposition for (I guess You are using MySQL):

SELECT 
    `document_id`, 
    SUBSTR(GROUP_CONCAT(RPAD(`status_code`,5) ORDER BY `timestamp` DESC), 1, 5) AS `last_status`,
    SUBSTR(GROUP_CONCAT(RPAD(`status_code`,5) ORDER BY `timestamp` DESC), 7, 5) AS `prev_status`,

    UNIX_TIMESTAMP(SUBSTR(GROUP_CONCAT(FROM_UNIXTIME(`timestamp`) ORDER BY `timestamp` DESC), 1, 19)) AS `last_timestamp`,
    UNIX_TIMESTAMP(SUBSTR(GROUP_CONCAT(FROM_UNIXTIME(`timestamp`) ORDER BY `timestamp` DESC), 21, 19)) AS `prev_timestamp`
FROM `log`
GROUP BY `document_id`
HAVING `last_timestamp` - `prev_timestamp` > 60*10 AND `last_status` IN (100,200);

All right, what is happing there. We're grouping rows by document_id, and ordering status codes and timestamps inside GROUP_CONCAT to get the last and pre-last entry.

If Your status code can have more than 5 digits, then replace it RPAD(status_code,X), where X is maximal number of status_code length

60*10 - is 10 minutes

last_status IN (100,200) - status codes You want to get only.

Upvotes: 0

Related Questions