BeanListHandler handles db column Timestamp as millseconds (epoch)

Question

I have database column which is Timestamp. The source system (we have no control), inserts data in the format yyyy-MM-dd HH:mm:ss.SSS into this column. I use a BeanListHandler in dbutils to map the row which includes this column to a bean. All other datatypes works as expected. I use java.sql.TimeStamp for mapping this column but i am getting this in epoch time i.e. in milliseconds. But i want this in the date format as stored in the database i.e. yyyy-MM-dd HH:mm:ss.SSS. I tried making the bean field as java,util.Date but no luck. Can someone help me here

Answer 1

DB dates/times and Java dates/times do not have a format. They are stored as millisecs (sometimes nanosecs) since 1 Jan 1970, so-called epoch dates.

The DB client presents them as human readable strings.

For the Timestamp to be readable as a String, format it as follows:

String formattedTimeStamp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(timestampFromDb);

On a related note, java.sql.Timestamp is not well designed and use is frowned upon. Try java.time.LocalDateTime or java.time.OffsetDateTime depending on your needs.

DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");

String formatDateTime = dateTimeFromDb.format(formatter);