pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Week of year function?
7+ messages / 5 participants
[nested] [flat]

* Week of year function?
@ 1999-10-22 19:52  Zot O'Connor <zot@zotconsulting.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Zot O'Connor @ 1999-10-22 19:52 UTC (permalink / raw)
  To: postgres sql <pgsql-sql@hub.org>

Is there a function to return the week of the year (0-51)?

-- 
Zot O'Connor

www.ZotConsulting.com
www.WhiteKnightHackers.com



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Week of year function?
@ 1999-10-25 17:14  Herouth Maoz <herouth@oumail.openu.ac.il>
  parent: Zot O'Connor <zot@zotconsulting.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Herouth Maoz @ 1999-10-25 17:14 UTC (permalink / raw)
  To: Zot O'Connor <zot@zotconsulting.com>; postgres sql <pgsql-sql@hub.org>

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Week of year function?
@ 1999-10-25 19:04  Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
  parent: Herouth Maoz <herouth@oumail.openu.ac.il>
  1 sibling, 1 reply; 7+ messages in thread

From: Moray McConnachie @ 1999-10-25 19:04 UTC (permalink / raw)
  To: Zot O'Connor <zot@zotconsulting.com>; postgres sql <pgsql-sql@hub.org>; Herouth Maoz <herouth@oumail.openu.ac.il>


----- 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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Week of year function?
@ 1999-10-25 19:47  Zot O'Connor <zot@zotconsulting.com>
  parent: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
  0 siblings, 1 reply; 7+ messages in thread

From: Zot O'Connor @ 1999-10-25 19:47 UTC (permalink / raw)
  To: postgres sql <pgsql-sql@hub.org>

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Week of year function?
@ 1999-10-26 07:53  Zakkr <zakkr@zf.jcu.cz>
  parent: Zot O'Connor <zot@zotconsulting.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Zakkr @ 1999-10-26 07:53 UTC (permalink / raw)
  To: Zot O'Connor <zot@zotconsulting.com>; +Cc: postgres sql <pgsql-sql@hub.org>


 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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Week of year function?
@ 1999-10-26 10:33  Herouth Maoz <herouth@oumail.openu.ac.il>
  parent: Herouth Maoz <herouth@oumail.openu.ac.il>
  1 sibling, 0 replies; 7+ messages in thread

From: Herouth Maoz @ 1999-10-26 10:33 UTC (permalink / raw)
  To: Zot O'Connor <zot@zotconsulting.com>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Week of year function?
@ 1999-10-26 15:19  Kyle Bateman <kyle@actarg.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Kyle Bateman @ 1999-10-26 15:19 UTC (permalink / raw)
  To: zot@zotconsulting.com; +Cc: pgsql-sql

> 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)?
>

I use pltcl and the following function:

-- Convert date in 1999-01-15 format to the month of the year (1999,35)
-- calling sequence: date_week(ISO_date)
drop function date_week(text);
create function date_week(text) returns text as '
        set spl [split $1 {-/. }]
        set year [lindex $spl 0]
        set month [string trimleft [lindex $spl 1] 0]
        set day [lindex $spl 2]
        if {$month > 0 && $month <= 31} {
            set secs [clock scan "$month/$day/$year"]
        } else {
            set secs [clock scan "$month $day, $year"]
        }
        set week [clock format $secs -format "%U"]
        if {$week < 1} {
            return "[expr $year - 1]-52"
        }
        return "$year-$week"
        ' LANGUAGE 'pltcl';

This has some extra stuff in it you don't need, but might demonstrate
the point.

Attachments:

kyle.vcf (text/x-vcard, 290B) download begin:vcard n:Bateman;Kyle tel;fax:801-377-8096 tel;work:801-377-8033x101 x-mozilla-html:FALSE url:www.actiontarget.com org:Action Target Inc adr:;;PO Box 636;Provo;UT;84603;US version:2.1 email;internet:kyle@actiontarget.com title:President x-mozilla-cpt:;-15520 fn:Kyle Bateman end:vcard
^ permalink raw reply [nested|flat] 7+ messages in thread


end of thread, other threads:[~1999-10-26 15:19 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-10-22 19:52 Week of year function? Zot O'Connor <zot@zotconsulting.com>
1999-10-25 17:14 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-10-25 19:04   ` Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
1999-10-25 19:47     ` Zot O'Connor <zot@zotconsulting.com>
1999-10-26 07:53       ` Zakkr <zakkr@zf.jcu.cz>
1999-10-26 10:33   ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-10-26 15:19 Re: [SQL] Week of year function? Kyle Bateman <kyle@actarg.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox