0

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

Sijo Kurien
  • 95
  • 2
  • 10

1 Answers1

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);
John Williams
  • 4,252
  • 2
  • 9
  • 18
  • I found the below to be useful but it takes the UTC timr as the default. My application wil be used accross timezone, so i need a way to pick the local time @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm a z") private Date date; something like timezone="LOCAL" – Sijo Kurien Mar 28 '23 at 06:42
  • 1
    Are there Java-based servers running in the different time zones? If so try this https://stackoverflow.com/questions/67929633/formatting-zoneddatetime-according-to-locale. If the users are in different zones to the server then stick to UTC on server and do locale formatting on clients. If the client is browser based you can pick up the locale from header and apply on server. – John Williams Mar 28 '23 at 07:40
  • ok. That makes sense. The application is a distributed one and we do not want to enforce the clients or the other apps using this to format. So question is whether there is a way to set the timezone to the local time or the JVM timezone – Sijo Kurien Mar 28 '23 at 08:01
  • Please accept my answer. I see it has an upvote. – John Williams Mar 30 '23 at 09:40