How to fix case statement with timestampdiff error 1064

Question

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?

Check the line `when timestampdiff(minute, created_on, current_timestamp) > 3 AND < 60 then " minutes ago"` to be correct you should change to `when timestampdiff(minute, created_on, current_timestamp) > 3 AND timestampdiff(minute, created_on, current_timestamp) < 60 then " minutes ago"` — Ergest Basha, Jan 18 '22 at 12:05

score 1 · Answer 1 · answered Jan 18 '22 at 11:50

1

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;

answered Jan 18 '22 at 11:50

Ian Kenney

6,376
1
25
44

What is the reason for to check the condition which matches previous condition? – Akina Jan 18 '22 at 11:58
The problem on the `OP` question is the use of operator `AND` `timestampdiff(minute, created_on, current_timestamp) > 3 AND timestampdiff(minute, created_on, current_timestamp) < 60 AND` – Ergest Basha Jan 18 '22 at 12:07
1

Noted @Ergest_Basha - I included that in the answer. I think though it is better to use the first solution in this case as it will have the same result with less typing and the same level of clarity – Ian Kenney Jan 18 '22 at 12:10
Sorry to ask again, guys. The line that Ergest noted worked to remove the error, but I cannot get the number of minutes to print with the words "minutes ago" affixed to the number of minutes. I have tried the following "timestampdiff(minute, created_on, current_timestamp) < 60 then concat(timestampdiff(minute, created_on, current_timestamp) + " minutes ago") – wilcan Jan 18 '22 at 12:35

score 1 · Answer 2 · answered Jan 18 '22 at 11:58

1

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;

answered Jan 18 '22 at 11:58

Akina

39,301
5
14
25

thank you for your answer. This is exactly how I want the result to appear. I don't have enough reputation points to like your reply. Thank you! – wilcan Jan 18 '22 at 13:03
@wilcan at the left of this answer you should find a green ticket if I'm not wrong check that and this is how you accept an answer. And now you have the points to upvote it , too – Ergest Basha Jan 18 '22 at 13:17
See "[Explaining entirely code-based answers](https://meta.stackoverflow.com/q/392712/128421)". While this might be technically correct it doesn't explain why it solves the problem or should be the selected answer. We should educate in addition to help solve the problem. – the Tin Man Jan 18 '22 at 23:03
@theTinMan I not only read the words, but also understand their meaning. Please point me to the moment of this query which is not clear and needs in additional explanations (it is believed that the one who looks at the answer not only looks, but also at least reads the RM on the statements and functions used)? I'll add according explanations. – Akina Jan 19 '22 at 04:50

How to fix case statement with timestampdiff error 1064

2 Answers2