Thread: Find all the dates in the calendar week?
I have a table of events with a column which stores datetimes. I want to check if a datetime is inside the current calendar week (i.e. from the previous monday to the next sunday). The purpose is to SELECT all the events of the week. I cannot find a way to do it in SQL? Did I miss something? [If there is no SQL way, I'll hack it in a Perl script. Code or suggestions welcome.]
> I have a table of events with a column which stores datetimes. I > want to check > if a datetime is inside the current calendar week (i.e. from the previous > monday to the next sunday). The purpose is to SELECT all the > events of the > week. See this posting: > -----Original Message----- > Sent: Tuesday, 4 July 2000 6:01 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] number of weeks > > -- Week number of the year > to_char(CURRENT_TIMESTAMP, 'WW'); > -- Day number of the year > to_char(CURRENT_TIMESTAMP, 'DDD'); > > See the documentation at: > http://www.comptechnews.com/~reaster/postgres/functions2976.htm So, something like: SELECT event FROM events WHERE to_char(CURRENT_TIMESTAMP, 'ww') = to_char(eventdate, 'ww'); Regards, Andrew Snow
On Thursday 6 July 2000, at 18 h 5, the keyboard of "Andrew Snow" <als@fl.net.au> wrote: > SELECT event FROM events WHERE to_char(CURRENT_TIMESTAMP, 'ww') = > to_char(eventdate, 'ww'); Sorry, I should have mentioned that I played with to_char and it seems the documentation is wrong: conferences=> select * from conferences where to_char(CURRENT_TIMESTAMP, 'ww') = conferences-> to_char(date_begin, 'ww'); ERROR: No such function 'to_char' with the specified attributes Or it is a new feature of PostgreSQL 7?
> SELECT event FROM events WHERE to_char(CURRENT_TIMESTAMP, 'ww') = > to_char(eventdate, 'ww'); Actually, you will probably want to add something in to make sure the year is the one you want, too! - Andrew
Stephane Bortzmeyer wrote: > > On Thursday 6 July 2000, at 18 h 5, the keyboard of "Andrew Snow" > <als@fl.net.au> wrote: > > > SELECT event FROM events WHERE to_char(CURRENT_TIMESTAMP, 'ww') = > > to_char(eventdate, 'ww'); > > Sorry, I should have mentioned that I played with to_char and it seems the documentation is wrong: > > conferences=> select * from conferences where to_char(CURRENT_TIMESTAMP, 'ww') = > conferences-> to_char(date_begin, 'ww'); > ERROR: No such function 'to_char' with the specified attributes > > Or it is a new feature of PostgreSQL 7? to_xxx are new to 7.0, thanks to Karel Zak. Mike Mascari
On Thursday 6 July 2000, at 5 h 17, the keyboard of Mike Mascari <mascarm@mascari.com> wrote: > to_xxx are new to 7.0, thanks to Karel Zak. OK, any solution for 6.x, since it is quite inconvenient to upgrade?
On Thu, 6 Jul 2000, Andrew Snow wrote: > > See the documentation at: > > http://www.comptechnews.com/~reaster/postgres/functions2976.htm > > > So, something like: > > SELECT event FROM events WHERE to_char(CURRENT_TIMESTAMP, 'ww') = > to_char(eventdate, 'ww'); A small note about 'WW' in to_char/timestamp(), in 7.0 is a small bug in this code (already discussed) and it is based on standard weeks, but in 7.1 it will *different* and based on weeks like Oracle (already in CVS). Oracle: first week start JAN-1 and all next weeks start in same day. For example if first day of year is friday, all weeks start in friday. ISO-week: week that has more than 4 day and start in Sunday. In future I try implement 'IW' that is ISO-week, but now I work on different things. In current 7.0 is probably better for week operations use date_part('week', TIMESTAMP) than to_char(). Karel
> ISO-week: week that has more than 4 day and start in Sunday.
> In future I try implement 'IW' that is ISO-week, but now I work on different
> things.
> In current 7.0 is probably better for week operations use
> date_part('week', TIMESTAMP) than to_char().
Note that the above returns ISO-week, not some Sun-Sat thing ('dow' ==
"day of week" does that). I haven't yet implemented ISO-year (Karel?)
which would seem to be an essential piece to use ISO-week effectively.
- Thomas
> yes, my current to_char() is almost compatible with oracle, but I need
> last two features --- IYYY (ISO-year) and IW (ISO-week). But I not explore
> it exactly yet. I mean that current (PG's) week-of-year is like ISO or not?
Yes. 'week' is "ISO week" (new for 7.0 afaicr). And it wasn't *entirely*
trivial to implement, so you may want to steal code for to_char() ;)
I just didn't implement the corresponding "year" code at least partly
because I wasn't sure what to call it. 'iyear' seems like a pretty good
choice, or should it be 'isoyear'? 'year' is already used, obviously,
and if 'iyear' is chosen then perhaps I should change 'week' to 'iweek'
for consistancy. Comments?
- Thomas
On Thu, 6 Jul 2000, Thomas Lockhart wrote:
> > ISO-week: week that has more than 4 day and start in Sunday.
> > In future I try implement 'IW' that is ISO-week, but now I work on different
> > things.
> > In current 7.0 is probably better for week operations use
> > date_part('week', TIMESTAMP) than to_char().
>
> Note that the above returns ISO-week, not some Sun-Sat thing ('dow' ==
> "day of week" does that). I haven't yet implemented ISO-year (Karel?)
> which would seem to be an essential piece to use ISO-week effectively.
>
yes, my current to_char() is almost compatible with oracle, but I need
last two features --- IYYY (ISO-year) and IW (ISO-week). But I not explore
it exactly yet. I mean that current (PG's) week-of-year is like ISO or not?
But freely, I not pursue after date/time operations and calculations, it is
"alchemy" (I admire you Thomas)... I must between work on to_char() insert
work on some other things :-)
Karel
On Thu, 6 Jul 2000, Thomas Lockhart wrote:
> > yes, my current to_char() is almost compatible with oracle, but I need
> > last two features --- IYYY (ISO-year) and IW (ISO-week). But I not explore
> > it exactly yet. I mean that current (PG's) week-of-year is like ISO or not?
>
> Yes. 'week' is "ISO week" (new for 7.0 afaicr). And it wasn't *entirely*
> trivial to implement, so you may want to steal code for to_char() ;)
My problem is not convert some data to char (in to_char()), but vice
versa convert from char to timestamp. For example convert week-of-year
number to real date. But yes, I steal you :-)
> I just didn't implement the corresponding "year" code at least partly
> because I wasn't sure what to call it. 'iyear' seems like a pretty good
> choice, or should it be 'isoyear'? 'year' is already used, obviously,
> and if 'iyear' is chosen then perhaps I should change 'week' to 'iweek'
> for consistancy. Comments?
For to_char() it is unambiguous, IW and IYYY.
BTW --- I now planning for 7.2 some routine for measure unit conversion. For
example 'km' to 'mile' ...etc. It will very good for PG international
domination :-)
Karel
On Thu, Jul 06, 2000 at 15:21:53 +0200, Karel Zak wrote: > ISO-week: week that has more than 4 day and start in Sunday. What about weeks that start in Monday (as here in Norway)? Helge --
> > ISO-week: week that has more than 4 day and start in Sunday.
> What about weeks that start in Monday (as here in Norway)?
You can probably calculate that from the existing date/time code. But it
isn't ISO-8601. Karel was referring to the ISO-defined week for which
the first week of the year is that week which contains a Thursday. It
has the rather strange outcome that for some years, days in the previous
or subsequent calendar year fall into the adjacent "ISO-year".
- Thomas
On Thu, 6 Jul 2000, Helge Haugland wrote:
> On Thu, Jul 06, 2000 at 15:21:53 +0200, Karel Zak wrote:
> > ISO-week: week that has more than 4 day and start in Sunday.
>
> What about weeks that start in Monday (as here in Norway)?
Yes, I known, in my country too. A question is how discern it ---
via 'SET WEEKSTART TO Monday'? Thomas, have you some idea about
mon-weeks?
Karel
On Fri, 7 Jul 2000, Thomas Lockhart wrote:
> > > ISO-week: week that has more than 4 day and start in Sunday.
> > What about weeks that start in Monday (as here in Norway)?
>
> You can probably calculate that from the existing date/time code. But it
> isn't ISO-8601. Karel was referring to the ISO-defined week for which
> the first week of the year is that week which contains a Thursday. It
> has the rather strange outcome that for some years, days in the previous
> or subsequent calendar year fall into the adjacent "ISO-year".
>
Is anywhere on net available see this ISO date/time definition?
Karel
On Thursday 6 July 2000, at 9 h 28, the keyboard of Stephane Bortzmeyer
<bortzmeyer@pasteur.fr> wrote:
> I have a table of events with a column which stores datetimes. I want to check
> if a datetime is inside the current calendar week (i.e. from the previous
> monday to the next sunday). The purpose is to SELECT all the events of the
> week.
...
> [If there is no SQL way, I'll hack it in a Perl script. Code or suggestions
> welcome.]
Here is the code:
my (@fields) = localtime(time());
my ($week_day) = $fields[6];
$week_day = ($week_day-1) % 7;
my ($previous_monday_offset) = $week_day;
my ($next_monday_offset) = 7 - $week_day;
my ($begin_this_week) = &string2time (($fields[5]+1900) . " " .
($fields[4]+1) . " " .
($fields[3]-
$previous_monday_offset) .
" " .
"0 0");
my ($end_this_week) = &string2time (($fields[5]+1900) . " " .
($fields[4]+1) . " " .
($fields[3]+
$next_monday_offset-1) .
" " .
"23 59");
my ($begin_this_week_iso) = &time2iso ($begin_this_week);
my ($end_this_week_iso) = &time2iso ($end_this_week);
...
($sth = $dbh->prepare( qq{
SELECT *
FROM conferences
WHERE
((date_begin >= '$begin_this_week_iso') AND
(date_begin <= '$end_this_week_iso')) OR
((date_end >= '$begin_this_week_iso') AND
(date_end <= '$end_this_week_iso'))
ORDER BY Date_begin
})) or die "Can't prepare statement: $DBI::errstr";
> Is anywhere on net available see this ISO date/time definition? http://www.cl.cam.ac.uk/~mgk25/iso-time.html has a summary and some more references. - Thomas
Thomas Lockhart writes: > I just didn't implement the corresponding "year" code at least partly > because I wasn't sure what to call it. 'iyear' seems like a pretty good > choice, or should it be 'isoyear'? 'year' is already used, obviously, > and if 'iyear' is chosen then perhaps I should change 'week' to 'iweek' > for consistancy. Comments? Then we should probably rather change 'year' to something else. Standards should be preferred. Out of curiosity, what's the difference between ISO-year and proprietary-year? I can see the week-of-year thing, but the year of a year is always constant, no? Btw., isn't there an SQL EXTRACT function for all of this? Shouldn't we be thinking in terms of that? IMHO, the "Oracle-week" is pretty brain-dead. You can get that from day-of-year % 7. The next thing they tell us is that the month of the year is really day-of-year % 30. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
> > I just didn't implement the corresponding "year" code at least partly
> > because I wasn't sure what to call it. 'iyear' seems like a pretty good
> > choice, or should it be 'isoyear'? 'year' is already used, obviously,
> > and if 'iyear' is chosen then perhaps I should change 'week' to 'iweek'
> > for consistancy. Comments?
> Then we should probably rather change 'year' to something else. Standards
> should be preferred.
"Standards" in this case include common usage and the ISO-defined
business usage of business-year/week-of-year. If we had to choose,
clearly common usage wins.
> Out of curiosity, what's the difference between
> ISO-year and proprietary-year? I can see the week-of-year thing, but the
> year of a year is always constant, no?
ISO-year/week-of-year is a business-only construct, perhaps helping with
payment intervals. There is some slop in the beginning and end of each
calendar year, which can result in a particular day in a calendar year
fitting into a different ISO-year (not the right term btw).
> Btw., isn't there an SQL EXTRACT function for all of this? Shouldn't we be
> thinking in terms of that?
EXTRACT() is implemented with date_part().
- Thomas
Thomas Lockhart writes: > "Standards" in this case include common usage and the ISO-defined > business usage of business-year/week-of-year. If we had to choose, > clearly common usage wins. The problem with common usage is that it invariably differs between localities and is therefore *not* standard. See date styles. Case week of the year: I lived in the U.S. for four years and have never heard of WoY used the way Oracle defines it. In Sweden on the other hand a lot of the calendaring is done in terms of WoY, official and inofficial. And in this case the common usage coincides with the standard. SQL has over the years steadily crept to being a truly international standard, see also references to Unicode in SQL99. It would be a shame if we substituted our idea of common usage in place of the coherent ISO framework > ISO-year/week-of-year is a business-only construct, perhaps helping with > payment intervals. Aren't people using databases to run businesses, perhaps even payment systems? I know I do. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
> > ISO-year/week-of-year is a business-only construct, perhaps helping with
> > payment intervals.
> Aren't people using databases to run businesses, perhaps even payment
> systems? I know I do.
I think we got off on the wrong foot on this discussion. I'm not arguing
for or against anything, just trying to clarify what we have and will
have in the future. afaik nothing existing or proposed is at odds with
any national or international standard.
In particular, "week of year" is defined in ISO-8601, and I'm not
remembering any other standard or convention which would specify an
incompatible definition of this concept. The "year" associated with
"week of year" is not guaranteed to coincide with the common-usage
"calendar year", but that is specified in the standard.
- Thomas