Milliseconds are not converted properly from java new Date() function to the postgres Timestamp with time Zone

Question

The code is legacy code.

The current date was set in a POJO field by using new Date() function(java.util.Date). And it is getting saved into the postgres database with the type= Timestamp with time zone

But some time I got the Date/Time saved up to 3 digit milliseconds and some up to 2 digit milliseconds.

For example:

Wed Jul 01 13:06:10 IST 2020 is converted or saved in the database like "2020-07-01 13:06:10.**178**+05:30" and some time "2020-07-01 13:06:10.**18**+05:30"

Do anyone has an idea about the inaccuracy of the millisecond precision?

Maybe, *sometimes* this third digit is actually `0` and gets omitted? Not sure, but possible... **But:** Don't use `java.util.Date` for datetime operations anymore, there's `java.time` which is much less troublesome! — deHaar, Jul 01 '20 at 07:47
@a_horse_with_no_name really `LocalDateTime` for a timestamp with an offset? Wouldn't `OffsetDateTime` be a better choice? — deHaar, Jul 01 '20 at 08:01
Unrelated to your problem, but: Postgres 9.4 is [no longer supported](https://www.postgresql.org/support/versioning/) you should plan an upgrade as soon as possible. — , Jul 01 '20 at 09:08

Arvind Kumar Avinash · Accepted Answer · 2020-07-01T10:08:35.967

I recommend you switch from the outdated and error-prone java.util date-time API to the rich set of modern date-time API.

Define a formatter object:

DateTimeFormatter.ofPattern("u-M-d H:m:s.SSSXXX")

…and use it for parsing both kinds of date-time strings.

import java.time.OffsetDateTime;
import java.time.format.DateTimeFormatter;

public class Main {
    public static void main(final String[] args) {
        // Define the formatter
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("u-M-d H:m:s.SSSXXX");

        // Test date-time strings
        String[] strDateArr = { "2020-07-01 13:06:10.178+05:30", "2020-07-01 13:06:10.18+05:30" };

        OffsetDateTime odt;
        for (String strDate : strDateArr) {
            // Parse a date-time string from the array of test date-time strings
            odt = OffsetDateTime.parse(strDate, formatter);
            System.out.println(odt);
        }
    }
}

Output:

2020-07-01T13:06:10.178+05:30
2020-07-01T13:06:10.180+05:30

Note that you can use OffsetDateTime directly with the PreparedStatement and the ResultSet objects without converting it to any other date-time object e.g.

Setting the OffsetDateTime object as a parameter to the PreparedStatement object:

pst.setObject(index, odt);

Getting the OffsetDateTime object from the Resultset object:

OffsetDateTime odt = rs.getObject(index, OffsetDateTime.class);

Update

Using legacy API:

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class Main {
    public static void main(final String[] args) throws ParseException {
        // Test date-time strings
        String[] strDateArr = { "2020-07-01 13:06:10.178+05:30", "2020-07-01 13:06:10.18+05:30" };

        // Define the format
        final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSXXX");

        Date date;
        for (String strDate : strDateArr) {
            // Parse a date-time string from the array of test date-time strings
            date = sdf.parse(strDate);
            System.out.println(date);
        }
    }
}

Output:

Wed Jul 01 08:36:10 BST 2020
Wed Jul 01 08:36:10 BST 2020

Backport of the Java SE 8 date-time classes to Java SE 6 and 7:

Check ThreeTen-Backport and How to use ThreeTenABP

I was analysing what will be the impact, if i'm storing the date as milliseconds in db(Postgres). While fetching based on the given milliseconds what if i use >= < operator. Will it be costly? — Pearl, Oct 21 '21 at 04:21

Milliseconds are not converted properly from java new Date() function to the postgres Timestamp with time Zone

1 Answers1

Update