Thread: date_trunct() and start of week

Hi,

while using date_trunc('week', some_date) to get the date of the first day of the week I noticed that it was working as
expected:Monday is considered the start of the week.  

I assume this depends on some locale setting, but I can't figure out which it is, so I can make sure this is not
"accidently"changed. I tried changing LC_TIME (American_America) but that still returned Monday as the first day (my
understandingis that in the States Sunday is considered the start of the week) 

Any pointers are appreciated (did I miss it in the manual?)

Regards
Thomas

On Thursday 26 November 2009 1:59:05 pm Thomas Kellerer wrote:
> Hi,
>
> while using date_trunc('week', some_date) to get the date of the first day
> of the week I noticed that it was working as expected: Monday is considered
> the start of the week.
>
> I assume this depends on some locale setting, but I can't figure out which
> it is, so I can make sure this is not "accidently" changed. I tried
> changing LC_TIME (American_America) but that still returned Monday as the
> first day (my understanding is that in the States Sunday is considered the
> start of the week)
>
> Any pointers are appreciated (did I miss it in the manual?)
>
> Regards
> Thomas

From here:
http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

week

    The number of the week of the year that the day is in. By definition (ISO
8601), the first week of a year contains January 4 of that year. (The ISO-8601
week starts on Monday.) In other words, the first Thursday of a year is in week
1 of that year.



--
Adrian Klaver
aklaver@comcast.net

Adrian Klaver, 26.11.2009 23:15:
> On Thursday 26 November 2009 1:59:05 pm Thomas Kellerer wrote:
>> Hi,
>>
>> while using date_trunc('week', some_date) to get the date of the first day
>> of the week I noticed that it was working as expected: Monday is considered
>> the start of the week.
>>
>> I assume this depends on some locale setting, but I can't figure out which
>> it is, so I can make sure this is not "accidently" changed. I tried
>> changing LC_TIME (American_America) but that still returned Monday as the
>> first day (my understanding is that in the States Sunday is considered the
>> start of the week)
>>
>> Any pointers are appreciated (did I miss it in the manual?)
>>
>> Regards
>> Thomas
>
> From here:
> http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
>
> week
>
>     The number of the week of the year that the day is in. By definition (ISO
> 8601), the first week of a year contains January 4 of that year. (The ISO-8601
> week starts on Monday.) In other words, the first Thursday of a year is in week
> 1 of that year.
>

Thanks for the answer, I'm aware of the week numbering but that's not what I'm interested in.

When I pass e.g. today's date (27.11.) I want the *date* returned of the monday of that week (23.11.)

Which is what date_trunc('week', some_date) gives me.

That is not my question

I'm just curious which setting defines whether monday or sunday is considered the "first day in a week"

Regards
Thomas

On Fri, Nov 27, 2009 at 08:13, Thomas Kellerer <spam_eater@gmx.net> wrote:

[...]
> I'm just curious which setting defines whether monday or sunday is
> considered the "first day in a week"

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.


--
- Rikard

Rikard Bosnjakovic, 27.11.2009 08:49:
> [...]
>> I'm just curious which setting defines whether monday or sunday is
>> considered the "first day in a week"
>
> 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.

Hmm, I don't see that in there.

It just states that the field will be set to "zero". But does zero refer to a Monday or a Sunday?

Regards
Thomas

Hi,

not all to zero : "that are less significant than the selected one set
to zero (or one, for day and month)"

so

select extract('dow' from date_trunc('week', current_date))

returns always 1 (i think accordingly to ISO-8601)

see
http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
dow:
"The day of the week (0 - 6; Sunday is 0)"

regards
Thomas



Thomas Kellerer schrieb:
>
> Hmm, I don't see that in there.
> It just states that the field will be set to "zero". But does zero
> refer to a Monday or a Sunday?
> Regards
> Thomas
>
>

Thomas Markus, 27.11.2009 09:41:
> Hi,
>
> not all to zero : "that are less significant than the selected one set
> to zero (or one, for day and month)"
>
Sorry, I missed the "or one" part.

> see
> http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
> dow: "The day of the week (0 - 6; Sunday is 0)"

So essentially it *is* always returning Monday independently of any setting.

Thanks for your help

Regards
Thomas

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
ofweek'. 

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
zeroor one as well, as it's value should be derived from day, month and year (unless for example dow and week were
specifiedand day of month was not). 

The documentation doesn't explicitly say what a week would truncate to, but earlier in the documentation for extract()
itexplains 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!

Thomas Kellerer <spam_eater@gmx.net> writes:
> I'm just curious which setting defines whether monday or sunday is considered the "first day in a week"

A look at the source code (timestamptz_trunc) shows that truncation to
week start follows the ISO week conventions --- so weeks start on
Monday, regardless of locale.

Offhand I do not think that we pay attention to locale for any datetime
calculations.  However, there are other places that use Sunday for week
start, so it does matter which calculation you ask for ...

            regards, tom lane