Thread: Date Questions

It appears to me that there is some inconsistency in the date
calculations for my PostgreSQL install (version 8.0 on Win32).

January 07 of 2007 is a Sunday. Based on the documentation I would
expect that date to be the first day of the second week of the year
2007. That's not what I'm getting. When I run:

  select date_part('DOW'::text, '2007-01-07'::timestamp);

I receive my expected result of 0 indicating that Sunday is the first
day of the week. But, when I run

  select date_part('week'::text, '2007-01-07'::timestamp);

I get a result of 1 indicating that it is part of the first week of the
year. That seems inconsistent with the first result which would indicate
it was the first day of the second week of the year. I was expecting a
result of 2.

Am I missing something?

Many thanks!

rjsjr

"Robert Sanford" <rsanford@trefs.com> writes:
> It appears to me that there is some inconsistency in the date
> calculations for my PostgreSQL install (version 8.0 on Win32).

extract(DOW) follows the convention that the week starts on Sunday,
but extract(WEEK) uses the ISO convention, which includes weeks
starting on Monday.  No, it's not super consistent, but hardly
anything about date/time conventions is :-(

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/23/07 17:22, Robert Sanford wrote:
> It appears to me that there is some inconsistency in the date
> calculations for my PostgreSQL install (version 8.0 on Win32).
>
> January 07 of 2007 is a Sunday. Based on the documentation I would
> expect that date to be the first day of the second week of the year
> 2007. That's not what I'm getting. When I run:
>
>   select date_part('DOW'::text, '2007-01-07'::timestamp);
>
> I receive my expected result of 0 indicating that Sunday is the first
> day of the week. But, when I run
>
>   select date_part('week'::text, '2007-01-07'::timestamp);
>
> I get a result of 1 indicating that it is part of the first week of the
> year. That seems inconsistent with the first result which would indicate
> it was the first day of the second week of the year. I was expecting a
> result of 2.
>
> Am I missing something?

01-Jan was a Monday.  So, is 07-Jan the first full week of the
month, or not?
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFtqb1S9HxQb37XmcRAnn5AKCXcaxwVSdVg+OVoHNebjGkJgA9RACgmATo
qcoUVqmUrODTLPDOc0pFl64=
=Vz8N
-----END PGP SIGNATURE-----

Every app has its joyful inconsistencies and learning them is part of
the process...

So if I'm currently doing a GROUP BY date_part(week, datecolumn) in a
view and  I really want to be using the DOW convention I need to write a
customized get_sunday_week_num (date) to make everything consistent. Or,
did someone else run into this problem and solve it before me?

rjsjr

PS - Coming from an MS-SQL Server background I *love* the date
functionality in PostgreSQL.

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, January 23, 2007 5:40 PM
> To: Robert Sanford
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Date Questions
>
> "Robert Sanford" <rsanford@trefs.com> writes:
> > It appears to me that there is some inconsistency in the date
> > calculations for my PostgreSQL install (version 8.0 on Win32).
>
> extract(DOW) follows the convention that the week starts on
> Sunday, but extract(WEEK) uses the ISO convention, which
> includes weeks starting on Monday.  No, it's not super
> consistent, but hardly anything about date/time conventions is :-(
>
>             regards, tom lane
>

On 01/23/07 17:22, Robert Sanford wrote:
>
> January 07 of 2007 is a Sunday. Based on the documentation I would
> expect that date to be the first day of the second week of the year
> 2007. That's not what I'm getting. When I run:

Read the 'week' documentation carefully. ISO weeks start on Mondays. So
2007-01-07 would be the last day of the first week of 2007.

So in some sense dow is inconsistant with week, but both are working
consistant with the documentation.