Java/MySQL - datetime problem

Question

!!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?

Does Java show the same day? If it shows the previous day and 23:00, it would seem like a timezone issue. — Lauri Lehtinen
– Lauri Lehtinen, Commented Jun 21, 2010 at 7:15
You need to check the column type in mysql and posting some of your java code might help — user180100
– user180100, Commented Jun 21, 2010 at 7:16
@RC: The java code is not the problem here, I know this for sure if I just look into the debug. @Lauri: I'm gonne check if it's the same day. Moment. — Julian
– Julian, Commented Jun 21, 2010 at 7:23
@Lauri: If I use: reserveringen.get(8).getVanaf().toGMTString(); < this is why 23:00:00 I think. It indeed returns 2 Dec 2010 23:00:00 GMT for the 12/3/2010. How should I fix the timezone issue? And isn't it weird that my MySQL isn't showing me any time? — Julian
– Julian, Commented Jun 21, 2010 at 7:26
Posted the tableinformation now. As you see it is a datetime at both the columns. Still not showing the time as I show in the copy of my table. — Julian
– Julian, Commented Jun 21, 2010 at 7:30

Rahil Wazir · Accepted Answer · 2014-11-18 12:51:01Z

3

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);

edited Nov 18, 2014 at 12:51

Rahil Wazir

10.1k11 gold badges46 silver badges66 bronze badges

answered Jun 21, 2010 at 7:44

Paul

1,4003 gold badges12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Julian Over a year ago

So instead of using a date/calendar to put the data in my database I should use a timestamp?

Julian Over a year ago

Because I'm doing this with hibernate. My class of Reservering(en) got 2 Date's in it. I also tried it with calenders but still my database (even if I look into the tables itself) isn't showing any time in the cells.

Paul Over a year ago

Yes. If u use Timestamp then the data will be stored like this '2010-06-21 13:28:17' while getting the value u can use statement.getString("columnname");

Julian Over a year ago

Hmmm.. for the second thing you say I use hibernate, so I dont need a statement or anything. Although I can fix this within hibernate. I'm now using the vanilla mysql. And I see that all my times are stored with 00:00:00. So I'm going to try this timestamp thing. Thanks

Julian Over a year ago

I never used the timestamp type. But looks like I can't set it to another datetime that I want to?

|

towe75 · Accepted Answer · 2010-06-21 07:49:09Z

1

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

answered Jun 21, 2010 at 7:49

towe75

1,46010 silver badges9 bronze badges

2 Comments

Julian Over a year ago

I need both date and time so thats why I'm using a DATETIME. How can I use the vanilla mysql commands?

Julian Over a year ago

Nvm, got the vanilla sql working. My times are all 00:00:00, so I'm going to try timestamp variable to insert it, instead of date/calendar.

Ashish Patil · Accepted Answer · 2010-06-21 11:46:10Z

0

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.

answered Jun 21, 2010 at 11:46

Ashish Patil

8088 silver badges17 bronze badges

Collectives™ on Stack Overflow

Java/MySQL - datetime problem

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related