Search Postgresql Archives

Re: date_trunct() and start of week

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux