How to pre-format a date in Postgres automatically for all SELECT queries

Question

I'm working with Postgres TIMESTAMP data types and want to change the display format anytime a SELECT query is called.

I currently have it working as shown below:

SELECT to_char(my_time_col, 'YYYY/MM/DD HH24:MI:SS') as time_col FROM my_table

Is there a way to have it formatted as 2018/05/31 13:00:00 for all queries either via some predefined function, or trigger (or something that I don't know about) without having to manually format the DATETIME fields for each individual query? In other words — I just want to type this:

SELECT my_time_col FROM my_table

Thanks in advance.

Have you tried to look for it? There's plenty in the official documentation: postgresql.org/docs/9.6/static/functions-formatting.html — vitaly-t
– vitaly-t, Commented Feb 3, 2018 at 3:08
Your SQL client formats the timestamp value. Check the configuration of the SQL client you are usnig — user330315
– user330315, Commented Feb 3, 2018 at 6:31
that depends if the client is using the binary or the ascii interface. — Jasen
– Jasen, Commented Feb 3, 2018 at 11:15

Jasen · Accepted Answer · 2018-02-03 11:08:52Z

1

run this sql:

SET datestyle to 'ISO, DMY';

so long as you don't store fractional seconds you should get the result you want.

this setting is temporary but can be made permanent by prefixing it with

ALTER USERusername

or

ALTER DATABASEdatabasename

answered Feb 3, 2018 at 11:08

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

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

Comments

Laurenz Albe · Accepted Answer · 2018-02-03 05:39:57Z

0

There is no such setting.

The best thing is to let the application take care of the formatting.

answered Feb 3, 2018 at 5:39

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

How to pre-format a date in Postgres automatically for all SELECT queries

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related