Received: from eater.zotnet.com (IDENT:root@snapuser87.pacificcrest.net [216.36.24.87]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA44681 for ; Mon, 25 Oct 1999 15:45:59 -0400 (EDT) (envelope-from zot@zotconsulting.com) Received: from zotconsulting.com (zot@mp.zotnet.com [216.36.24.118]) by eater.zotnet.com (8.8.7/8.8.7) with ESMTP id NAA00956 for ; Mon, 25 Oct 1999 13:03:05 -0700 Sender: zot@eater.zotnet.com Message-ID: <3814B3C5.E9BAF9E5@zotconsulting.com> Date: Mon, 25 Oct 1999 12:47:17 -0700 From: "Zot O'Connor" Organization: Zot Consulting X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.5-15smp i686) X-Accept-Language: en MIME-Version: 1.0 To: postgres sql Subject: Re: [SQL] Week of year function? References: <002501bf1f1b$c03a7da0$681b4cc0@public.ox.ac.uk> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Moray McConnachie wrote: > > Seems you only need to divide the day of the year by seven to reach that, > > don't you? > > I don't think that's quite right. You would need to add some maths to make > sure that if January 1st is a Wednesday, week 1 of the year begins on > January 6th (with Monday as first day of week) or Jan 5th (Sunday as first > day of week). > For my purposes Herouth's approach would work. I came up with a much more complicated function because I thought "day" of date_part was Day of Month. I wish the documentation would just give examples of each value (just take one date and have a table of values). In fact the week of year is a but more complicated. Intel for instance started this work year on the last Sunday of December. I can see this being a company standards issue. I merely wanted to group totals by week, so that I ducked the issue :) Thanks! Actually I now see I am partially correct. A date_part of a datetime does show the DoM: Now|Mon Oct 25 12:24:47 1999 PDT year|1999 month|10 day|25 hour|12 minute|24 second|47 decade|200 century|20 millenium|2 millisecond|0 microsecond|0 dow|1 epoch|940879487 But If I so a timespan: select date_trunc('year','now'::datetime); date_trunc ---------------------------- Fri Jan 01 00:00:00 1999 PST (1 row) => select 'now'::datetime - date_trunc('year','now'::datetime); ?column? ----------------------------------- @ 297 days 11 hours 32 mins 54 secs (1 row) And run the same functions as above year|0 month|0 day|297 hour|11 minute|29 second|45 decade|1 century|1 millenium|1 millisecond|0 microsecond|0 END I did not expect to see century/decade/millenium of 1 (which is fine since it is consistent with itself), but this was not documented, and should be. Can be documented better? I read everything to do with date and time and search the mailing list for 4 hours and did not understand date_trunc correctly. Even cutting and pasting this note would help a lot of people My php code I used: $array_names = array ("year", "month", "day", "hour", "minute", "second", "decade", "century", "millenium", "millisecond", "microsecond" ); while (list($key, $val) = each($array_names)) { $query="SELECT date_part('$val', 'now'::datetime - date_trunc('year', 'now'::datetime))"; $fcs->query($query); $fcs->next_record(); echo"$val|". $fcs->f("0") . "
\n"; } Thanks all! -- Zot O'Connor www.ZotConsulting.com www.WhiteKnightHackers.com