Convert java.time.Instant to java.sql.Timestamp without Zone offset

java date time timestamp java.time.instant
62,967

Solution 1

I changed my computer’s time zone to Europe/Bucharest for an experiment. This is UTC + 2 hours like your time zone.

Now when I copy your code I get a result similar to yours:

    Instant now = Instant.now();
    System.out.println(now); // prints 2017-03-14T06:16:32.621Z
    Timestamp current = Timestamp.from(now);
    System.out.println(current); // 2017-03-14 08:16:32.621

Output is given in comments. However, I go on:

    DateFormat df = DateFormat.getDateTimeInstance();
    df.setTimeZone(TimeZone.getTimeZone("UTC"));
    // the following prints: Timestamp in UTC: 14-03-2017 06:16:32
    System.out.println("Timestamp in UTC: " + df.format(current));

Now you can see that the Timestamp really agrees with the Instant we started out from (only the milliseconds are not printed, but I trust they are in there too). So you have done everything correctly and only got confused because when we printed the Timestamp we were implicitly calling its toString method, and this method in turn grabs the computer’s time zone setting and displays the time in this zone. Only because of this, the displays are different.

The other thing you attempted, using LocalDateTime, appears to work, but it really does not give you what you want:

    LocalDateTime ldt = LocalDateTime.ofInstant(Instant.now(), ZoneOffset.UTC);
    System.out.println(ldt); // 2017-03-14T06:16:32.819
    current = Timestamp.valueOf(ldt);
    System.out.println(current); // 2017-03-14 06:16:32.819
    System.out.println("Timestamp in UTC: " + df.format(current)); // 14-03-2017 04:16:32

Now when we print the Timestamp using our UTC DateFormat, we can see that it is 2 hours too early, 04:16:32 UTC when the Instant is 06:16:32 UTC. So this method is deceiving, it looks like it’s working, but it doesn’t.

This shows the trouble that lead to the design of the Java 8 date and time classes to replace the old ones. So the real and good solution to your problem would probably be to get yourself a JDBC 4.2 driver that can accept an Instant object readily so you can avoid converting to Timestamp altogether. I don’t know if that’s available for you just yet, but I’m convinced it will be.

Solution 2

Wrong classes to use

LocalDateTime ldt = LocalDateTime.ofInstant(Instnant.now(), ZoneOffset.UTC);
Timestamp current = Timestamp.valueOf(ldt);

Two problems with that code.

Firstly, never mix the modern java.time classes (LocalDateTime here) with the terrible old legacy date-time classes (java.sql.Timestamp here). The java.time framework supplants entirely the terrible old classes, as of the adoption of JSR 310. You need never use Timestamp again: As of JDBC 4.2 we can directly exchange java.time objects with the database.

The other problem, is that the LocalDateTime class cannot, by definition, represent a moment. It purposely lacks a time zone or offset-from-UTC. Use LocalDateTime only when you mean a date with time-of-day everywhere or anywhere, in other words, any/all of many more moments across a range of about 26-27 hours (the current extremes of time zones around the globe).

Do not use LocalDateTime when you mean a specific moment, a specific point on the timeline. Instead use:

Then I try to create Timestamp object

Don’t.

Never use java.sql.Timestamp. Replaced by java.time.Instant. Read on for more info.

Table of date-time types in Java (both modern and legacy) and in standard SQL.

Current moment

To capture the current moment in UTC use either of these:

Instant instant = Instant.now() ;

…or…

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC );

The both represent the very same thing, a moment in UTC.

Database

Here is some example SQL and the Java code to pass the current moment into the database.

The example uses the H2 Database Engine, built in Java.

sql = "INSERT INTO event_ ( name_ , when_ ) " + "VALUES ( ? , ? ) ;";
try ( PreparedStatement preparedStatement = conn.prepareStatement( sql ) ; ) {
    String name = "whatever";
    OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC );

    preparedStatement.setString( 1 , name );
    preparedStatement.setObject( 2 , odt );
    preparedStatement.executeUpdate();
}

