CODEWITHSUNDEEP
javaoracle-databasemongodbtimestampisodate
TuGordoBello
TuGordoBello

Reputation: 4509

How to transform ISO DATE to Timestamp in Java or SQL?

I am using Mongo and Oracle 12c at the same time.

For MongoDB I am using ISO DATE (but like String) to persist date. Like this:

{
            "_id" : null,
            "fields" : {
                "Status" : "UnderInvestigation",
                "Assignee" : "",
                "CreationDate" : "2016-12-14T00:00:00Z", //ISO DATE
                "CaseId" : "8165021",
                "RunningMode" : "STS",
                "CloserDueDate" : ""
            },
            "domain" : {},
            "arrays" : {}
        }

I want use Timestamp format type in Oracle for a column with the same name, so the idea is transform that date to timestamp.

I don't know how to do that. I have the follow code but it doesn't work.

final String query = "INSERT INTO " + TABLE_APPLICATION + " (CreationDate) VALUES (TO_DATE(?,'YYYY-MM-DD HH24:MI:SS'))" ;
PreparedStatement ps = getPreparedStatement();
((OraclePreparedStatement) ps).setString(1, getValueFromJson(JSON, 'fields.CreationDate'));

But I got:

java.sql.SQLDataException: ORA-01861: el literal no coincide con la cadena de formato

What's the problem?

Upvotes: 0

Views: 3793

Answers (3)

Basil Bourque
Basil Bourque

Reputation: 338574

java.time

To do this in Java rather than SQL, use the java.time classes.

The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction).

Your input string is in standard ISO 8601 format. The java.time classes use these standard formats by default. So, no need to specify a formatting pattern.

Instant instant = Instant.parse( "2016-12-14T00:00:00Z" );

If your JDBC driver supports JDBC 4.2 or later, you can pass/fetch java.time objects via the PreparedStatement::setObject/ResultSet::getObject methods.

myPreparedStatement.setObject( 1 , instant );

Or perhaps you need to specify the SQLType.

myPreparedStatement.setObject( 1 , instant , JDBCType.TIMESTAMP_WITH_TIMEZONE );

If your JDBC driver does not specify the java.time types, fall back to using the old java.sql types. Call new methods added to the old classes for conversion.

java.sql.Timestamp ts = java.sql.Timestamp.from( instant );
myPreparedStatement.setTimestamp( 1 , ts );

Your Question really is a duplicate of many others. Search Stack Overflow for these class names to get more examples and discussion.

About java.time

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.

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.

Upvotes: 0

MT0
MT0

Reputation: 167972

Oracle solution:

SELECT TO_TIMESTAMP_TZ(
         '2016-12-14T00:00:00Z',
         'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM'
       )
FROM   DUAL;

So your code should be:

final String query = "INSERT INTO " + TABLE_APPLICATION + " ("
                        + "CreationDate"
                        + ") VALUES ("
                        + "TO_TIMESTAMP_TZ( ?, 'YYYY-MM-DD\"T\"HH24:MI:SSTZH:TZM')"
                        + ")";

Upvotes: 2

jguyet
jguyet

Reputation: 75

I SimpleDateFormat parse your field Date and getTime() you return millisTime exact.

//String base = "YYYY-MM-ddTkk:mm:ssZ";
//"date": "2016-11-22T16:59:01+01:00"

String timeform = "YYYY-MM-dd-kk-mm-ss-Z";
Date datec = new Date();

//si null ont crée une nouvelle date
if (!json.isNull("date"))
{
    DateFormat format = new SimpleDateFormat(timeform, Locale.FRANCE);
    try {
        datec = format.parse(json.getString("date"));
    } catch (ParseException e){}
}
datec.getTime();

Upvotes: 1

Related Questions