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!

Improve this question
7
  • 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 Commented Sep 12, 2022 at 15:00
  • Are you saying that you want to store this date constant in a table somewhere? Something else? Commented Sep 12, 2022 at 15:00
  • 1
    @floatfoo Please read the first comment above. Dates are stored internally in Postgres as binary, they have no explicit format. Commented Sep 12, 2022 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? Commented Sep 12, 2022 at 18:12
  • 1
    Or you can use an BEFORE UPDATE ON trigger as illustrated in my answer to this question Update a column. Commented Sep 12, 2022 at 19:31

1 Answer 1

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.