Here is a complete example app using that code.

package com.basilbourque.example;

import java.sql.*;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.util.UUID;

public class MomentIntoDatabase {

    public static void main ( String[] args ) {
        MomentIntoDatabase app = new MomentIntoDatabase();
        app.doIt();
    }

    private void doIt ( ) {
        try {
            Class.forName( "org.h2.Driver" );
        } catch ( ClassNotFoundException e ) {
            e.printStackTrace();
        }

        try (
                Connection conn = DriverManager.getConnection( "jdbc:h2:mem:moment_into_db_example_" ) ;
                Statement stmt = conn.createStatement() ;
        ) {
            String sql = "CREATE TABLE event_ (\n" +
                    "  id_ UUID DEFAULT random_uuid() PRIMARY KEY ,\n" +
                    "  name_ VARCHAR NOT NULL ,\n" +
                    "  when_ TIMESTAMP WITH TIME ZONE NOT NULL\n" +
                    ") ; ";
            System.out.println( sql );
            stmt.execute( sql );

            // Insert row.
            sql = "INSERT INTO event_ ( name_ , when_ ) " + "VALUES ( ? , ? ) ;";
            try ( PreparedStatement preparedStatement = conn.prepareStatement( sql ) ; ) {
                String name = "whatever";
                OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC );

                preparedStatement.setString( 1 , name );
                preparedStatement.setObject( 2 , odt );
                preparedStatement.executeUpdate();
            }

            // Query all.
            sql = "SELECT * FROM event_ ;";
            try ( ResultSet rs = stmt.executeQuery( sql ) ; ) {
                while ( rs.next() ) {
                    //Retrieve by column name
                    UUID id = ( UUID ) rs.getObject( "id_" );  // Cast the `Object` object to UUID if your driver does not support JDBC 4.2 and its ability to pass the expected return type for type-safety.
                    String name = rs.getString( "name_" );
                    OffsetDateTime odt = rs.getObject( "when_" , OffsetDateTime.class );

                    //Display values
                    System.out.println( "id: " + id + " | name: " + name + " | when: " + odt );
                }
            }
        } catch ( SQLException e ) {
            e.printStackTrace();
        }
    }
}

Parsing string

Regarding a related comment by Melnyk, here is another example based on the example code above. Rather than capturing the current moment, this code parses a string.

The input string lacks any indicator of time zone or offset-from-UTC. So we parse as a LocalDateTime, keeping in mind that this does not represent a moment, is not a point on the timeline.

String input = "22.11.2018 00:00:00";
DateTimeFormatter f = DateTimeFormatter.ofPattern( "dd.MM.uuuu HH:mm:ss" );
LocalDateTime ldt = LocalDateTime.parse( input , f );

ldt.toString(): 2018-11-22T00:00

But we have been informed the string was meant to represent a moment in UTC, but the sender screwed up and failed to include that information (such as a Z or +00:00 on the end to mean UTC). So we can apply an offset-from-UTC of zero hours-minutes-seconds to determine an actual moment, a specific point on the timeline. The result as a OffsetDateTime object.

OffsetDateTime odt = ldt.atOffset( ZoneOffset.UTC );

odt.toString(): 2018-11-22T00:00Z

The Z on the end means UTC and is pronounced “Zulu”. Defined in ISO 8601 standard.

Now that we have a moment in hand, we can send it to the database in a column of SQL-standard type TIMESTAMP WITH TIME ZONE.

preparedStatement.setObject( 2 , odt );

When then retrieve that stored value.

 OffsetDateTime odt = rs.getObject( "when_" , OffsetDateTime.class );

2018-11-22T00:00Z

Here is the complete for this example app.

package com.basilbourque.example;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;
import java.util.UUID;

public class MomentIntoDatabase {

    public static void main ( String[] args ) {
        MomentIntoDatabase app = new MomentIntoDatabase();
        app.doIt();
    }

