Postgresql - store only hours and minutes in column time type

Question

There is some option to store only hours and minutes in the database (No seconds)?

I know that I can use the function to_char in my query, but this is not the solution for me.

I am using/editing a system written in PHP that I cannot edit (I mean PHP), it is something like WYSIWYG, and it injects the values ​​directly into the forms (Talking about editing existing), and the form should be in HH:MM and as long as the injection does not cause an application error , saving (Even without editing) causes an error due to validation because it incject HH:MM:SS because that is the column type.

So far, to work around the problem, I have created text columns, but it creates other complications and I was wondering if it could be done in a different way

Answer 1

Assuming that

1. You can't modify your PHP application.
2. You wish to keep a column of type time for other purposes.
3. There are no select * anywhere that expect the column layout of that table to remain unchanged.
4. You only need this one piece of the application to select, insert and update that table as if HH:MM was its default output format.
5. The application won't mind that it'll read text instead of time type data, as long as it's HH:MM.

You can do a column swap and handle incoming data in a trigger, also maintaining integrity between the actual time-type column and its text representation column. That way anything that doesn't rely on the data being type `time will get HH:MM format from that particular table.

drop table if exists test_70092210;
create table test_70092210 as select now()::time time_column;
select time_column from test_70092210;
--   time_column
-------------------
-- 12:06:23.890971
--(1 row)
alter table test_70092210 rename column time_column to time_column_raw;--column swap
alter table test_70092210 add column time_column text;--column swap
update test_70092210 set time_column=to_char(time_column_raw,'HH24:MI');
select time_column from test_70092210;
-- time_column
---------------
-- 12:06
--(1 row)

While updates and inserts can be handled using triggers:

CREATE or replace FUNCTION test_70092210_time_column_insert_handler() RETURNS trigger AS $test_70092210_time_column_insert_handler$
    BEGIN
        NEW.time_column_raw=coalesce(   NEW.time_column_raw::time,
                                        NEW.time_column::time);
        NEW.time_column=coalesce(   to_char(NEW.time_column_raw::time,'HH24:MI'),
                                    to_char(NEW.time_column::time,'HH24:MI'));
        RETURN NEW;
    END;
$test_70092210_time_column_insert_handler$ LANGUAGE plpgsql;
create or replace trigger test_70092210_time_column_insert_handler_trigger before insert on test_70092210
for each row execute function test_70092210_time_column_insert_handler();

CREATE or replace FUNCTION test_70092210_time_column_update_handler() RETURNS trigger AS $test_70092210_time_column_update_handler$
    BEGIN
        NEW.time_column_raw=case 
                                when NEW.time_column_raw<>OLD.time_column_raw
                                    then NEW.time_column_raw::time
                                else    
                                    NEW.time_column::time
                            end;
        NEW.time_column=case 
                            when NEW.time_column_raw<>OLD.time_column_raw
                                then to_char(NEW.time_column_raw::time,'HH24:MI')
                            else
                                to_char(NEW.time_column::time,'HH24:MI')
                        end;
        RETURN NEW;
    END;
$test_70092210_time_column_update_handler$ LANGUAGE plpgsql;
create or replace trigger test_70092210_time_column_update_handler_trigger before update on test_70092210
for each row execute function test_70092210_time_column_update_handler();

insert into test_70092210 select now()-'01:30'::time;
select time_column from test_70092210;
-- time_column
---------------
-- 12:06
-- 10:37
--(2 rows)
update test_70092210 set time_column='16:23' where ctid in (select min(ctid) from test_70092210);
select time_column from test_70092210;
-- time_column
---------------
-- 10:37
-- 16:23
--(2 rows)

Having the cookie and eating it too:

select * from test_70092210;
-- time_column_raw | time_column
-------------------+-------------
-- 10:37:19.91891  | 10:37
-- 16:23:00        | 16:23
--(2 rows)