Thread: date format
I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is still rendered in the format (y,m,d) . How can I change this behaviour? regards garry
Garry Saddington <garry@schoolteachers.co.uk> writes: > I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is still > rendered in the format (y,m,d) . How can I change this behaviour? ISO means y-m-d... -- Jorge Godoy <jgodoy@gmail.com>
On 04/03/2007 12:58, Jorge Godoy wrote:
> Garry Saddington <garry@schoolteachers.co.uk> writes:
>
>> I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is still
>> rendered in the format (y,m,d) . How can I change this behaviour?
>
> ISO means y-m-d...
The default postgresql.conf that is installed has
datestyle = iso,mdy
though commented out - on a new installation I uncomment this line as a
matter of course and change it to "iso,dmy" and it works fine.
Are you sure that PostgreSQL is presenting the dates in the wrong
format? Could there be another layer that's swapping the month and year
around? - try SELECTing some date values from psql and see what you get.
Ray.
---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------
On Sunday 04 March 2007 15:28, Raymond O'Donnell wrote: > On 04/03/2007 12:58, Jorge Godoy wrote: > > Garry Saddington <garry@schoolteachers.co.uk> writes: > >> I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is > >> still rendered in the format (y,m,d) . How can I change this behaviour? > > > > ISO means y-m-d... > > The default postgresql.conf that is installed has > > datestyle = iso,mdy > > though commented out - on a new installation I uncomment this line as a > matter of course and change it to "iso,dmy" and it works fine. > > Are you sure that PostgreSQL is presenting the dates in the wrong > format? Could there be another layer that's swapping the month and year > around? - try SELECTing some date values from psql and see what you get. > I tried pgAdmin3 on windows to run 'select now()' and it still does not return the correct datestyle. Regards Garry
On 04/03/2007 16:16, Garry Saddington wrote: > I tried pgAdmin3 on windows to run 'select now()' and it still does not > return the correct datestyle. Probably a silly question, but did you remember to restart the server after changing the datestyle setting? Ray. --------------------------------------------------------------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie ---------------------------------------------------------------
On Sunday 04 March 2007 16:16, Raymond O'Donnell wrote: > On 04/03/2007 16:16, Garry Saddington wrote: > > I tried pgAdmin3 on windows to run 'select now()' and it still does not > > return the correct datestyle. > > Probably a silly question, but did you remember to restart the server > after changing the datestyle setting? yes regards garry > Ray. > > --------------------------------------------------------------- > Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland > rod@iol.ie > --------------------------------------------------------------- > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings
On Sun, Mar 04, 2007 at 05:05:44PM +0000, Garry Saddington wrote:
> On Sunday 04 March 2007 16:16, Raymond O'Donnell wrote:
> > On 04/03/2007 16:16, Garry Saddington wrote:
> > > I tried pgAdmin3 on windows to run 'select now()' and it still does not
> > > return the correct datestyle.
> >
> > Probably a silly question, but did you remember to restart the server
> > after changing the datestyle setting?
> yes
> regards
Can you show us your exact output so we don't have to guess? I get
this:
# set datestyle=iso,dmy;
SET
# select now();
now
-------------------------------
2007-03-04 18:07:54.626267+01
(1 row)
Which is precisely what I expect.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
Attachment
Garry Saddington wrote: > I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is still > rendered in the format (y,m,d) . How can I change this behaviour? > Did you restart PostgreSQL after making the change? You can also set it on the fly per session. Joshua D. Drake > regards > garry > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
On Sunday 04 March 2007 17:09, Martijn van Oosterhout wrote: > On Sun, Mar 04, 2007 at 05:05:44PM +0000, Garry Saddington wrote: > > On Sunday 04 March 2007 16:16, Raymond O'Donnell wrote: > > > On 04/03/2007 16:16, Garry Saddington wrote: > > > > I tried pgAdmin3 on windows to run 'select now()' and it still does > > > > not return the correct datestyle. > > > > > > Probably a silly question, but did you remember to restart the server > > > after changing the datestyle setting? > > > > yes > > regards > > Can you show us your exact output so we don't have to guess? I get > this: > > # set datestyle=iso,dmy; > SET > # select now(); > now > ------------------------------- > 2007-03-04 18:07:54.626267+01 > (1 row) > > Which is precisely what I expect. Which is precisely what I get, but I want 04-03-2007. regards Garry
On 04/03/2007 18:56, Garry Saddington wrote: >> # select now(); >> now >> ------------------------------- >> 2007-03-04 18:07:54.626267+01 >> (1 row) >> >> Which is precisely what I expect. > Which is precisely what I get, but I want 04-03-2007. Ahh! Then you need to use to_char(). The datestyle setting in postgresql.conf differentiates *only* between European and American date formats - i.e. the relative order of day and month. It doesn't set the exact output format. Ray. --------------------------------------------------------------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie ---------------------------------------------------------------
On 04/03/2007 19:26, Raymond O'Donnell wrote: > Ahh! Then you need to use to_char(). The datestyle setting in http://www.postgresql.org/docs/8.2/static/functions-formatting.html --------------------------------------------------------------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie ---------------------------------------------------------------
Garry Saddington schrieb: > I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is still > rendered in the format (y,m,d) . How can I change this behaviour? > regards > garry > See my answer on the Zope list. The style however influences the way dates are parsed by default, so you need to take care when you feed dates from your application to database. You should also use to_date() to make it explicit. Regards Tino
Am Sonntag, 4. März 2007 17:16 schrieb Raymond O'Donnell: > Probably a silly question, but did you remember to restart the server > after changing the datestyle setting? You don't need to do that. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > Am Sonntag, 4. März 2007 17:16 schrieb Raymond O'Donnell: > >> Probably a silly question, but did you remember to restart the server >> after changing the datestyle setting? >> > > You don't need to do that. > I didn't really explain my point here. You an use datestyle from a session, or you can reload the server. You do not need to *restart*. However, he didn't mention if he had done any of that. He just said he set it which doesn't really mean anything if he didn't change it from the setting or reload the server. J
On 05/03/2007 12:16, Joshua D. Drake wrote: > I didn't really explain my point here. You an use datestyle from a > session, or you can reload the server. You do not need to *restart*. Yes, that's what I was getting at as well - I was in a hurry at the time and "restart" was the wrong word to use. Ray. --------------------------------------------------------------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie ---------------------------------------------------------------