Re: [SQL] Week of year function?

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") . "<BR>\n";
}

Thanks all!

--
Zot O'Connor

www.ZotConsulting.com
www.WhiteKnightHackers.com