Re: [SQL] Week of year function? - Mailing list pgsql-sql

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