Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement.setDate() is cutting the time part

 
Gabriel Beres
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I'm passing a java.sql.Date to PreparedStatement.setDate() method.

void setDate(int parameterIndex, java.sql.Date x, Calendar cal)
throws SQLException;

I double checked that my java.sql.Date called x contains the date, and the time part as well.
However, when call commit(), in the database i only see the date part, the time part contains zeros.

Why is that?

Thanks
 
Hebert Coelho
Ranch Hand
Posts: 754
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How is your date column? It is set up to be date and time?
 
Gabriel Beres
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hebert Coelho wrote:How is your date column? It is set up to be date and time?


It is an SQL DATE not TIMESTAMP
 
Gabriel Beres
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What i realized is that if i call setTimestamp() instead, i get the time information, but also millisecound which i don't want
 
Paul Clapham
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So then setTimestamp is the method you should call. If you want the milliseconds set to zero, then do that before you call setTimestamp.
 
Gabriel Beres
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:So then setTimestamp is the method you should call. If you want the milliseconds set to zero, then do that before you call setTimestamp.


Setting to just zero is not suitable for me, i want to totally get rid of milliseconds. I mean i not only use this logic for INSERT but also for SELECT.
When i'm doing a select i don't want millisecond appear in my queries.
 
Paul Clapham
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When you say "queries" I assume you're talking about Java code which gets data from the database. In which case it's completely up to you how you display the dates. You can display them with or without milliseconds. That has nothing to do with how you store the data in the database.
 
Gabriel Beres
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:When you say "queries" I assume you're talking about Java code which gets data from the database. In which case it's completely up to you how you display the dates. You can display them with or without milliseconds. That has nothing to do with how you store the data in the database.


Yeah i know, but i'm binding the parameters with prepared statements. so i eather use setDate(), and have only date, or use setTimestamp(), and have milliseconds. I need something in between :)
 
Paul Clapham
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So far I pointed out that (a) you can set the milliseconds to zero before you insert the timestamp into the database, and (b) you can display the timestamp without milliseconds after you extract it from the database.

As far as I can see that takes care of all your objections. You're going to have to explain what your problem is if it isn't one of those two.
 
Gabriel Beres
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:So far I pointed out that (a) you can set the milliseconds to zero before you insert the timestamp into the database, and (b) you can display the timestamp without milliseconds after you extract it from the database.

As far as I can see that takes care of all your objections. You're going to have to explain what your problem is if it isn't one of those two.


Paul, we are not on the same page.

(a) i perfectly understand
(b) Do you understand, that i'm using prepared statement to make the query? If yes, it should be obvious, that i can only bind my query parameters with setDate() or setTimestamp(). The actual problem is, that if i bind with setDate(), then it will be yyyyMMdd. If i use setTimestamp it will be yyyyMMdd HHMMsss +miliseconds.

In that case i think i have no possibility to format it myself.
 
Paul Clapham
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Gabriel Beres wrote:Paul, we are not on the same page.

(b) Do you understand, that i'm using prepared statement to make the query? If yes, it should be obvious, that i can only bind my query parameters with setDate() or setTimestamp(). The actual problem is, that if i bind with setDate(), then it will be yyyyMMdd. If i use setTimestamp it will be yyyyMMdd HHMMsss +miliseconds.

In that case i think i have no possibility to format it myself.


I have said twice that you could set the milliseconds of a timestamp to zero before inserting that timestamp into the database. What you're saying doesn't make any sense. The database doesn't store anything in any format at all. It's only when you extract something from the database and display it that formatting takes place. So you're right, I don't know what page you're on. You're going to have to explain more carefully why you think formatting has anything to do with storing the data in the database.
 
Gabriel Beres
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
Gabriel Beres wrote:Paul, we are not on the same page.

(b) Do you understand, that i'm using prepared statement to make the query? If yes, it should be obvious, that i can only bind my query parameters with setDate() or setTimestamp(). The actual problem is, that if i bind with setDate(), then it will be yyyyMMdd. If i use setTimestamp it will be yyyyMMdd HHMMsss +miliseconds.

In that case i think i have no possibility to format it myself.


I have said twice that you could set the milliseconds of a timestamp to zero before inserting that timestamp into the database. What you're saying doesn't make any sense. The database doesn't store anything in any format at all. It's only when you extract something from the database and display it that formatting takes place. So you're right, I don't know what page you're on. You're going to have to explain more carefully why you think formatting has anything to do with storing the data in the database.


Paul, let's forget about saving into the database. As i already said, i understand that part.

"It's only when you extract something from the database and display it that formatting takes place"

I'm a visual type. Could you please show me how to format the parameter to yyyyMMdd HHmm (without milliseconds) by constructing the query using setTimestamp() method of prepared statement?



 
Paul Clapham
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Gabriel Beres wrote:I'm a visual type. Could you please show me how to format the parameter to yyyyMMdd HHmm (without milliseconds) by constructing the query using setTimestamp() method of prepared statement?


I'm sorry, I find that question too confusing to answer. Let me say a few things about it and maybe we can figure out some other question.

First, I don't understand what it means to "format a parameter". A parameter of what?

And second, if I were to format a parameter, whatever that means, I wouldn't do it by constructing a database query.

So as I said, I'm completely lost. Could you rephrase that question?
 
Gabriel Beres
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
Gabriel Beres wrote:I'm a visual type. Could you please show me how to format the parameter to yyyyMMdd HHmm (without milliseconds) by constructing the query using setTimestamp() method of prepared statement?


I'm sorry, I find that question too confusing to answer. Let me say a few things about it and maybe we can figure out some other question.

First, I don't understand what it means to "format a parameter". A parameter of what?

And second, if I were to format a parameter, whatever that means, I wouldn't do it by constructing a database query.

So as I said, I'm completely lost. Could you rephrase that question?


Sure.

I want to construct a JDBC query by using PreparedStatement. For example SELECT * FROM TABLE WHERE mydate = ?. Mydate is a date and i want to bind it as parameter. In order to do that, i must call setTimestamp() method on PreparedStatement. However if i do that, and execute my query, my parameter translates to yyyyMMdd HHmmss + miliseconds. My goal is to get rid of the milliseconds.

I hope it is more understandable now.
 
Paul Clapham
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Now I understand.

And I've said several times already that you should just set the milliseconds part of the timestamp to zero before you pass it to the setTimestamp() method. You would use a Calendar object to do that. Here's an outline of how to do it:

Create a Calendar object.
Set its time to your "date" value.
Set the milliseconds to zero.
Get the Calendar's time value into a java.util.Date object.
Make a java.sql.Timestamp from that.
 
Gabriel Beres
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:Now I understand.

And I've said several times already that you should just set the milliseconds part of the timestamp to zero before you pass it to the setTimestamp() method. You would use a Calendar object to do that. Here's an outline of how to do it:

Create a Calendar object.
Set its time to your "date" value.
Set the milliseconds to zero.
Get the Calendar's time value into a java.util.Date object.
Make a java.sql.Timestamp from that.


Thanks, i will try that. I know you said i should set it to zero, but my understanding was, that the milliseconds would be still there, but with zero value.
My apologize for the miss understanding.
 
Paul Clapham
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Gabriel Beres wrote:but my understanding was, that the milliseconds would be still there, but with zero value.


Well, yes. That's right, if you ask your timestamp what its milliseconds value is, it's going to say zero. It has to be something. It's just a number, after all. There isn't any concept of "not being there" for a number.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic