pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWeek 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