CODEWITHSUNDEEP
postgresql
Kliver Max
Kliver Max

Reputation: 5299

How to change datestyle in PostgreSQL?

In postgres I have a table with date column. Now postgres allows me to write date in Y-m-d format. But I need date in d/m/Y format. How to change it?

When I do:

   show datestyle;

I get:

 "ISO, DMY"

And input date in table in this format 13/02/2009

But when I close and open table again I see this 2009-02-13. JDBC gives me date in this format too. What am I doing wrong?

Upvotes: 41

Views: 158496

Answers (6)

Kevin Scott
Kevin Scott

Reputation: 11

For data import you can use: SET datestyle = 'ISO, DMY';

and then your copy table import CSV statement

Upvotes: 1

NELSON RODRIGUEZ
NELSON RODRIGUEZ

Reputation: 369

I change this variable in postgresql.conf and it works!

enter image description here

Upvotes: 3

T Ismael Verdugo
T Ismael Verdugo

Reputation: 251

you also can use the command

set datestyle to [your new datestyle];

in the console of postgreSQL.

Upvotes: 25

Craig Ringer
Craig Ringer

Reputation: 324265

If at all possible, don't use DATESTYLE. It'll affect all code in your session, including things like stored procedures that might not be expecting it and might not have set an explicit overriding DATESTYLE in their definitions.

If you can, use to_char for date output, and to_timestamp for date input whenever you need to use any date formats that might be ambiguous, like D/M/Y. Use ISO dates the rest of the time.

Upvotes: 7

Ha Sh
Ha Sh

Reputation: 764

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!

Upvotes: 24

hd1
hd1

Reputation: 34657

Use the to_char function with your date as follows:

select to_char(date_column1, 'Mon/DD/YYYY');

Upvotes: 11

Related Questions