?
Solved

jsp and mysql set time , date

Posted on 2005-04-19
16
Medium Priority
?
259 Views
Last Modified: 2010-04-01
Hello, friends,

I want to set a date/time field in a db table.The format will be : HH:mm yyyy-MM-dd .

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

in the sql statements:

insertStmt = sqlConn.prepareStatement( "UPDATE fileTable SET submitTime=?");
insertStmt.setDate( 1, new java.sql.Date(currentDate.getTime() ));
insertStmt.executeUpdate();

in the table, I found the time format is :yyyy-MM-dd. How could I get  HH:mm yyyy-MM-dd ?

Another related question is:  if I want to sort the table based on the time, can I implement like this :

selectStmt=sqlConn.prepareStatement("SELECT fileName FROM fileTable ORDER BY submitTime" );

Thanks for yor reply.


0
Comment
Question by:lilyyan
  • 9
  • 7
16 Comments
 
LVL 92

Expert Comment

by:objects
ID: 13819607
what type is the database column?
0
 

Author Comment

by:lilyyan
ID: 13820918
Hi, thanks for your reply.
The field: submitTime datetype is date.
0
 
LVL 92

Accepted Solution

by:
objects earned 400 total points
ID: 13820949
The date type does not store time details, you'll need to use a a datetime or timestamp for that

the order by is correct to sort by date
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lilyyan
ID: 13821001
Hello, thanks for the prompt response.

Now I changed the datatype to datetime.

If I use:  insertStmt.setTime( 1, new java.sql.Time(currentDate.getTime() ));

the result is in milliseconds ?
 
0
 
LVL 92

Expert Comment

by:objects
ID: 13821021
Use Timestamp instead of Time
0
 

Author Comment

by:lilyyan
ID: 13821124
Well, maybe those two ( Time or Timestamp) don't have much difference ?

From the PreparedStatement API:

setTime(int parameterIndex, Time x)
//  this will set the designated parameter to the given java.sql.Time value :

// the second paremter has to be Time datatype , it seems that I need use :

the method Time valueOf(String s)  

Another one :

setTimestamp(int parameterIndex, Timestamp x)  // simliar as above
//this will set the designated parameter to the given java.sql.Timestamp value.  it seems that I need use :

Timestamp valueOf(String s)  

??The question is : the format of the String s, how to get it ?


Since I need sort the table by the time order, I'm guessing that the vaule of  submitTime filed has to be milliseconds ?
0
 
LVL 92

Expert Comment

by:objects
ID: 13821137
You can use SimpleDateFormat class to format the date however you require it, that is independant of the database.
0
 

Author Comment

by:lilyyan
ID: 13821157
Is there an example for the format of Timestamp? Thanks
0
 

Author Comment

by:lilyyan
ID: 13821233
When I use :

insertStmt.setTimestamp(1 ,  new java.sql.Timestamp(currentDate.getTime() )); // when the submitTime datatype
                                                                                                                    //is timestamp
insertStmt.executeUpdate();

The value of submitTime will be 2005-04-19 22:31:47 . It's correct

If I  use :
insertStmt.setTime( 1, new java.sql.Time(currentDate.getTime() )); //when the submitTime datatype is datetime
insertStmt.executeUpdate();

The value of submitTime will be 0000-00-00 00:00:00. It's wrong.

But I didn't find setDatetime method in PreparedStatement API ?
0
 
LVL 92

Expert Comment

by:objects
ID: 13821255
use setTimestamp() for a datetime
0
 

Author Comment

by:lilyyan
ID: 13821307
haven't tried the above method

from PreparedStatement API
setTimestamp(int parameterIndex, Timestamp x)  // the second prameter has to be a Timestamp, but suppose submitTime datatype is datetime, how to solve this ? Thanks

0
 

Author Comment

by:lilyyan
ID: 13821321
Just tried : insertStmt.setTimestamp( 1, new java.sql.Time(currentDate.getTime() )); // it's failed :(
0
 
LVL 92

Expert Comment

by:objects
ID: 13821353
should be

 insertStmt.setTimestamp( 1, new java.sql.Timestamp(currentDate.getTime() ));
0
 

Author Comment

by:lilyyan
ID: 13821369
Yeah, when submitTime datatype is timestamp. if it's datetime, how to achieve ?

If I  use :
insertStmt.setTime( 1, new java.sql.Time(currentDate.getTime() )); //when the submitTime datatype is datetime
insertStmt.executeUpdate();

The value of submitTime will be 0000-00-00 00:00:00. It's wrong.

But I didn't find setDatetime method in PreparedStatement API ?
0
 

Author Comment

by:lilyyan
ID: 13821380
Many thanks for your attention. The problem is basically solved. I will close the question tomorrow.
0
 
LVL 92

Assisted Solution

by:objects
objects earned 400 total points
ID: 13821590
> Yeah, when submitTime datatype is timestamp. if it's datetime, how to achieve ?

From a java perspective I think you can treat them both the same
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Tech giants such as Amazon and Google have sold Alexa and Echo to such an extent that they have become household names. And soon they are expected to be used by commoners in their homes, ordering takeout, picking out a song, answering trivia questio…
Exchange administrators are always vigilant about Exchange crashes and disasters that are possible any time. It is quite essential to identify the symptoms of a possible Exchange issue and be prepared with a proper recovery plan. There are multiple…
Integration Management Part 2
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses
Course of the Month15 days, 19 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question