In postgres, how can I create a custom type that is just a timestamp truncated to minutes
I have a column "event_datetime" that is in many tables and I need it truncated to a minute. I don't want to create triggers in all places to truncate it when I insert or update, or I need to assign date_trunc when comparing. Is it possible to have a custom type that is essentially a timestamp?
source to share
You can create a new base types in the low-level language such as the C . You probably don't want to do this.
In PostgreSQL, timestamp data types accept extra precision . But setting it to zero removes fractional seconds, not seconds.
I think the best you can do is
- create a domain with a check constraint, and
- require all inserts and updates to call a function.
The code create domain
looks like this.
create domain ts as timestamp
constraint no_seconds check (VALUE = date_trunc('minute', VALUE));
create table ts_test (
test_ts ts primary key
);
-- Doesn't work . . .
insert into ts_test values (current_timestamp);
ERROR: value for domain ts violates check constraint "no_seconds"
-- But this does.
insert into ts_test values (date_trunc('minute', current_timestamp));
It also allows comparisons without being called date_trunc()
.
To avoid writing date_trunc()
to every INSERT and UPDATE statement,
- undo "insert" and "update" permissions in the base table,
- write a function to do truncations and insertions, and
- calling a function instead of using the base table directly.
But this means that you will need to call your function on every insert and update date_trunc()
statement, instead of calling it on every insert and update statement. It is not clear if you are ready to do this.
source to share