How to fix case statement with timestampdiff error 1064-CodePudding

I am using MySQL 8 and am trying to get the time difference between the current time and a field in my database table called "created_on". If the difference is less than 180 seconds, I need the query to return the number of seconds. If the number of minutes is > 180 and < 60, then print the number of minutes.

The following query keeps displaying the 1064 error code:

select current_timestamp, created_on,
case
when timestampdiff(second, created_on, current_timestamp) <= 180 then " seconds ago"
when timestampdiff(minute, created_on, current_timestamp) > 3 AND < 60 then " minutes ago"
else ''
end
from whp;

Can anyone offer advice on how to correct this?

CodePudding user response：

Like this:

select 
  current_timestamp, 
  created_on,
  case
    when timestampdiff(second, created_on, current_timestamp) <= 180 then " seconds ago"
    when timestampdiff(minute, created_on, current_timestamp) < 60 then " minutes ago"
    else ''
  end
from whp;

With a case statement it uses the first when that the condition matches. You do not need to test for > 3 minutes as the first match on <= 180s would pick that up. If there are cases where you do need a range you would need to use and in the following way

select 
  current_timestamp, 
  created_on,
  case
    when timestampdiff(second, created_on, current_timestamp) <= 180 then " seconds ago"
    when timestampdiff(minute, created_on, current_timestamp) > 3 and 
         timestampdiff(minute, created_on, current_timestamp) < 60  then " minutes ago"
    else ''
  end
from whp;

CodePudding user response：

SELECT CURRENT_TIMESTAMP, 
       created_on,
       CASE WHEN TIMESTAMPDIFF(SECOND, created_on, CURRENT_TIMESTAMP) BETWEEN 0 AND 180 
            THEN CONCAT(TIMESTAMPDIFF(SECOND, created_on, CURRENT_TIMESTAMP), " seconds ago")
            WHEN TIMESTAMPDIFF(MINUTE, created_on, CURRENT_TIMESTAMP) < 60 
            THEN CONCAT(TIMESTAMPDIFF(MINUTE, created_on, CURRENT_TIMESTAMP), " minutes ago")
            WHEN CURRENT_TIMESTAMP < created_on
            THEN 'in future'
            ELSE 'over an hour ago'
            END
FROM whp;