    private void doIt ( ) {
        try {
            Class.forName( "org.h2.Driver" );
        } catch ( ClassNotFoundException e ) {
            e.printStackTrace();
        }

        try (
                Connection conn = DriverManager.getConnection( "jdbc:h2:mem:moment_into_db_example_" ) ;
                Statement stmt = conn.createStatement() ;
        ) {
            String sql = "CREATE TABLE event_ (\n" +
                    "  id_ UUID DEFAULT random_uuid() PRIMARY KEY ,\n" +
                    "  name_ VARCHAR NOT NULL ,\n" +
                    "  when_ TIMESTAMP WITH TIME ZONE NOT NULL\n" +
                    ") ; ";
            System.out.println( sql );
            stmt.execute( sql );

            // Insert row.
            sql = "INSERT INTO event_ ( name_ , when_ ) " + "VALUES ( ? , ? ) ;";
            try ( PreparedStatement preparedStatement = conn.prepareStatement( sql ) ; ) {
                String name = "whatever";
                String input = "22.11.2018 00:00:00";
                DateTimeFormatter f = DateTimeFormatter.ofPattern( "dd.MM.uuuu HH:mm:ss" );
                LocalDateTime ldt = LocalDateTime.parse( input , f );
                System.out.println( "ldt.toString(): " + ldt );
                OffsetDateTime odt = ldt.atOffset( ZoneOffset.UTC );
                System.out.println( "odt.toString(): " + odt );

                preparedStatement.setString( 1 , name );
                preparedStatement.setObject( 2 , odt );
                preparedStatement.executeUpdate();
            }

            // Query all.
            sql = "SELECT * FROM event_ ;";
            try ( ResultSet rs = stmt.executeQuery( sql ) ; ) {
                while ( rs.next() ) {
                    //Retrieve by column name
                    UUID id = ( UUID ) rs.getObject( "id_" );  // Cast the `Object` object to UUID if your driver does not support JDBC 4.2 and its ability to pass the expected return type for type-safety.
                    String name = rs.getString( "name_" );
                    OffsetDateTime odt = rs.getObject( "when_" , OffsetDateTime.class );

                    //Display values
                    System.out.println( "id: " + id + " | name: " + name + " | when: " + odt );
                }
            }
        } catch ( SQLException e ) {
            e.printStackTrace();
        }
    }
}

Converting

If you must interoperate with old code not yet updated for java.time, you can convert back-and-forth. Look to new methods to…/from… added to the old classes.

To get a legacy java.sql.Timestamp object, call Timestamp.from( Instant ). To get an Instant from our OffsetDateTime object seen above, simply call OffsetDateTime::toInstant.

java.sql.Timestamp ts = Timestamp.from( odt.toInstant() ) ;

Going the other direction.

OffsetDateTime odt = OffsetDateTime.ofInstant( ts.toInstant() , ZoneOffset.UTC ) ;

If using the ThreeTen-Backport library for Java 6 & 7 projects, look to the DateTimeUtils class for the to…/from… conversion methods.

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.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

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. Hibernate 5 & JPA 2.2 support java.time.

Where to obtain the java.time classes?

Table of which java.time library to use with which version of Java or Android

Solution 3

Instant always gives time in UTC whereas Timestamp gives time in your local zone. So if you are not concern about any specific time zone, you can use Instant. It's also a good practice to save records in UTC, so that your application remains unaffected in case it is deployed in any other timezone.

Solution 4

If you want the current timestamp why not use the following function, I have used this in various projects and works perfectly:

public static Timestamp getTimeStamp()
{
    // Calendar information
    Calendar calendar       = Calendar.getInstance();
    java.util.Date now      = calendar.getTime();
    Timestamp dbStamp       = new Timestamp(now.getTime());
    return dbStamp;
}

Example:

System.out.println( getTimeStamp() );

Output: 2017-03-13 15:01:34.027

EDIT

Using Java 8 LocalDateTime:

