Thread: Problem with Day of Week
Greetings,
I notice some talk about date problems and interestingly enough planning
out an application in which I will need to be able to manipulate dates.
I notice however that there seems to be a discrepancy with the day or
week in 7.0.3
---
pmhcc=# select date_part('dow','now'::timestamp);date_part
----------- 1
(1 row)
pmhcc=# select to_char('now'::timestamp,'D');to_char
---------2
(1 row)
pmhcc=# select version(); version
---------------------------------------------------------------------PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by
gccegcs-2.91.66
(1 row)
pmhcc=# select now(); now
------------------------2001-01-29 12:57:46-05
(1 row)
---
Now as far as I know, Sunday is supposed to 1, so I would think that
date_part is doing something funky unless I am mis-understanding
something. I was hoping that someone might be able to shead some light
on this. Thanks.
Keith C. Perry
VCSN, Inc.
http://vcsn.com
On Mon, 29 Jan 2001, Keith Perry wrote:
> Greetings,
>
> I notice some talk about date problems and interestingly enough planning
> out an application in which I will need to be able to manipulate dates.
> I notice however that there seems to be a discrepancy with the day or
> week in 7.0.3
>
> ---
>
> pmhcc=# select date_part('dow','now'::timestamp);
> date_part
> -----------
> 1
> (1 row)
>
> pmhcc=# select to_char('now'::timestamp,'D');
> to_char
> ---------
> 2
> (1 row)
>
See:
test=# select date_part('dow','2001-02-11'::timestamp);date_part
----------- 0
test=# select to_char('2001-02-11'::timestamp, 'D');to_char
---------1date_part is based on zero - use range 0-6to_char is based on one - use range 1-7
Karel
Keith,
Try:
select to_char('now'::timestamp,'Dy'); to_char
--------- Mon
(1 row)
----------------------------------------------------------
DAY = full upper case day name (9 chars)
Day = full mixed case day name (9 chars)
day = full lower case day name (9 chars)
DY = abbreviated upper case day name (3 chars)
Dy = abbreviated mixed case day name (3 chars)
dy = abbreviated lower case day name (3 chars)
DDD = day of year (001-366)
DD = day of month (01-31)
D = day of week (1-7; SUN=1)
-------------------------------------------------------------
Taken from
http://www.postgresql.org/users-lounge/docs/7.0/user/functions2972.htm
At 12:55 PM 1/29/01 -0500, Keith Perry wrote:
>Greetings,
>
>I notice some talk about date problems and interestingly enough planning
>out an application in which I will need to be able to manipulate dates.
>I notice however that there seems to be a discrepancy with the day or
>week in 7.0.3
>
>---
>
>pmhcc=# select date_part('dow','now'::timestamp);
> date_part
>-----------
> 1
>(1 row)
>
>pmhcc=# select to_char('now'::timestamp,'D');
> to_char
>---------
> 2
>(1 row)
>
>pmhcc=# select version();
> version
>---------------------------------------------------------------------
> PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
>(1 row)
>
>pmhcc=# select now();
> now
>------------------------
> 2001-01-29 12:57:46-05
>(1 row)
>
>---
>
>Now as far as I know, Sunday is supposed to 1, so I would think that
>date_part is doing something funky unless I am mis-understanding
>something. I was hoping that someone might be able to shead some light
>on this. Thanks.
>
>Keith C. Perry
>VCSN, Inc.
>http://vcsn.com
On Mon, Feb 05, 2001 at 05:15:47PM +0100, Karel Zak wrote:
>
> test=# select date_part('dow','2001-02-11'::timestamp);
> date_part
> -----------
> 0
>
> test=# select to_char('2001-02-11'::timestamp, 'D');
> to_char
> ---------
> 1
>
>
> date_part is based on zero - use range 0-6
> to_char is based on one - use range 1-7
>
My understanding is that one is ISO, the other is for Oracle
compatability, is that not right Karel?
Ross
On Mon, 5 Feb 2001, Ross J. Reedstrom wrote:
> On Mon, Feb 05, 2001 at 05:15:47PM +0100, Karel Zak wrote:
> >
> > test=# select date_part('dow','2001-02-11'::timestamp);
> > date_part
> > -----------
> > 0
> >
> > test=# select to_char('2001-02-11'::timestamp, 'D');
> > to_char
> > ---------
> > 1
> >
> >
> > date_part is based on zero - use range 0-6
> > to_char is based on one - use range 1-7
> >
>
> My understanding is that one is ISO, the other is for Oracle
> compatability, is that not right Karel?
You are right, Larry Ellison use 1-7 (he must, if the PostgreSQL to_char()
is 100% comapatible :-)
I don't know if 0-6 is like ISO (Thomas?), but surely it's like POSIX.
Karel
On Mon, 5 Feb 2001 keith@vcsn.com wrote:
> Ok, so there is actually two standards then. Is this documented
> anywhere? Is this is something that is going to change? I don't want
> to write and app and have things "break" during and upgrade :)
I mean you can be caseful. Not changes planned here.
date_part() is not documented to much in detail, but formatting
functions are described good.
Karel
Ok, so there is actually two standards then. Is this documented
anywhere? Is this is something that is going to change? I don't want
to write and app and have things "break" during and upgrade :)
Thanks for the response.
On Mon, 5 Feb 2001, Karel Zak wrote:
>
> On Mon, 29 Jan 2001, Keith Perry wrote:
>
> > Greetings,
> >
> > I notice some talk about date problems and interestingly enough planning
> > out an application in which I will need to be able to manipulate dates.
> > I notice however that there seems to be a discrepancy with the day or
> > week in 7.0.3
> >
> > ---
> >
> > pmhcc=# select date_part('dow','now'::timestamp);
> > date_part
> > -----------
> > 1
> > (1 row)
> >
> > pmhcc=# select to_char('now'::timestamp,'D');
> > to_char
> > ---------
> > 2
> > (1 row)
> >
>
> See:
>
> test=# select date_part('dow','2001-02-11'::timestamp);
> date_part
> -----------
> 0
>
> test=# select to_char('2001-02-11'::timestamp, 'D');
> to_char
> ---------
> 1
>
>
> date_part is based on zero - use range 0-6
> to_char is based on one - use range 1-7
>
> Karel
>
Always- I think I'll use the to_char since I think you all are saying that that is ISO or at least POSIX. On Mon, 5 Feb 2001, Karel Zak wrote: > > On Mon, 5 Feb 2001 keith@vcsn.com wrote: > > > Ok, so there is actually two standards then. Is this documented > > anywhere? Is this is something that is going to change? I don't want > > to write and app and have things "break" during and upgrade :) > > I mean you can be caseful. Not changes planned here. > > date_part() is not documented to much in detail, but formatting > functions are described good. > > Karel >