Setting a default date format on PostgreSQL

Question

Good afternoon,

Many of you may already know that it is possible, for instance, in Oracle set the default date format used by formatting functions (ie to_char), just like this:

ALTER SESSION SET NLS_DATE_FORMAT='SYYYY/MM/DD~HH24:Mi:SS'

My question is: is there any equivalence for this on PostgreSQL. I have been searching for an answer without success although I have found some references to the PostgreSQL locale settings (http://www.postgresql.org/docs/7.4/static/charset.html). Anyhow, I have been unable to figure out an answer.

Any suggestions? Thank you in advance and best wishes for this holidays!

Are you really using version 7.4? That's EOL for a long time... Use a newer version, at least version 8.3 at this moment, all older versions are EOL. And use the matching documentation. — Frank Heikens
– Frank Heikens, Commented Dec 23, 2011 at 12:42
Actually I am working with 10g but the code I am working with was written ages ago. What I am doing is migrating that code to PostgreSQL, so the question arised. Anyway, your suggestion is a good reminder for all of us. Thanks! — Sergio
– Sergio, Commented Dec 23, 2011 at 12:47

user594138 · Accepted Answer · 2011-12-23 12:34:59Z

9

It's possibly that SET DATESTYLE does what you want, at least for day, month, year portions of the date.

Search here for DateStyle.

answered Dec 23, 2011 at 12:34

user594138

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

1 Comment

Frank Heikens Over a year ago

That will only give you 4 fixed options, not a user defined one.

Frank Heikens · Accepted Answer · 2011-12-23 12:59:23Z

5

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.

edited Dec 23, 2011 at 12:59

answered Dec 23, 2011 at 12:45

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

1 Comment

Sergio Over a year ago

Looking good!That's the most accurate way to proceed. Thanks!

Collectives™ on Stack Overflow

Setting a default date format on PostgreSQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related