CODEWITHSUNDEEP
javamysqlsqltimejodatime
Remi
Remi

Reputation: 53

Java gives 00:00:00 time when gathering dateTime from MySQL

I'm trying to gather a certain time from my SQL database, and then comparing it to the current date to check wether the user is currently banned.

It displays the date that it gathers from the db correctly, but the time is just 00:00:00 every time.

Here is the code I'm using:

public static boolean checkBan(String playerName, String type) {
    Format formatter;
    Date date = new Date();
    formatter = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
    try {
        createConnection();
        Statement statement = con.createStatement();
        String query = "SELECT * FROM players WHERE username = '"
                + playerName + "'";
        ResultSet results = statement.executeQuery(query);
        while (results.next()) {
            java.sql.Date mysqlDate = results.getDate(type);
            if (mysqlDate == null || formatter.format(date) == null) {
                return false;
            } else {
                java.util.Date dateConverter = new java.util.Date(
                        mysqlDate.getTime());
                DateTime now = new DateTime(formatter.format(date));
                DateTime end = new DateTime(formatter.format(dateConverter));
                int mins = Minutes.minutesBetween(now, end).getMinutes();
                System.out.println("Start time: " + now);
                System.out.println("End time: " + end);
                System.out.println("Difference in minutes:" + mins);
                if (mins <= 0) {
                    return false;
                } else {
                    return true;
                }
            }
        }
        destroyCon();
    } catch (SQLException e) {
        destroyCon();
        e.printStackTrace();
    }
    return false;
}

The output it gives me is:

Start time: 2012-07-16T20:26:50.000Z

End time: 2012-07-16T00:00:00.000Z

Difference in minutes:-1226

But the end time that is stored in the SQL database is: 2012-07-16 21:13:31

Some other info: I'm using Joda-time and the sql database and the java server is ran on the same server. Please tell me if you need more information.

Upvotes: 2

Views: 1919

Answers (3)

Mikko Maunu
Mikko Maunu

Reputation: 42114

As told in API documentation java.sql.Date does not have time component (all time related field are set to 0). It matches to SQL date.

Use java.sql.Timestamp instead.

Upvotes: 2

Affe
Affe

Reputation: 47994

java.sql.Date is defined to be a time-less date. When you use that type, it will chop the time information out of the date regardless of what is in the database. If you want both Date and Time information, you want to use getTimestamp() on the ResultSet, not getDate().

Upvotes: 4

Puggan Se
Puggan Se

Reputation: 5846

never used mysql with java,
but if java.sql.Date mysqlDate is a mysql DATE, insted of a DATETIME,
then that could be the problem

update
in oracle doc: java.sql.Date

"If the given ... value contains time information, the driver will set the time components ... to zero GMT."

Upvotes: 0

Related Questions