Solved Checking whether predefined time has passed (MySQL ban)

Discussion in 'Plugin Development' started by Errno452, Sep 28, 2013.

Thread Status:
Not open for further replies.
  1. Offline

    Errno452

    Hey.
    So I'm making a plugin that stores the start and end times and dates of a ban inside a MySQL table. The following code that is supposed to decide on player join whether to let the player in or not, works fine except for 1 problem:
    No matter whether the end TIME has passed or not, the player can log in (assuming it is the date when the ban is set to end). If the date has not come yet, he will still be banned just like supposed to. I used the same system for both date and time, but only date works.
    So basically I could only make bans that are accurate by 1 day, but not the time. Any tips? Hopefully I wasn't too confusing!
    Thanks in advance!

    Code:java
    1. @EventHandler
    2. public void onPlayerJoin(PlayerJoinEvent event) {
    3. Player p = event.getPlayer();
    4. openConnection();
    5. try {
    6. if (playerDataContainsPlayer(p.getName().toLowerCase())) {
    7. if (playerIsBanned(p)) {
    8. PreparedStatement sql = connection.prepareStatement("SELECT * FROM `bantable` WHERE player=?;");
    9. sql.setString(1, p.getName());
    10. ResultSet result = sql.executeQuery();
    11. result.next();
    12.  
    13. Calendar cal = Calendar.getInstance();
    14. Calendar cal2 = Calendar.getInstance();
    15. Time compareStringOne = result.getTime("time"); //"time" is the time when the ban should end in HH:mm:ss
    16. if (cal.getTime().after(result.getDate("ends"))){ // (WORKS), "ends" is the date in yyyy-MM-dd when the ban is gonna expire
    17. if (compareStringOne.before(cal2.getTime())) {// (DOESN'T WORK)
    18. unbanPlayer(p.getName());
    19. }else{
    20. p.kickPlayer(result.getString("reason"));
    21. }
    22. }else{
    23. p.kickPlayer(result.getString("reason"));
    24. }
    25. sql.close();
    26. result.close();
    27. }
    28. }
    29. }catch (Exception e){
    30. e.printStackTrace();
    31. }finally{
    32. closeConnection();
    33. }
    34. }
     
  2. Offline

    amhokies

    Errno452
    Question. Why do you need two different instances of a Calender object? Can't you just use the same one for the date and the time?
     
  3. Offline

    Errno452

    Yeah I did that at first but I was messing around trying everything possible, but you are right. Still makes no difference :)

    Anyone?

    C'mon someone must know what I'm doing wrong.. I commented after the problematic line in the code. No idea why I can't check both date and time the same way.

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 4, 2016
  4. Offline

    nisovin

    Is "time" a full datetime value or just a time value?
     
  5. Offline

    Errno452

    nisovin
    "time" is get by using getTime from the current Calendar.getInstance when the ban is issued, then saved to MySQL. This same time is then used as the unban time because the bans I will issue will always be full days. It displays as hh:mm:ss inside the MySQL table.
     
  6. Offline

    nisovin

    Well, if the "time" field is just a time type, then this is failing because it has no date, but the value returned from cal.getTime() does have a date portion on it.

    You really don't need both an "end" and "time" field, you just need one datetime field. You don't need to use the Calendar class either, just get, set, and compare from within MySQL, it makes things easier.

    For example, to set a datetime to the current time:

    UPDATE `bantable` SET `end` = NOW() ...

    And to check if the time is in the past:

    SELECT IF(`end` < NOW(), 1, 0) AS `expired` ...
     
  7. Offline

    1Rogue

    Idea: instead of storing a start and end time, just use variable to check if a player is banned or not, and then store an end date.

    Code:sql
    1. UPDATE `bantable` SET `banned`=1, `ban_end`=DATE_ADD(NOW(), INTERVAL 2 WEEK)


    and modifying the interval based on input. This allows for a lot more flexibility with plain sql:

    Check if banned (null if not banned, otherwise a date):
    Code:sql
    1. SELECT (`ban_end`) FROM `bantable` WHERE `playername`='example' AND `banned`=1


    A good login query:
    Code:sql
    1. UDPATE `bantable` SET `banned`=0 WHERE DATE_SUB(`ban_end`, NOW())<=0 AND `playername`='example';
    2. SELECT COUNT(*) FROM `bantable` WHERE `playername`='example' AND `banned`=0


    If the above second query returns 0, then they are banned, or if it returns 1 they are not banned.
     
  8. Offline

    Errno452

    nisovin
    Thanks for the tip.
    I've been trying to get that to work for a while now, but I think I might have misunderstood you (or then I'm just too noob in java). How exactly should I get the full datetime value in the first place anyways?


    1Rogue
    I also coded a php banlist for my forums that gets info from MySQL and displays the start and end times of all current bans (and yes I am already using 'banned' 1 or 0), so I'm not sure if that would quite work this way or am I wrong?
     
  9. Offline

    1Rogue


    I don't believe it would work based for start time, but I don't think that's really necessary info, considering you know the end time.
     
  10. Offline

    nisovin

    The NOW() function in MySQL gives the full datetime. You just have to store that in a datetime field.
     
  11. Offline

    Errno452

    Thank you guys, I got it working by only replacing all of my previous work with 1 single MySQL line.
    Million thanks!
     
Thread Status:
Not open for further replies.

Share This Page