2

From my database i retrieve value as :

20-DEC-17 10.15.53.000000000 AM

I want above java.sql.Timestamp to be converted to Instant time as :

2017-12-20T10:15:53Z

I tried following with current time stamp

Timestamp ts2 = new Timestamp(date1.getTime());
Date tradeDate1=new Date(ts2.getTime());
Instant tradeInstant = tradeDate1.toInstant();
System.out.println("Tade Instant"+ tradeInstant);
  • Actual time stamp: Fri Jun 22 16:07:35 IST 2018
  • What is prints in instan : Tade Instant2018-06-22T10:37:35.420Z

The hours/mins/seconds are updated which I dont want - is there a way this can be retained as is?

Idriss Neumann
  • 3,760
  • 2
  • 23
  • 32
Imran Moosani
  • 41
  • 1
  • 5
  • Please correct your typos. It's really hard to understand the paragraph after your code-snippet. – Christian Jun 22 '18 at 10:42
  • 2
    One is printing the time in local time zone (IST), the other is printing it in UTC (`0Z`), would seem to be correct – MadProgrammer Jun 22 '18 at 10:43
  • 1
    If your timestamp value is correct, then you don’t have an issue. The `Instant` denotes the same point in time. Any other conversion — like one that printed the same hour of day in UTC as in IST — would be incorrect. However, the best solution would be to retrieve the `Instant` directly from the database and avoid the `Timestamp` class altogether. It’s long outmoded. Are you using JDBC and a `ResultSet`? – Ole V.V. Jun 22 '18 at 11:02
  • Are you saying that `20-DEC-17 10.15.53.000000000 AM` is already in UTC so that converting it from IST to UTC is incorrect? – Ole V.V. Jun 22 '18 at 11:06
  • 1
    Thats right, the value retrieved from DB is already in UTC and its stored as timestamp - can i retrieve this value as result.getInstant to ensure i dont loose precision of seconds ? – Imran Moosani Jun 22 '18 at 11:19
  • 1
    @MadProgrammer understood - however assuming that timestamp value would be coming from database (which is already UTC) - i want to ensure the hours mins and seconds are not updated .. unfortunately I want this of type Instant cause my ultimate POJO accepts instant object. – Imran Moosani Jun 22 '18 at 11:20
  • @ImranMoosani Also understand that the values been printed are using an internal formatter used to provide "human readable" results, the classes them selves are just a container for the number of milliseconds since the Unix Epoch (at least in the case of `Timestamp`). How you "format" the information is more important, this is why the API has `DateTimeFormatter` – MadProgrammer Jun 22 '18 at 11:50

3 Answers3

3

I am assuming that you are using at least Java 8 and at least JDBC 4.2. I am further assuming that the timestamp doesn’t have time zone or offset information in the database, but is to be understood as a timestamp in UTC (which is a recommended practice). In this case I would consider it safest to add the information about UTC offset explicitly in Java:

PreparedStatement yourPreparedStatement 
            = yourConnection.prepareStatement("select trade_timestamp from your_table");
ResultSet rs = yourPreparedStatement.executeQuery();
while (rs.next()) {
    LocalDateTime tradeDateTime = rs.getObject(1, LocalDateTime.class);
    Instant tradeInstant = tradeDateTime.atOffset(ZoneOffset.UTC).toInstant();
    System.out.println("Trade Instant: " + tradeInstant);
}

Note that the code avoids the outdated Timestamp class completely. A LocalDateTime is a date and time of day without time zone or offset. If your database datatype had been timestamp with time zone, you could have passed either Instant.class or OffsetDateTime.class to rs.getObject and have got an Instant or an OffsetDateTime back. JDBC 4.2 only specifies support for OffsetDateTime, but many drivers support Instant too. Obviously with Instant you need no further conversion. With OffsetDateTime do

    Instant tradeInstant = tradeDateTime.toInstant();

Depending on your database and its capabilities it is also possible that you can set UTC as offset/time zone on the database session so you can get the correct instant even from timestamp without time zone.

Discussion: Arvind Kumar Avinash in a comment recommends that one should rely only on the types officially supported by JDBC 4.2, that is, LocalDateTime and OffsetDateTime for our purposes. The types are mentioned at the bottom of the article Why do we need a new date and time library? on Oracle’s web site, there’s a link at the bottom. Arvind Kumar Avinash further refers us to PSQLException: Can't infer the SQL type to use for an instance of java.time.Instant, also linked to at the bottom. Since comments are fragile, I wanted to include the essence here in the answer.

