[GENERAL] 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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Date Questions
Robert Sanford [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Date Questions
-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-
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Date Questions
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:[EMAIL PROTECTED] Sent: Tuesday, January 23, 2007 5:40 PM To: Robert Sanford Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Date Questions Robert Sanford [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Date Questions
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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
