1

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?

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
Buda Sergiu Flavius
  • 210
  • 1
  • 3
  • 13
  • 2
    You don't have a "joda.Datetime" in your database. The Joda datetime is a Java representation. What kind of database do you use, and how is the date formatted in it? For the JSON-part, if you use Jackson, you need to register the JavaTimeModule, as you can see here: https://github.com/FasterXML/jackson-modules-java8 – marstran Feb 05 '19 at 09:30
  • Yes , you are right , sorry. In my database i have bytea type for dates. I use PostgreSQL with DBeaver. But my questions is: Are this solution the right way of changing my time types? – Buda Sergiu Flavius Feb 05 '19 at 16:10

1 Answers1

0

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

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
  • 1
    "timestamp with time zone" doesn't really correspond to `OffsetDateTime` as far as I'm aware. The "with time zone" is a bit of a lie - it really just changes how values are ingested. It only represents an instant in time. – Jon Skeet Feb 06 '19 at 09:00
  • Thanks for the comment, @JonSkeet. My (limited) understanding agrees with yours. I read that storing an `OffsetDateTime` will make sure the correct point in time is stored, but not the offset. What you get when retrieving into `OffsetDateTime`, probably an `OffsetDateTime`in UTC(?) – Ole V.V. Feb 06 '19 at 09:06
  • 1
    Yes, I'd expect so. Or maybe it would convert it into the system offset. But it won't allow you to store and retrieve arbitrary `OffsetDateTime` values. It's basically `Instant` (although I don't know the precision). – Jon Skeet Feb 06 '19 at 09:08