In Postgres, Can You Set the Default Formatting for a Timestamp, by Session or Globally

in postgres, can you set the default formatting for a timestamp, by session or globally?

In postgres, you can change the default format mask for datetimes - using the set datestyle option; the available options can be found here (see 8.5.2. Date/Time Output).

Unfortunately, all the available options include the number of seconds - you will therefore need to reformat them either in the query or the application code (if applicable).

How to change default timestamp format in PostgreSQL

The date/time style can be selected by the user using the SET datestyle
command, the DateStyle parameter in the postgresql.conf
configuration file, or the PGDATESTYLE environment variable on the
server or client.

https://www.postgresql.org/docs/current/static/datatype-datetime.html#datatype-datetime-output

The available options (masks) are also listed at the same reference. But they may not provide for what you want exactly. Beyond that you have the to_char() function

The formatting function to_char (see Section 9.8) is also available as
a more flexible way to format date/time output.

https://www.postgresql.org/docs/current/static/functions-formatting.html

Postgresql - store only hours and minutes in column time type

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)

Setting a default date format on PostgreSQL

to_char doesn't have a default format, but you could use a variable for this to overcome this issue.

Just create your own to_char function, using a default setting:

CREATE OR REPLACE FUNCTION to_char(timestamptz) RETURNS text AS
$$
    SELECT to_char($1,'YYYY/MM/DD~HH24:Mi:SS'); -- here your default setting
$$
LANGUAGE SQL;

The "normal" to_char function will also work, you still have that option.

How to change datestyle in PostgreSQL?

yyyy-mm-dd is the recommended format for date field, its the ISO 8601 format.

You can change the format in the postgresql.conf file.

The document states

The date/time styles can be selected by the user using the SET
datestyle command, the DateStyle parameter in the postgresql.conf
configuration file, or the PGDATESTYLE environment variable on the
server or client. The formatting function to_char is
also available as a more flexible way to format date/time output.

Hope this helps!

---

Related Topics