Re: date_trunct() and start of week

On 27 Nov 2009, at 8:49, Rikard Bosnjakovic wrote:

> Read 9.9.2 on http://www.postgresql.org/docs/8.1/static/functions-datetime.html
> and you will see that even if you find such setting, date_trunc() will
> always return monday as start of week:
>
> =========[snip]
> source is a value expression of type timestamp or interval. (Values of
> type date and time are cast automatically, to timestamp or interval
> respectively.) field selects to which precision to truncate the input
> value. The return value is of type timestamp or interval with all
> fields that are less significant than the selected one set to zero (or
> one, for day and month).
> =========[snip]

> Atleast that's how I interpret the last parenthesis in the paragraph.

That seems an unlikely interpretation to me. I'm pretty sure 'day' in that text is meant as 'day of month', not as 'day of week'.

That aside, if fields are getting set to zero (or one for day and month) it would be a bad idea to set day of week to zero or one as well, as it's value should be derived from day, month and year (unless for example dow and week were specified and day of month was not).

The documentation doesn't explicitly say what a week would truncate to, but earlier in the documentation for extract() it explains it uses ISO-8601 when extracting weeks. It says there:

"By definition (ISO 8601), the first week of a year contains January 4 of that year. (The ISO-8601 week starts on Monday.)"
^^^^^^

It seems safe to assume date_trunc() uses the same ISO standard when truncating dates.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4b0fb5a211731686815181!