Re: extract (dow/week from date)

On Sun, 21 Aug 2005, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> > I think something like:
> > (CASE WHEN extract(dow from date) = 0 THEN 7 else extract(dow from date))
>
> It's really not that hard:
>
> (extract(dow from date) + 6) % 7
>
> You can rotate to any week-start day you like by substituting different
> things for "6".

Yeah, mod is probably better and more general than case. It's still fairly
ugly to require a non-trivial expression to get something that's
consistent with the way that extract(week from date) works. ;)

For anyone who is considering using this, to get an actual iso day of
week, I think you'll need to add 1 at the end since that appears to use a
1-7 range. If you're just using it for sorting or the like, you can get
away with not bothering.