date_trunct() and start of week

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

2009/11/26 Thomas Kellerer <spam_eater@gmx.net>

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

I don't understand how date_trunc is giving you the day of the week. As

far as I'm aware it only reduces the precision of the date/time. What I
imagine you'd use is: extract(DOW from some_date). This won't be
locale-dependant. It will always be 0 (Sunday) - 6 (Saturday). There is
another way to get the day of the week which is to_char(some_date, 'D')
which is numbered 1 (Sunday) to 7 (Saturday).

Regards

Thom

2009/11/26 Thomas Kellerer <spam_eater@gmx.net>

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

Actually I think I misunderstood your post. You're looking for the *date*

of the first day of the week, not the day. My bad. :)

Thom

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:

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 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!

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