Java/MySQL - datetime problem

Question

Hi,

!!IMPORTANT!! Solution found, you need to use a sql.timestamp. Although there are 2 problems with the timestamp. First of all if you want to put an Date into an Timestamp you need to do: new Timestamp(date.getTime().getTime()); Kinda weird...

Also the months of a Timestamp start at 0, so january is 0. This means 23-02-2010 in Timestamp means 23-01-2010.

Thanks all.

!!IMPORTANT!!

I've got a question about Java with MySQL. I've made a table called "Reserveringen". This table got 5 columns as shown below. The 2nd and 3th column are both datetime types (made in Dreamcoder for mysql). But as you can see both ain't showing any time. Even when I get them from the database with Java, it shows the time is 23:00:00 if I'm correct.

  Id    vanaf       tot     klant_idmachine_id
    9   12/3/2010   1/14/2011   6   29
    8   1/3/2011    1/14/2011   6   27
    2   1/14/2011   6/20/2010   6   9
    3   1/14/2011   6/20/2010   6   11
    4   1/14/2011   6/20/2010   6   19
    5   1/14/2011   6/20/2010   6   21
    6   1/14/2011   6/20/2010   6   23
    7   1/14/2011   6/20/2010   6   25
    1   1/14/3911   1/14/3911   6   5

Tableinformation from mysql:

reserveringen  
Field  Null  Type  Key  Default  Extra  
Id _  NO _  int(10) _  PRI _  _  _  
vanaf _  YES _  datetime _  _  _  _  
tot _  YES _  datetime _  _  _  _  
klant_id _  YES _  int(10) _  MUL _  _  _  
machine_id _  YES _  int(10) _  MUL _  _  _  

Any solutions?

Answer 1

use timestamp then it 'll store date and time like this '2010-06-21 13:28:17'

java.util.Date today=new java.util.Date();

Timestamp currentTimestamp=new Timestamp(today.getTime());

PreparedStatement statement = dbConnection.prepareStatement("your query");

statement.setTimestamp(1, currentTimestamp);

Answer 2

Both 'vanaf' and 'tot' columns are defined using mysql's 'datetime'. This datatype is indeed used to store both date and time information and there is no way to ommit the time. Please use the vanilla mysql command line tool to verify the actual contents of your table.

If you only need the dates and no time at all: You could change the database schema to use 'date' columns. (See The DATETIME, DATE, and TIMESTAMP Types

Answer 3

java.sql.Date values do not have a time component http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Date.html (It does store milliseconds, but then normalize it to midnight.)

Use java.sql.Timestamp instead. That should solve your issue.