Postgres Pro
Downloads
Home   >   mailing lists

Thread: datestyle formatting

datestyle formatting

From
mstory@uchicago.edu
Date:

I need to know the day of the week for a schedule rotation algorithm i'm working
on.  Initially i was going to use the function for day of the year and week of
the year to find the first day of the year, but it seems like it would be easier
to use the postgres, dmy format and then parse the string for the day
abbreviation.  I've switched the datestyle format in the .conf file and used the
set to command, but my output never reads as the example in the 7.4.6 docs it
always shows just the dmy standard output 10-12-1999 for example as opposed to
wed oct 17 1987, any help on this would be appreciated

thanks,
matt

Re: datestyle formatting

From
Tom Lane
Date:
mstory@uchicago.edu writes:
> I need to know the day of the week for a schedule rotation algorithm
> i'm working on.

extract(dow ...) might help.

            regards, tom lane

Re: datestyle formatting

From
Michael Glaesemann
Date:
On Jan 11, 2005, at 15:03, mstory@uchicago.edu wrote:

> I need to know the day of the week for a schedule rotation algorithm
> i'm working
> on.  Initially i was going to use the function for day of the year and
> week of
> the year to find the first day of the year, but it seems like it would
> be easier
> to use the postgres, dmy format and then parse the string for the day
> abbreviation.  I've switched the datestyle format in the .conf file
> and used the
> set to command, but my output never reads as the example in the 7.4.6
> docs it
> always shows just the dmy standard output 10-12-1999 for example as
> opposed to
> wed oct 17 1987, any help on this would be appreciated


Check out to_char(). It'll probably help you get to where you want to
go.

<http://www.postgresql.org/docs/7.4/interactive/functions-
formatting.html>

Michael Glaesemann
grzm myrealbox com

Re: datestyle formatting

From
Michael Fuhr
Date:
On Tue, Jan 11, 2005 at 12:03:16AM -0600, mstory@uchicago.edu wrote:
>
> I need to know the day of the week for a schedule rotation algorithm
> i'm working on.

Others have already replied to this part.

> I've switched the datestyle format in the .conf file and used the
> set to command, but my output never reads as the example in the
> 7.4.6 docs it always shows just the dmy standard output 10-12-1999
> for example as opposed to wed oct 17 1987, any help on this would
> be appreciated

Apparently the 'Postgres' output format acts differently depending
on whether the value is a date or a timestamp:

SET datestyle TO 'Postgres, DMY';

SELECT current_date;
    date
------------
 11-01-2005
(1 row)

SELECT current_timestamp;
             timestamptz
-------------------------------------
 Tue 11 Jan 01:30:32.617394 2005 MST
(1 row)

The 'Postgres' output format also doesn't appear to support 'YMD',
always putting the year last:

SET datestyle TO 'Postgres, YMD';

SELECT current_date;
    date
------------
 01-11-2005
(1 row)

SELECT current_timestamp;
            timestamptz
-----------------------------------
 Tue Jan 11 01:31:25.6092 2005 MST
(1 row)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/