public static Timestamp getTimeStamp()
{
    return Timestamp.valueOf(LocalDateTime.now());
}
Share:
62,967

Related videos on Youtube

Aleydin Karaimin
Author by

Aleydin Karaimin

Updated on July 09, 2022

Comments

  • Aleydin Karaimin
    Aleydin Karaimin almost 2 years

    In the application I am developing, I need to convert java.time.Instant object to java.sql.Timestamp. When I create Instant object like:

    Instant now = Instant.now();

    I receive something like 2017-03-13T14:28:59.970Z. And when I try to create Timestamp object like this:

    Timestamp current = Timestamp.from(now);

    I receive something like 2017-03-13T16:28:59.970Z. The same result but with an additional 2 hour delay. Can someone explain why this is happening and provide me with an answer to fix this problem without this delay?

    When I created like this:

    LocalDateTime ldt = LocalDateTime.ofInstant(Instant.now(), ZoneOffset.UTC);
Timestamp current = Timestamp.valueOf(ldt);

    Everything works well, but I try to avoid conversions. Is there a way to do this by only using Instant object?

    • Jon Skeet
      Jon Skeet about 7 years
      Are you sure both of them really show Z? Can you provide a minimal reproducible example to demonstrate this without a debugger?
    • Aleydin Karaimin
      Aleydin Karaimin about 7 years
      For now I can't provide you, but I am almost sure that the record in the database would be with applied zone offset. The second example is tested, and the record is as I expected.
    • Aleydin Karaimin
      Aleydin Karaimin about 7 years
      @JonSkeet . Sorry I am wrong, no Z appeared in Timestamp current = Timestamp.from(now);
    • Jon Skeet
      Jon Skeet about 7 years
      Right - and is your local time zone two hours ahead of UTC at the moment? If so, the two values represent the same point in time.
    • Aleydin Karaimin
      Aleydin Karaimin about 7 years
      Yeah, I want 0 offset, as the second example, without conversion (from instant to LocalDateTime or ZonedDateTime) if is possible.
    • Jon Skeet
      Jon Skeet about 7 years
      Are you just looking at the toString() representation? It's not clear why it matters.
    • Aleydin Karaimin
      Aleydin Karaimin about 7 years
      mmm, I don't think so. You are right that this which eclipse show me is the toString() mthod of the object. I saw the structure of the Timestamp instance and the zone offset value was set to specific value
    • Ole V.V.
      Ole V.V. about 7 years
      You are doing everything correctly. A Timestamp doesn’t have a time zone association, but it seems its toString method uses your computer’s default time zone.
    • Ole V.V.
      Ole V.V. about 7 years
      On the other hand, I think the result you get from Timestamp.valueOf(ldt) differs from what you want. The documentation of the method says: “The provided LocalDateTime is interpreted as the local date-time in the local time zone.” So this does not give you the time in UTC. In other words, it gives you a time that is different from the Instant,now() you started out from.
    • Aleydin Karaimin
      Aleydin Karaimin about 7 years
      @OleV.V. Yeah, but during the documentation, LocalDateTime does not hold any timezone information. You can build it using offset from standard UTC of your specific zone. (+2 is mine). My instance of LocalDateTime is build with +0 offset, so the Timestamp copy it and transform it it;s structure. But my qestion is why doen't do it with my Instant object, which represent nano seconds from 1970 in UTC. Does my Instant holds my timezone offset, or Timestamp add it.
    • Ole V.V.
      Ole V.V. about 7 years
      Neither Instant nor Timestamp holds any time zone offset. Timestamp.toString() picks up your computer's default time zone and uses it for display, which is what confuses you. So even though rhey hold the same number of milliseconds inUTC, they don't display the same.
    • Aleydin Karaimin
      Aleydin Karaimin about 7 years
      @OleV.V. Yeah, but when I create it like the first example I shared and put it into the database, the offset is still there and the record is with +2 hours offset of the desired. Using the second example the result is correct
    • Ole V.V.
      Ole V.V. about 7 years
  • Aleydin Karaimin
    Aleydin Karaimin about 7 years
    thank you but I want to use the java 8 way, because of it's immutable classes
  • DazstaV3
    DazstaV3 about 7 years
    @AleydinKaraimin I added a different way that gives the same output using LocalDateTime. How about this?
  • Aleydin Karaimin
    Aleydin Karaimin about 7 years
    Yeah, I mentioned it in my post that using this way is fine, but, I want to use only instant, and want to learn the reason why this happens. Maybe the creation of timestamp add offset, when accept instant
  • Basil Bourque
    Basil Bourque over 5 years
    Never use java.sql.Timestamp class. It was entirely supplanted by the modern java.time classes. No need to mix the terrible legacy classes with the modern. Timestamp was replaced by Instant(always in UTC by definition) or OffsetDateTime (set to UTC).
  • Basil Bourque
    Basil Bourque over 5 years
    Detail: A JDBC 4.2 compliant driver might use an Instant, but is required to use an OffsetDateTime. To convert: OffsetDateTime odt = myInstant.atOffset( ZoneOffset.UTC ) ;
  • Taras Melnyk
    Taras Melnyk over 5 years
    I use java.sql.Timestamp class to store date into DB.
  • Basil Bourque
    Basil Bourque over 5 years
    Timestamp was replaced by OffsetDateTime as of JDBC 4.2. Search Stack Overflow. This has been covered many many times already. myPreparedStatement.setObject( … , Instant.now().atOffset( ZoneOffset.UTC ) ) ;
  • Taras Melnyk
    Taras Melnyk over 5 years
    I have: String dateStr = "22.11.2018 00:00:00"; OffsetDateTime offsetDateTime = date.atOffset(ZoneOffset.UTC); -> 2018-11-22T00:00Z SQl Server DB stores offsetDateTime as 2018-11-21 22:00:00.000 instead of 2018-11-22 00:00:00.000
  • Basil Bourque
    Basil Bourque over 5 years
    I added my own Answer with example code for you to follow. That example uses the H2 Database Engine, though I expect you to see the same results if using Microsoft SQL Server.
  • Basil Bourque
    Basil Bourque over 4 years
    @Dogbert_1825027 (A) As for the need to interoperate with old code not yet updated to java.time, good point. Your comment prompted me to add a Converting section near the bottom with examples of code going back-and-forth between the legacy and modern classes. (B) As for my insistence on avoiding the legacy classes, I stand by my position. The legacy date-time classes really are that bad, flawed in many ways, terribly difficult to work with as can be seen by the profusion of questions here on Stack Overflow. Adding ThreeTen-Backport to an old Java 6/7 project is well worth the bother.

Recents

Why Is PNG file with Drop Shadow in Flutter Web App Grainy?
How to troubleshoot crashes detected by Google Play Store for Flutter app
Cupertino DateTime picker interfering with scroll behaviour
Why does awk -F work for most letters, but not for the letter "t"?
Flutter change focus color and icon color but not works
How to print and connect to printer using flutter desktop via usb?
Critical issues have been reported with the following SDK versions: com.google.android.gms:play-services-safetynet:17.0.0
Flutter Dart - get localized country name from country code
navigatorState is null when using pushNamed Navigation onGenerateRoutes of GetMaterialPage
Android Sdk manager not found- Flutter doctor error
Flutter Laravel Push Notification without using any third party like(firebase,onesignal..etc)
How to change the color of ElevatedButton when entering text in TextField

Related

Sort date and time values
Convert java.util.date default format to Timestamp in Java
Java Date cut off time information
Calculate days, hours and minutes between two instants
Calculating the time difference between two time stamp object - java
Best Way to store time (Only Time and Not date) in database
java - Getting current date and time based on the locale (country)
Java parse oracle timestamp to date.util.date
Convert Date to time stamp in java
Find the difference between two times in java