CODEWITHSUNDEEP
node.jspostgresqldatetimestamp
user3242466
user3242466

Reputation: 474

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

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.

Upvotes: 0

Views: 300

Answers (2)

Jasen
Jasen

Reputation: 12412

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

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246798

There is no such setting.

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

Upvotes: 0

Related Questions