2

Here is what I have right now

SELECT name, admin, reason, unban,
FROM_UNIXTIME(date) AS datetime, HOUR(difference) AS HRemain,
MINUTE(difference) AS MRemain
FROM (SEC_TO_TIME(AVG(TIMESTAMPDIFF(MINUTE, unban, NOW()))) AS difference FROM banned, banned) 
WHERE name = '%e'

I get some syntax error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(AVG(TIMESTAMPDIFF(MINUTE, unban, NOW()))) AS difference FROM banned, banned) WH' at line 3

Here is what I am trying to achieve.

Table banned

name   | admin     | reason | unban(TIMESTAMP) | date
Yashas | SomeAdmin | test   | 180              | 60

Required Results:

Player who was banned: Yashas (given)
Administrator who banned: SomeAdmin (need to fetch from the db)
Banned on: January 1st, 1971 (must be obtained from date) (Need the string as datetime) (I decided that I don't need the time, just the date as a string)
Unban Time left: 0 Hours 2 Minutes (I need the hours left as HRemain and minutes left as MRemain)

I need the following:

  • unban as a date-time string
  • time remaining for unban from current time. I need just hours and minutes remaining.
  • and rest of the content of the table as it is including unban

If possible optimize the query for the following: If unban is set to 0 then it means its a permanent ban and therefore I don't need the time remaining until unban. So in every case where unban is zero, I don't need HRemain, MRemain.

Thanks

EDIT:

CREATE TABLE `banned` (
`name` varchar(21) NOT NULL,
`admin` varchar(21) NOT NULL,
`reason` varchar(81) NOT NULL,
`unban` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My old query which was working but isn't efficient.

SELECT name, admin, reason, unban, FROM_UNIXTIME(date) AS datetime, 
HOUR(SEC_TO_TIME(AVG(TIMESTAMPDIFF(MINUTE, unban, NOW())))) AS HRemain, 
MINUTE(SEC_TO_TIME(AVG(TIMESTAMPDIFF(MINUTE, unban, NOW())))) AS MRemain 
FROM banned WHERE name = '%e' 
1
SELECT name, admin, reason, unban, FROM_UNIXTIME(date, '%Y-%m-%d') AS datetime, 
HOUR(x.difference) AS HRemain, MINUTE(x.difference) AS MRemain 
FROM(SELECT SEC_TO_TIME(AVG(TIMESTAMPDIFF(MINUTE, unban, NOW()))) AS difference FROM banned) AS x, banned 
WHERE name LIKE 'Yashas' 

Solved it myself. Just had to get a textbook and study.

EDIT: Server returns datetime as NULL for some reason.

  • Your SQLFiddle won't work with the SQL as written. – Vérace Feb 14 '16 at 20:28
  • datetime is always returned as NULL and HRemain and MRemain are not set to correct values. – Yashas Feb 15 '16 at 6:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.