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

Question

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?

[Like this in Oracle](http://stackoverflow.com/a/28211019/266304); but the title says you want to do it in Java, so which do you really mean? Also what data type is the column, and do you intend to convert the UTC value to a local time? If it's a date and you don't want to adjust it, you could use the same format mask with `to_date()`, but that wouldn't be strictly correct. — Alex Poole, Jan 10 '17 at 14:23
how is the code set up in `getFieldFromJson` method ? are you using Mongo java driver to handle the mapping ? You should try to store date as date type in mongo db too. So you dont have to convert and you can just read from MongoDB and save it to oracle. — s7vr, Jan 10 '17 at 15:06

MT0 · Accepted Answer · 2017-01-10T15:19:41.590

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')"
                        + ")";

score 1 · Answer 2 · answered Jan 10 '17 at 15:00

1

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();

answered Jan 10 '17 at 15:00

jguyet

75
8

1

Incorrect. That formatting pattern does match the input string. Ignores the `T` in the middle of a standard ISO 8601 formatted string. – Basil Bourque Jan 10 '17 at 19:42
@GyGy19 I agree, the date change if el use this code :( – TuGordoBello Jan 10 '17 at 20:04
Yes from Location ! – jguyet Feb 05 '19 at 13:57

score 0 · Answer 3 · edited May 23 '17 at 11:46

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?

Java SE 8 and SE 9 and later
- Built-in.
- Part of the standard Java API with a bundled implementation.
- Java 9 adds some minor features and fixes.
Java SE 6 and SE 7
- Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
Android
- The ThreeTenABP project adapts ThreeTen-Backport (mentioned above) for Android specifically.
- See How to use ThreeTenABP….

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.

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

3 Answers3

java.time

About java.time