I know how to retrieve timestamp with milliseconds:
to_char(systimestamp ,'YYYY-MM-DD HH24:MI:SS,FF9')
Can anyone please advise, is the timestamp data type sufficient to store the date with milliseconds or can I use varchar2? I am trying to insert this value from Java.
Yes, TIMESTAMP allows precision down to nanoseconds if you want it.
If you only need milliseconds, you just want a TIMESTAMP(3) column.
Use a java.sql.Timestamp (which again goes down to nanoseconds, if you need it to) on the Java side. Note that you should avoid doing your to_char conversion if possible - perform any string conversions you need client-side; fetch data as a timestamp, and send it as a timestamp.
Use smart objects, not dumb strings. Use java.time for nanosecond resolution.
myResultSet.getObject( … , LocalDateTime.class )
The Answer by Jon Skeet is correct, but now outdated. The java.sql.Timestamp class is supplanted by the java.time classes such as Instant and LocalDateTime.
The java.time classes have a resolution of nanoseconds, more than enough for your milliseconds.
Oracle database seems to have a TIMESTAMP type that is equivalent to the SQL-standard type TIMESTAMP WITHOUT TIME ZONE. That means it lacks any concept of time zone or offset-from-UTC. For such a column, use LocalDateTime in Java as it too lacks any concept of zone/offset.
LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;
For storing into a field of type similar to SQL-standard TIMESTAMP WITH TIME ZONE where do have respect for zone/offset, use Instant:
Instant instant = Instant.now() ; // Capture current moment as fine as nanoseconds. In practice, we get microseconds in Java 9, but only milliseconds in Java 8.
As of JDBC 4.2 and later, we can directly exchange java.time objects with the database via PreparedStatement.setObject and ResultSet.getObject. So use these smart objects rather than mere strings to communicate date-time values.
The java.time classes generate strings in standard ISO 8601 formats. Just call toString.
String output = instant.toString() ;
String output = ldt.toString() ;
The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.
The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.
Where to obtain the java.time classes?
The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.
