0

I am learning how to use ORM with Postgres in Java and I want to maintain in the table column date record that will hold exactly the following format:

2022-05-28T21:12:01.000Z

So I see two path:

  1. Create a Postgres Insert trigger
  2. Just care about date insertint in dao logic

Second is pretty intuitive for me, but what should I choose? Maybe flyway migration may be of use here?

EDIT:: I've been said in the comment that Postgres saves date in binary format, so I'll handle this. But automatic insertion of 'date' is still unclear to me

Thanks in advance!

floatfoo
  • 83
  • 2
  • 8
  • 1
    If the column type is `date` then the value will be not stored with any format. A format is presentation thing and should be handled at that layer – Adrian Klaver Sep 12 '22 at 15:00
  • Are you saying that you want to store this date constant in a table somewhere? Something else? – Tim Biegeleisen Sep 12 '22 at 15:00
  • @TimBiegeleisen No, just this format, where date goes first, then time, divided by letter T – floatfoo Sep 12 '22 at 15:01
  • 1
    @floatfoo Please read the first comment above. Dates are stored internally in Postgres as binary, they have no explicit format. – Tim Biegeleisen Sep 12 '22 at 15:02
  • 1
    If you want an INSERT `date` then you can create `DEFAULT current_date` to have a date automatically created on the column. If you want something else you will need to be more specific about what you are trying to achieve? – Adrian Klaver Sep 12 '22 at 18:12
  • @AdrianKlaver Well, I want to update column field, when I reupload file (doing insert or update). Still your approach works fine - I will have a default on creation and then manually update every time – floatfoo Sep 12 '22 at 18:59
  • 1
    Or you can use an `BEFORE UPDATE ON` trigger as illustrated in my answer to this question [Update a column](https://stackoverflow.com/questions/73483680/how-to-update-a-column-only-when-a-row-has-any-changes/73490431#73490431). – Adrian Klaver Sep 12 '22 at 19:31

1 Answers1

1

It is generally recommended to store date types in all DBs as date types rather than as strings. Because date types has a internal structures and you can shown this data to users in any formats. For example: 13 September 2022 or 13.09.2022 or 2022-09-13T12:24:45 and etc. You can use all date functions for this field such as: year > 2022 and etc. And all this does not depend on the fact that we use DB triggers or Java DAO.

But, triggers for that, for example, when there is an insert data into a table, we want to change another record in another table or in same table. We have two variants for doing this process:

  1. Doing this using PostgreSQL triggers.
  2. To do this in Backend (Java dao logic)

Option 1- not recommended. Because triggers wil be runs always, even when we will be execute any SQL command for importing data. Or in the Future may be we need different business logic such as: for some records we don't need changing another records.

Option 2 - recommended. Because all our business logics will be store in backend. And where ever we need to change another record before inserting, we will write the change code. And where not but we will not use the change code.

But sometimes it happens that before inserting data, you always (necessarily) need to change another record. In these circumstances, it is recommended to use triggers because suddenly you forget to write the change code somewhere. And triggers will always work no matter what

Ramin Faracov
  • 3,032
  • 1
  • 2
  • 8