Help with timestamp feature (SQL)

Discussion in 'Plugin Development' started by es359, Sep 14, 2014.

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

    es359

    So I have a plugin that logs a playername and the most recent message they chatted on the server to a MySQL table. My current problem is how to attach a timestamp to each message. Possibly in the format
    01/1/1996 || 23:59:00 ETC.

    I've already looked at some features related to timestamp, but I'm still stuck.

    Here is an example of the table I already have set up and working.

    Thanks.

    [​IMG]
     
  2. Offline

    mythbusterma

    es359 likes this.
  3. Offline

    fireblast709

    rbrick, mythbusterma and es359 like this.
  4. Offline

    guitargun

    es359 what I used for a plugin with that kind of timestamp was this
    Code:java
    1. Timestamp time = new Timestamp(Calendar.getInstance().getTimeInMillis());
    2. pst.setTimestamp(3, time);


    the pst is from a preparedStatement that just basicly is a insert/update String.

    the output looks like this:
    [​IMG]
     
    es359 likes this.
  5. Offline

    es359

    Thanks! I'll look into this.
     
  6. Offline

    guitargun

    btw make sure that in your table it is noted as datetime. otherwise there can be some problems
     
  7. Another reason that fireblast709 's solution is clearly superior.
     
  8. Offline

    es359

    I get this error in the console.

    http://pastebin.com/Z4BEjgyj
     
  9. es359 Read the exception message, seems pretty clear to me.
     
  10. Offline

    es359

    I get the message... But I'm unsure on what the parameters it is asking for...
     
  11. es359 Please provide your code :) (Not just the 2 lines)
     
    es359 and Garris0n like this.
  12. Offline

    Garris0n

    No, it should be a Timestamp. Datetime stores a date and a time, Timestamp stores a timestamp and automatically converts it depending on the machine's timezone. You're inserting a Timestamp.

    Post the code.
     
  13. Offline

    es359

    Creation of tables; (Placed inside the onEnable method.)
    PHP:
     try {
              
    DatabaseMetaData dbm sql.c.getMetaData();
              
    ResultSet tb dbm.getTables(null,null"users",null);
            
    //  tb = dbm.getTables(null,null,"chat",null);
     
              
    ResultSet chat dbm.getTables(null,null,"chat",null);
     
              if(
    tb.next() || chat.next()) {
                  
    Bukkit.getServer().getConsoleSender().sendMessage(ChatColor.BOLD "" ChatColor.RED"Already found the correct table(s)!");
                  return;
              }else {
                  
    PreparedStatement ps sql.c.prepareStatement("CREATE table users (name varchar(50), IP varchar(50) );");
                  
    ps.executeUpdate();
                  
    ps.close();
                  
    Bukkit.getServer().getConsoleSender().sendMessage(ChatColor.RED +"SQL Table 'users' created!");
                  
    System.out.println("");
     
     
     
               
                  
    PreparedStatement statement sql.c.prepareStatement("CREATE table chat (playername varchar(50), chat_message varchar(200), datetime varchar(50) );");
     
                  
    statement.executeUpdate();
                  
    statement.close();
                  
    Bukkit.getServer().getConsoleSender().sendMessage(ChatColor.RED "SQL Table 'chat' created!");
              }
     
          }catch(
    Exception e) {
              
    e.printStackTrace();
              
    Bukkit.getServer().getConsoleSender().sendMessage(ChatColor.DARK_RED +"Couldn't create the table!");
          }
    (Inside AsyncPlayerChat Method.)
    PHP:
     if(logChat) {
                    try {
     
                        
    Timestamp time = new Timestamp(Calendar.getInstance().getTimeInMillis());
     
     
                        
    PreparedStatement chat sql.c.prepareStatement("INSERT INTO myDatabase.chat (playername, chat_message, datetime)\nVALUES ('" event.getPlayer().getName() + "', '" event.getMessage() +"','" +time"' ); ");
                        
    chat.setTimestamp(3time);
                        
    chat.executeUpdate();
                        
    chat.close();
     
                        
    Bukkit.getServer().getConsoleSender().sendMessage(ChatColor.GRAY "Logged the chat for the player " event.getPlayer().getName());
     
                    }catch (
    Exception e) {
                        
    e.printStackTrace();
     
                    }
                }
    Please note that I am new to SQL. I am using a book called SAM's teach your self SQL.

    Garris0n AdamQpzm
     
  14. es359 You should look up some tutorials on how prepared statements work :)
     
    es359 likes this.
  15. Offline

    es359

    Sounds good. Any idea on some sources? I've heard The new Boston has some good tutorials.
    AdamQpzm
     
  16. Offline

    Garris0n

    You should also use CREATE TABLE IF NOT EXISTS.
     
    es359 likes this.
  17. Offline

    guitargun

    Good notice, I changed it right away
     
Thread Status:
Not open for further replies.

Share This Page