Обсуждение: Week of year function?
Is there a function to return the week of the year (0-51)? -- Zot O'Connor www.ZotConsulting.com www.WhiteKnightHackers.com
At 21:52 +0200 on 22/10/1999, Zot O'Connor wrote: > Is there a function to return the week of the year (0-51)? Seems you only need to divide the day of the year by seven to reach that, don't you? Maybe you should try: CREATE FUNCTION week( datetime ) RETURNS int4 AS ' SELECT int( date_part( ''day'', $1 - date_trunc( ''year'', $1 ) ) ) /7 ' LANGUAGE 'sql'; Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
----- Original Message -----
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Zot O'Connor <zot@zotconsulting.com>; postgres sql <pgsql-sql@hub.org>
Sent: Monday, October 25, 1999 6:14 PM
Subject: Re: [SQL] Week of year function?
> At 21:52 +0200 on 22/10/1999, Zot O'Connor wrote:
>
>
> > Is there a function to return the week of the year (0-51)?
>
> Seems you only need to divide the day of the year by seven to reach that,
> don't you?
>
> Maybe you should try:
>
> CREATE FUNCTION week( datetime ) RETURNS int4 AS '
> SELECT int( date_part( ''day'', $1 - date_trunc( ''year'', $1 ) ) ) / 7
> ' LANGUAGE 'sql';
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).
CREATE FUNCTION week( datetime ) RETURNS int4 AS ' SELECT (int( date_part( ''day'', $1 - date_trunc( ''year'',
$1 )))-datepart(''dow'',date_trunc(''year'',$1)))) / 7' LANGUAGE 'sql';
I don't have postgres accessible from this computer, but I think it should
be something like that - gives weeks 0-51 if dow returns 0-6. Herouth can
probably rewrite it more concisely..
Yours,
Moray
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
Hi,
in _any_ next PostgreSQL version (perhaps) will include the oracle compatible
to_char(datetime, text), this routine allows arbitrary formatting for
datetime-text outputs. Example:
template1=> select to_char(now(), 'My week: WW');
to_char
-----------
My week: 43
(1 row)
Zakkr
------------------------------------------------------------------------------
<zakkr@zf.jcu.cz> http://home.zf.jcu.cz/~zakkr/
Kim Project: http://home.zf.jcu.cz/~zakkr/kim/ (process manager)
FTP: ftp://ftp2.zf.jcu.cz/users/zakkr/ (C/ncurses/PgSQL)
------------------------------------------------------------------------------
...and cathedral dilapidate
At 20:24 +0200 on 25/10/1999, Zot O'Connor wrote: > Herouth Maoz wrote: > > > > At 21:52 +0200 on 22/10/1999, Zot O'Connor wrote: > > > > > Is there a function to return the week of the year (0-51)? > > > > Seems you only need to divide the day of the year by seven to reach that, > > don't you? > > > > BTW I ought to offset the year to some standard too since Jan1 is not a > sunday. I know Intel starts work week 1 in dec this work year. That's why I asked whether you only needed to divide by seven. It depends on your definition of "a week". If you define a week as "seven consecutive days", then in a year with 365 or 366 days you will always have 53 weeks, the last of which being a short one. If you define a week as "Monday to Sunday", or "Sunday to Saturday" (this is culture-dependent), then you may even have 54 weeks per year (52 full weeks, a weekend in the beginning, and the beginning of a week in the end - but only in a leap year). This complicates the calculation a bit. You probably have to subtract the day-of-week (0-7) oy January first from it, and then subtract the resulting date from your $1, and divide by 7. This is true in places where the week's first day is Sunday. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma