Convert from joda.Datetime to time.LocalDateTime is the right way?

Question

I need timestamp format for my dates in database. For now i have joda.Datetime in database , but also in my restApi application.

I tried to create a new column , and converted the existing joda.Datetime in the other column time.LocalDateTime. Also I replaced in all code joda.DateTime with time.LocalDateTime.

It works, but when i make a get call in postman, i received a json like:

{
seconds: x1,
minutes: x2,
hours: x3,
days: x4,
........
}

I think i need a convertor, to show the timestamp as "dd-mm-yy hh-mm-ss"

I want to have timestamp format in database to be able to execute SQL standard operation and named queries on time.

In my database I have bytea type for dates. I use PostgreSQL with DBeaver.

Is this the right way, or you could recommend me another option?

Answer 1

Is this the right way, or you could recommend me another option?

Without experience with PostgreSQL I should say that bytea is the wrong datatype for your dates or timestamps. timestamp with time zone is good and supports SQL operations and queries. It further has the advantage that you can store OffsetDateTime (perhaps even Instant, I am not sure) directly, so you avoid formatting your timestamp for storing it. That’ll be recommended.

For a time stamp to be a time stamp date and time of day is not enough. Date and time of day will be interpreted differently in different time zones (and is even ambiguous in the fall when summer time ends and the clocks are turned backward). As far as I have understood timestamp with time zone will make sure that time stamps are stored in UTC, so will be unambiguous points in time. In Java the Instant class represents a point in time independently of time zone, so is good for timestamps. Some JDBC drivers allow you to store an Instant directly into a timestamp with time zone column, others require you to convert to OffsetDateTime first. In the latter case use

OffsetDateTime dateTimeForDatabase = yourInstant.atOffset(ZoneOffset.UTC);

Edit: Please note that the with time zone bit is a bit of a lie, as @Jon Skeet points out in a comment. The database doesn’t store a time zone or offset, it only makes sure that dates and times are stored in UTC for removing ambiguity about the point in time.

Link: Date/Time Types in the PostgreSQL docs