What went wrong in your code?

It seems your database session understood the timestamp as a date and time in your local time zone (IST, I assume it’s for India Standard Time (other interpretations exist)). According to Mark Rotteveel’s informative comment this behaviour is required by JDBC, but it doesn’t agree with your need when the value is in UTC. Therefore it gave you the wrong point in time, though it looked right when you printed it. The conversion in itself was correct.

Links

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
  • thanks for detail explaination. I believe I am having a better understanding of how this Instant and LocalDateTime are behaving. I will try on same lines and hopefully this should now work as expected. Thanks again !! – Imran Moosani Jun 22 '18 at 12:20
  • 1
    _"It seems your database session incorrectly understood the timestamp as a date and time in your local time zone"_ that behavior is actually required by JDBC (for timestamp without timezone). – Mark Rotteveel Jun 22 '18 at 13:26
  • *If your database datatype had been `timestamp with timezone`, you could have passed `Instant.class` to rs.getObject and have got an `Instant` immediately and would not have hat to convert.* - This is wrong. The only supported `java.time` types are `LocalDate`, `LocalTime`, `LocalDateTime`, `OffsetTime`, and `OffsetDateTime`. Also, some DBs do not support `LocalTime` and `OffsetTime`. – Arvind Kumar Avinash Jun 04 '21 at 18:02
  • **For future visitors:** This is the correct answer except for the problem that I have pointed out in the above comment. Check [this](https://stackoverflow.com/a/67752047/10819573) and [this](https://stackoverflow.com/a/67505173/10819573) for the list of `java.time` supported types in JDBC. These two answers solve different problems and one should go through both of them to understand scenarios for using `LocalDate/LocalDateTime` and `OffsetDateTime`. – Arvind Kumar Avinash Jun 05 '21 at 19:10
  • @OleV.V. - Better but I still recommend that one should rely only on the types given at the bottom of [this page](https://www.oracle.com/technical-resources/articles/java/jf14-date-time.html). A driver can be implemented to support `Instant` but I would still stick to the spec even while using that driver. – Arvind Kumar Avinash Jun 05 '21 at 20:04
  • 1
    I like portable code myself, so clearly see your point, @ArvindKumarAvinash. On the other hand `Instant` makes such great sense here. For many purposes it’s what you want. So I find it fine to offer both possibilities. Please let your comment stand, your opinion is valuable. – Ole V.V. Jun 05 '21 at 20:12
  • Thanks, @OleV.V. for the nice comment. I liked your spirit. **Just for the information to the future visitors**: [PSQLException: Can't infer the SQL type to use for an instance of java.time.Instant. #1095](https://github.com/tpolecat/doobie/issues/1095) – Arvind Kumar Avinash Jun 05 '21 at 20:18
0

Building from the comment about not using SimpleDateFormat, I have moved to DateTimeFormatter:

Date today = new Date();

DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss") // same format
        .withLocale(Locale.UK) // UK locale
        .withZone(ZoneId.systemDefault());

String output = dateTimeFormatter.format( today.toInstant() );

System.out.println( output );

Running gives you:

2018-06-22T14:14:26
chocksaway
  • 870
  • 1
  • 10
  • 21
  • A clear improvement over your first answer, IMHO. A tip: `Timestamp` too furnishes [a `toInstant` method](https://docs.oracle.com/javase/9/docs/api/java/sql/Timestamp.html#toInstant--). With that I don’t think you need the rest. – Ole V.V. Jun 22 '18 at 19:44
-2

I have created a SimpleDateFormat, which only prints "up to seconds":

/* simple date format */   
DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");

/* get toInstant() and convert to date */
Date myDate = Date.from(today.toInstant());

/* myDate formatted using DATE_FORMAT */
String formattedDate = DATE_FORMAT.format(myDate);

System.out.println(formattedDate);
chocksaway
  • 870
  • 1
  • 10
  • 21
  • 5
    Please don’t teach the young ones to use the long outdated and notoriously troublesome `SimpleDateFormat` class. At least not as the first option. And not without any reservation. Today we have so much better in [`java.time`, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/) and its `DateTimeFormatter`. – Ole V.V. Jun 22 '18 at 11:37
  • Please refer to my new answer, which uses DateTimeFormatter. – chocksaway Jun 22 '18 at 13:17
  • Thanks, I may be hard to please, though, I have not understood how it answers the question. – Ole V.V. Jun 22 '18 at 15:22