SimpleDateFormat return 12 hours ago time between 5.30 to 6.30

Question

We are using SimpleDateFormat to convert Date Time into time in Milliseconds bellow is the code we are using for conversion

below is the date-time format I am getting from my database

"2019-04-04 12:24:53.754787+00" 

Code:

  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-M-dd 
  hh:mm:ss");
    Date date = null;
    try {
        date = sdf.parse(dateTime);
    } catch (java.text.ParseException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    long timeInMilis = date.getTime();
    return timeInMilis;

It returns the correct timestamp in milliseconds. Except 5.30 pm to 6.30 pm it return 12 hours delay why it returns wrong timestamp? Or os their any other way to do the conversion?

Answer 1

First don’t store your datetime as a string in PostgreSQL and don’t retrieve a string like the one you are showing from the database. Store a proper date/time type; in your case probably timestamp with time zone. And instead of retrieving a string, fetch a corresponding Java datetime type. For example:

    PreparedStatement select = yourDatabaseConnection
            .prepareStatement("select ts from your_table where id = 4;");
    ResultSet rs = select.executeQuery();
    if (rs.next()) {
        OffsetDateTime dateTime = rs.getObject("ts", OffsetDateTime.class);
        long milliseconds = dateTime.toInstant().toEpochMilli();
        // Do something with milliseconds
    }

(I have not tested this snippet since I haven’t got PostgreSQL installed.)

If for one reason or another you cannot avoid getting the string:

    DateTimeFormatter formatter
            = DateTimeFormatter.ofPattern("uuuu-MM-dd HH:mm:ss.SSSSSSx");
    String stringFromDatabase = "2019-04-04 12:24:53.754787+00";
    OffsetDateTime dateTime
            = OffsetDateTime.parse(stringFromDatabase, formatter);
    long milliseconds = dateTime.toInstant().toEpochMilli();
    System.out.println(milliseconds);

This snippet I have tried running. The output is:

1554380693754

Since PostgreSQL stores microsecond precision and millisconds since the epoch obviously don’t, we have lost the three last decimals of the original value.

I am using and recommending java.time, the modern Java date and time API. The datetime classes that you tried to use — SimpleDateFormat and Date — are long outdated and were always poorly designed. So avoid those.

What went wrong in your code?

• Others have already pointed out that for parsing your string you need uppercase HH for hour of day from 00 through 23. Lowercase hh is for hour within AM or PM from 01 through 12. Since 12 AM means 00, parsing an hour of 12 gives the wrong result that you have observed, while other hour values work.
• The single M, on the other hand, doesn’t matter in this case, it works the same for parsing as MM (for formatting we need MM if we want two digits always, as in your example string).
• You neither parsed the fraction of second nor the UTC offset of +00. If you got an approximately correct result in spite of this, you’ve been struck by luck and should not count on this being the case on other computers or JVMs with other default settings.

Links

• PostgreSQL Date/Time Types.
• Using Java 8 Date and Time classes from PostgreSQL JDBC driver documentation.
• Oracle tutorial: Date Time explaining how to use java.time.
• Related question: Difference between java HH:mm and hh:mm on SimpleDateFormat

Answer 2

As Jon Skeet mentioned, the simple date format is also a bit off. I've changed -M- to -MM- and hh to HH. More about SimpleDateFormat can be found on https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html. Also, TimeZone might be affecting it.

You could also use Calendar to return long time.

Example:

    String date = "2019-04-04 12:24:53.754787+00";

    private long getTime(String _date) {

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Calendar cal = Calendar.getInstance();
        cal.setTimeZone(TimeZone.getTimeZone("Europe/Vienna"));
        try {
            cal.setTime(format.parse(_date));
        } catch (ParseException e) {
            e.printStackTrace();
        }

        // First .getTime() returns Date, second returns long in millis.
        return cal.getTime().getTime(); 
    }