CODEWITHSUNDEEP
javaoracle-databasedatetime
user918477
user918477

Reputation:

insert current time into table

                java.util.Date date = new java.util.Date();
                java.sql.Date today = new java.sql.Date(date.getTime()); //2012-03-23
                java.sql.Time time = new java.sql.Time(date.getTime()); //02:32:46
                PreparedStatement pst = null;
String queryString = "INSERT INTO PR_VISITOR(PRISONER_ID,VISITOR_NAME,FATHER_NAME,DOV,IN_TIME) VALUES (?,?,?,?,?)";
                        pst = connect.prepareStatement(queryString);
                        pst.setString(1, pr_id);
                        pst.setString(2, visit);
                        pst.setString(3, father);
                        pst.setDate(4, today);
                        pst.setTime(5, time);
                        int officerQuery = pst.executeUpdate();
                        if (officerQuery == 1) {
                            response.sendRedirect("/FYP3.4/prisonAdmin/visitor_out.jsp");
                            JOptionPane.showMessageDialog(null, "Visitor information registered !!", "Visitor Information", JOptionPane.INFORMATION_MESSAGE);
                        } else {
                            JOptionPane.showMessageDialog(null, "Unable to Add information !!", "Visitor Information", JOptionPane.ERROR_MESSAGE);
                        }

By using the above code i'm trying to insert the current date and time into table,which have the separate columns. When i'm executing the above query then it insert the todays date in the time IN_TIME field too.

EDIT

DATATYPE OF IN_TIME and DOV are DATE .

Need Help.. !!

Upvotes: 0

Views: 2762

Answers (5)

Touchstone
Touchstone

Reputation: 5962

Follow this,it will help both in java and oracle

create table datetime(date_time timestamp);

insert into datetime values (sysdate);

To get date: select to_char(date_time,'DD-MON-YY') from datetime;

eg:12-JUL-12

To get month: select to_char(date_time,'mm') from datetime;

eg:7

To get time: select to_char(date_time,'HH24:MI:SS') from datetime;

eg:23:56:15

cheers!!

Upvotes: 0

jacknad
jacknad

Reputation: 13739

Use SimpleDateFormat. This is one way I have used it:

Date now = Calendar.getInstance().getTime());
DateFormat df = new SimpleDateFormat("yyyyMMdd");
String date = df.format(now);
DateFormat tf = new SimpleDateFormat("HHmmss");
String time = tf.format(now);

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231651

A DATE column in an Oracle database will always store both a day (i.e. March 22, 2012) and a time to the second (i.e. 3:30:00 PM). A java.sql.Date and a java.sql.Time store the day and time as well but to the millisecond.

It doesn't really make sense to have separate columns in Oracle for the day and for the time but particularly not where both columns are declared as DATE data types. It would be much more conventional to use a single column declared as a DATE. If you really wanted to, you could truncate the day so that it represents midnight on the current day and then store the time component as, say, an INTERVAL DAY TO SECOND. But that would generally add a fair amount of complexity to the system for very little gain.

Upvotes: 1

user800014
user800014

Reputation:

Since DOV and IN_TIME is date you don't need to separate date and hour. The type date in Oracle holds date and time. I suggest you change your table to have just one date column.

To insert the current time you can use the Oracle's sysdate function:

INSERT INTO PR_VISITOR(PRISONER_ID,VISITOR_NAME,FATHER_NAME,DATETIME_COLUMN) VALUES (?,?,?,?,SYSDATE)

To format your output of the date value you can use the SimpleDateFormat class in Java or to_char in Oracle.

Upvotes: 1

JohnnyK
JohnnyK

Reputation: 1102

You're much better off using oracle's 'systimestamp'. The reason being, if you're java code is running in one timezone, and oracle lives in another. Forcing your own Time object, could cause problems.

Do you really need separate fields for this? I would think just having a timestamp would be enough.

Upvotes: 0

Related Questions