Postgres Pro
Downloads
Home   >   mailing lists

Thread: Week of year function?

Week of year function?

From
"Zot O'Connor"
Date:
Is there a function to return the week of the year (0-51)?

-- 
Zot O'Connor

www.ZotConsulting.com
www.WhiteKnightHackers.com

Re: [SQL] Week of year function?

From
Herouth Maoz
Date:
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

Re: [SQL] Week of year function?

From
"Moray McConnachie"
Date:
----- 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

Re: [SQL] Week of year function?

From
"Zot O'Connor"
Date:
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

Re: [SQL] Week of year function?

From
Zakkr
Date:
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

Re: [SQL] Week of year function?

From
Herouth Maoz
Date:
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