Re: [GENERAL] Setting week starting day
On Thu, Mar 08, 2007 at 20:32:22 -0300, Jorge Godoy [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: As I said, it is easy with a function. :-) I was just curious to see if we had something like Oracle's NEXT_DAY function or something like what I described (SET BOW=4; -- makes Thursday the first day of week): If you are actually using date you can get the effect you want by adding a constant integer to the date in the date_trunc function. That seems pretty easy. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Setting week starting day
Bruno Wolff III [EMAIL PROTECTED] writes: On Thu, Mar 08, 2007 at 20:32:22 -0300, Jorge Godoy [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: As I said, it is easy with a function. :-) I was just curious to see if we had something like Oracle's NEXT_DAY function or something like what I described (SET BOW=4; -- makes Thursday the first day of week): If you are actually using date you can get the effect you want by adding a constant integer to the date in the date_trunc function. That seems pretty easy. I couldn't see where to specify that integer. Or, if it to sum it up to the date, something that calculates it automatically. http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC Adding an integer I'd still have to write the verifications (such as the one I mention below for Oracle's NEXT_DATE()) to get the desired result. Just to repeat my question: (I don't want to write a function, I can do that pretty easily... And I was asking if there existed some feature on the database that... It's just a curiosity) Given a date X it would return me the first day of the week so that I can make this first day an arbitrary day, e.g. Friday or Wednesday. Oracle's NEXT_DAY() gets closer to that, but would still require a few operations (checking if the returned date is before the given date or if after then subtract one week from this returned value, kind of a PREVIOUS_DATE()...). With a function I could make it easily, but then I'd have to wrap all calculations with that... It was just something to make life easier. From the answers I'm getting I see that there's no way to do that without a function and that I'm not missing any feature on PG with regards to that ;-) -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Setting week starting day
Jorge Godoy escribió: Just to repeat my question: (I don't want to write a function, I can do that pretty easily... And I was asking if there existed some feature on the database that... It's just a curiosity) Given a date X it would return me the first day of the week so that I can make this first day an arbitrary day, e.g. Friday or Wednesday. When you say it would return, what's the it? I wasn't proposing to use any function, just putting a simple expression in the SELECT's result list (and maybe the GROUP BY, etc). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Setting week starting day
I think you can coax the date_trunc function to give you a proper start day. I think it's more than adding an integer to your date, though. You also have to do some mod work after the function returns, I think. I agree that the point isn't that you can't do it with some effort, however. It's mainly that it's a bit linguistically unintuitive. It would be nice to have a start date as an argument to the function. Having said that, my own personal use of it will definitely be inside another wrapper function because I need database platform independence, so I need to abstract the function to look the same on all of my platforms. Jorge Godoy wrote: Bruno Wolff III [EMAIL PROTECTED] writes: On Thu, Mar 08, 2007 at 20:32:22 -0300, Jorge Godoy [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: As I said, it is easy with a function. :-) I was just curious to see if we had something like Oracle's NEXT_DAY function or something like what I described (SET BOW=4; -- makes Thursday the first day of week): If you are actually using date you can get the effect you want by adding a constant integer to the date in the date_trunc function. That seems pretty easy. I couldn't see where to specify that integer. Or, if it to sum it up to the date, something that calculates it automatically. http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC Adding an integer I'd still have to write the verifications (such as the one I mention below for Oracle's NEXT_DATE()) to get the desired result. Just to repeat my question: (I don't want to write a function, I can do that pretty easily... And I was asking if there existed some feature on the database that... It's just a curiosity) Given a date X it would return me the first day of the week so that I can make this first day an arbitrary day, e.g. Friday or Wednesday. Oracle's NEXT_DAY() gets closer to that, but would still require a few operations (checking if the returned date is before the given date or if after then subtract one week from this returned value, kind of a PREVIOUS_DATE()...). With a function I could make it easily, but then I'd have to wrap all calculations with that... It was just something to make life easier. From the answers I'm getting I see that there's no way to do that without a function and that I'm not missing any feature on PG with regards to that ;-) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Setting week starting day
Alvaro Herrera [EMAIL PROTECTED] writes:
Jorge Godoy escribió:
Just to repeat my question:
(I don't want to write a function, I can do that pretty easily... And I was
asking if there existed some feature on the database that... It's just a
curiosity)
Given a date X it would return me the first day of the week so that I can
make this first day an arbitrary day, e.g. Friday or Wednesday.
When you say it would return, what's the it?
The function that came with the database, the feature, the something. :-)
I wasn't proposing to use any function, just putting a simple expression
in the SELECT's result list (and maybe the GROUP BY, etc).
So I'm blind on how to do that. Maybe some CASE?
Here's what I was asking for (Sunday=0, Saturday=6, to remember ;-)):
testdb=# select current_date;
date
2007-03-09
(1 row)
testdb=# select current_date + '3 weeks'::interval;
?column?
-
2007-03-30 00:00:00
(1 row)
testdb=# select date_trunc('week', current_date + '3 weeks'::interval);
date_trunc
-
2007-03-26 00:00:00
(1 row)
testdb=# select date_part('dow', date_trunc('week', current_date + '3
weeks'::interval));
date_part
---
1
(1 row)
testdb=#
This is the standard behavior. It returns me the first monday. Now, if I had
the week starting on Wednesdays, I should get 2007-03-28 instead of
2007-03-26.
I can check in a function to see if the returned date is before or after my
desired week-start-day (as in Wednesdays, for example) and if date_part('dow',
date) is bigger than it return the value for Monday + 2 days, if it is lower
then return Monday - 5 days.
For example, again:
testdb=# select date_part('dow', current_date + '3 weeks'::interval);
date_part
---
5
(1 row)
testdb=# select date_trunc('week', current_date + '3 weeks'::interval) + '2
days'::interval;
?column?
-
2007-03-28 00:00:00
(1 row)
testdb=#
That would be the first day of the week in three weeks from now, with weeks
starting on Wednesdays.
If I had asked for this 3 days ago:
testdb=# select date_trunc('week', current_date - '3 days'::interval + '3
weeks'::interval);
date_trunc
-
2007-03-26 00:00:00
(1 row)
testdb=# select date_part('dow', current_date - '3 days'::interval + '3
weeks'::interval);
date_part
---
3
(1 row)
testdb=# select date_trunc('week', current_date - '3 days'::interval + '3
weeks'::interval) - '5 days'::interval;
?column?
-
2007-03-21 00:00:00
(1 row)
testdb=#
Then if it was Tuesday, the week three weeks from now would have started on
Wednesday, 2007-03-21.
It is not hard to calculate, as you can see... but it would be nice if
date_trunc('week', date) could do that directly. Even if it became
date_trunc('week', date, 4) or date_trunc('week', date, 'Wednesday') it
would be nice... :-) And that is what I was trying to ask ;-)
Thanks for your attention, Alvaro. :-)
--
Jorge Godoy [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Setting week starting day
On Fri, Mar 09, 2007 at 14:59:35 -0300,
Jorge Godoy [EMAIL PROTECTED] wrote:
It is not hard to calculate, as you can see... but it would be nice if
date_trunc('week', date) could do that directly. Even if it became
date_trunc('week', date, 4) or date_trunc('week', date, 'Wednesday') it
would be nice... :-) And that is what I was trying to ask ;-)
Use date_trunc('week', current_day + 1) and date_trunc('dow', current_day + 1)
to have a one day offset from the standard first day of the week.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Setting week starting day
Bruno Wolff III [EMAIL PROTECTED] writes:
On Fri, Mar 09, 2007 at 14:59:35 -0300,
Jorge Godoy [EMAIL PROTECTED] wrote:
It is not hard to calculate, as you can see... but it would be nice if
date_trunc('week', date) could do that directly. Even if it became
date_trunc('week', date, 4) or date_trunc('week', date, 'Wednesday') it
would be nice... :-) And that is what I was trying to ask ;-)
Use date_trunc('week', current_day + 1) and date_trunc('dow', current_day + 1)
to have a one day offset from the standard first day of the week.
I believe there's more than that... Probably the +1 should be outside the
date_trunc, anyway. It might help, but I still see the need to to do
calculations... Specially if it was Tuesday today...
neo=# select date_trunc('dow', current_date + 1);
ERRO: unidades de timestamp with time zone dow são desconhecidas
neo=# select date_part('dow', current_date + 1);
date_part
---
6
(1 row)
neo=# select date_trunc('week', current_date + 1);
date_trunc
2007-03-05 00:00:00-03
(1 row)
neo=# select date_trunc('week', current_date);
date_trunc
2007-03-05 00:00:00-03
(1 row)
neo=#
--
Jorge Godoy [EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Setting week starting day
On Fri, Mar 09, 2007 at 16:44:57 -0300,
Jorge Godoy [EMAIL PROTECTED] wrote:
Bruno Wolff III [EMAIL PROTECTED] writes:
On Fri, Mar 09, 2007 at 14:59:35 -0300,
Jorge Godoy [EMAIL PROTECTED] wrote:
It is not hard to calculate, as you can see... but it would be nice if
date_trunc('week', date) could do that directly. Even if it became
date_trunc('week', date, 4) or date_trunc('week', date, 'Wednesday') it
would be nice... :-) And that is what I was trying to ask ;-)
Use date_trunc('week', current_day + 1) and date_trunc('dow', current_day +
1)
to have a one day offset from the standard first day of the week.
I believe there's more than that... Probably the +1 should be outside the
date_trunc, anyway. It might help, but I still see the need to to do
calculations... Specially if it was Tuesday today...
No, it has to be inside the function so that the modular arithmetic is
applied to it.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Setting week starting day
It is not hard to calculate, as you can see... but it would be nice if
date_trunc('week', date) could do that directly. Even if it became
date_trunc('week', date, 4) or date_trunc('week', date, 'Wednesday')
it
would be nice... :-) And that is what I was trying to ask ;-)
Use date_trunc('week', current_day + 1) and date_trunc('dow', current_day
+ 1)
to have a one day offset from the standard first day of the week.
I believe there's more than that... Probably the +1 should be outside
the
date_trunc, anyway. It might help, but I still see the need to to do
calculations... Specially if it was Tuesday today...
Out of curiosity, why does the database need to know this, or to be able to
calculate it? There are lots of things that would be useful to me, if the
RDBMS I'm using at the time supported them (particularly certain statistical
functions - ANOVA, MANOVA, nonlinear least squares regression, time series
analysis, c.), but given that I can readily obtain these from other
software I use, and can if necessary put the requisite code in a middleware
component, I would rather have the PostgreSQL developer's focus on issues
central to having a good DB, such as ANSI standard compliance for SQL, or
robust pooling, c. and just leave me a mechanism for calling functions that
are external to the database for the extra stuff I need. I would prefer a
suite of applications that each does one thing well than a single
application that does a mediocre job on everything it allegedly supports.
What would be 'nice' and what is practical are often very different things.
I know what you're after is simple, but remember the good folk responsible
for PostgreSQL have only finite time available to work on it, and thus, when
they're making choices about priorities, I'd rather they ignore even simple
ancillary stuff and focus on what really matters.
I just recently finished a project in which the data processing needed
information similar to what you're after, but instead of doing it in the
database, we opted to do it in the Perl script I wrote that fed data to the
database. In fact, it wasn't so much the day of the week that mattered to
the processing algorithm but the resulting dates for the immediately
preceding business day and the immediately following business day. It was
those dates we fed to the database rather than the weekday. There are
several Perl packages (see CPAN) supporting this kind of calculation. These
are generally outstanding (and would probably be useful if you want to
create your own stored function implemented in Perl), but you may have to
customize them by providing additional configuration information such as
timezone and statutory and religious holidays if you need to determine
business days in addition to just the day of the week. the day of the week
can be obtained in Perl with a single function call!
I just took a quick break to read about the date functions available within
PostgreSQL, and while apparently nice, you have much greater flexibility,
and many more functions, in these Perl packages I mentioned. If you just
want a function call, I'd suggest you create a function that just dispatches
a call to the Perl function that best meets your needs. In a sense, you are
not really rolling your own. You're just dispatching the call to a function
in a Perl package.
Cheers
Ted
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Setting week starting day
Ted, my reason for asking the question that I believe precipitated this
thread was that I wanted a single sql statement that aggregated time
data by week. Yes, I could do the aggregation subsequently in my own
client side code, but it's easier and less error prone to have it done
by the server.
Ted Byers wrote:
It is not hard to calculate, as you can see... but it would be nice if
date_trunc('week', date) could do that directly. Even if it became
date_trunc('week', date, 4) or date_trunc('week', date,
'Wednesday') it
would be nice... :-) And that is what I was trying to ask ;-)
Use date_trunc('week', current_day + 1) and date_trunc('dow',
current_day + 1)
to have a one day offset from the standard first day of the week.
I believe there's more than that... Probably the +1 should be
outside the
date_trunc, anyway. It might help, but I still see the need to to do
calculations... Specially if it was Tuesday today...
Out of curiosity, why does the database need to know this, or to be
able to calculate it? There are lots of things that would be useful
to me, if the RDBMS I'm using at the time supported them (particularly
certain statistical functions - ANOVA, MANOVA, nonlinear least squares
regression, time series analysis, c.), but given that I can readily
obtain these from other software I use, and can if necessary put the
requisite code in a middleware component, I would rather have the
PostgreSQL developer's focus on issues central to having a good DB,
such as ANSI standard compliance for SQL, or robust pooling, c. and
just leave me a mechanism for calling functions that are external to
the database for the extra stuff I need. I would prefer a suite of
applications that each does one thing well than a single application
that does a mediocre job on everything it allegedly supports. What
would be 'nice' and what is practical are often very different things.
I know what you're after is simple, but remember the good folk
responsible for PostgreSQL have only finite time available to work on
it, and thus, when they're making choices about priorities, I'd rather
they ignore even simple ancillary stuff and focus on what really matters.
I just recently finished a project in which the data processing needed
information similar to what you're after, but instead of doing it in
the database, we opted to do it in the Perl script I wrote that fed
data to the database. In fact, it wasn't so much the day of the week
that mattered to the processing algorithm but the resulting dates for
the immediately preceding business day and the immediately following
business day. It was those dates we fed to the database rather than
the weekday. There are several Perl packages (see CPAN) supporting
this kind of calculation. These are generally outstanding (and would
probably be useful if you want to create your own stored function
implemented in Perl), but you may have to customize them by providing
additional configuration information such as timezone and statutory
and religious holidays if you need to determine business days in
addition to just the day of the week. the day of the week can be
obtained in Perl with a single function call!
I just took a quick break to read about the date functions available
within PostgreSQL, and while apparently nice, you have much greater
flexibility, and many more functions, in these Perl packages I
mentioned. If you just want a function call, I'd suggest you create a
function that just dispatches a call to the Perl function that best
meets your needs. In a sense, you are not really rolling your own.
You're just dispatching the call to a function in a Perl package.
Cheers
Ted
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Setting week starting day
- Original Message - From: Omar Eljumaily [EMAIL PROTECTED] To: Ted Byers [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Friday, March 09, 2007 5:00 PM Subject: Re: [GENERAL] Setting week starting day Ted, my reason for asking the question that I believe precipitated this thread was that I wanted a single sql statement that aggregated time data by week. Yes, I could do the aggregation subsequently in my own client side code, but it's easier and less error prone to have it done by the server. I don't buy the suggestion that server side code is less error prone that client side code, but be that as it may, we're talking about a function that has one line of code. And given what you just said, you don't want the day of the week, you want a function that returns the week of the year. This can be had from the same Perl functions I mentioned before, with a minor alteration in how you call it. my suggestion would be to create that one line function that invokes the relevant Perl function, which can then be invoked in your select statement (presumably with a group clause to avoid mixing data from different years). It should take about ten to fifteen minutes to write and test? Ted ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Setting week starting day
Bruno Wolff III [EMAIL PROTECTED] writes: No, it has to be inside the function so that the modular arithmetic is applied to it. Then there's the error I've shown from your command. Can you give me a working one? This was with PostgreSQL 8.2.3. -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Setting week starting day
Ted Byers [EMAIL PROTECTED] writes: Out of curiosity, why does the database need to know this, or to be able to calculate it? There are lots of things that would be useful to me, if the It was a curiosity. But it would make working with some dates easier. I've given some examples but if you really want I may search for the messages and repost them for you. RDBMS I'm using at the time supported them (particularly certain statistical functions - ANOVA, MANOVA, nonlinear least squares regression, time series analysis, c.), but given that I can readily obtain these from other software I use, and can if necessary put the requisite code in a middleware component, I would rather have the PostgreSQL developer's focus on issues central to You can have those using R and plR inside the database. ;-) having a good DB, such as ANSI standard compliance for SQL, or robust pooling, c. and just leave me a mechanism for calling functions that are external to the database for the extra stuff I need. I would prefer a suite of applications that each does one thing well than a single application that does a mediocre job on everything it allegedly supports. What would be 'nice' and what is practical are often very different things. I know what you're after is simple, but remember the good folk responsible for PostgreSQL have only finite time available to work on it, and thus, when they're making choices about priorities, I'd rather they ignore even simple ancillary stuff and focus on what really matters. If I have to do calculations with dates inside the database the worst thing I'd like to do was retrieving part of it, going to some external code, coming back to the database and so on. If there was something inside the database then I'd really like to know and use it. I don't see how worse it would be when compared to other non-ANSI extensions that are already available. I just recently finished a project in which the data processing needed information similar to what you're after, but instead of doing it in the database, we opted to do it in the Perl script I wrote that fed data to the database. In fact, it wasn't so much the day of the week that mattered to the There's no feeding here. Imagine that I'm filtering huge selects to be processed externaly. I wouldn't like to get some millions of rows instead of hundreds or a few thousands of them. processing algorithm but the resulting dates for the immediately preceding business day and the immediately following business day. It was those dates we fed to the database rather than the weekday. There are several Perl packages (see CPAN) supporting this kind of calculation. These are generally I know Perl. I have already thought it for IBM... ;-) outstanding (and would probably be useful if you want to create your own stored function implemented in Perl), but you may have to customize them by providing additional configuration information such as timezone and statutory and religious holidays if you need to determine business days in addition to just the day of the week. the day of the week can be obtained in Perl with a single function call! As in several other languages. Even in plpgsql, with simple calculations like I've shown. As I said, writing a function for that is simple enough and I just wanted to know if there was anything that could be done by the database. I never asked for any new implementation. I just took a quick break to read about the date functions available within PostgreSQL, and while apparently nice, you have much greater flexibility, and many more functions, in these Perl packages I mentioned. If you just want a function call, I'd suggest you create a function that just dispatches a call to the Perl function that best meets your needs. In a sense, you are not really rolling your own. You're just dispatching the call to a function in a Perl package. And to do that you have to write a function... -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Setting week starting day
Ted Byers [EMAIL PROTECTED] writes:
I don't buy the suggestion that server side code is less error prone that
client side code, but be that as it may, we're talking about a function that
has one line of code. And given what you just said, you don't want the day of
the week, you want a function that returns the week of the year. This can be
had from the same Perl functions I mentioned before, with a minor alteration
in how you call it. my suggestion would be to create that one line function
that invokes the relevant Perl function, which can then be invoked in your
select statement (presumably with a group clause to avoid mixing data from
different years). It should take about ten to fifteen minutes to write and
test?
There's no need to use Perl.
neo=# select extract('week' from now());
date_part
---
10
(1 registro)
neo=#
Today is a day at the tenth week of the year.
--
Jorge Godoy [EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Setting week starting day
On Fri, Mar 09, 2007 at 20:13:11 -0300,
Jorge Godoy [EMAIL PROTECTED] wrote:
Bruno Wolff III [EMAIL PROTECTED] writes:
No, it has to be inside the function so that the modular arithmetic is
applied to it.
Then there's the error I've shown from your command. Can you give me a
working one? This was with PostgreSQL 8.2.3.
postgres=# select date_trunc('week', current_date + 1);
date_trunc
2007-03-05 00:00:00-06
(1 row)
It turns out DOW isn't available for date_trunc. You can probably use
extract to get what you want. You probably should check that it works
at DST transitions, since the date value is cast to a timestamp and
if DST transitions happen at in your time zone, you might get an
unexpected answer.
postgres=# select extract(dow from current_date + 1);
date_part
---
6
(1 row)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Setting week starting day
Bruno Wolff III [EMAIL PROTECTED] writes:
On Fri, Mar 09, 2007 at 20:13:11 -0300,
Jorge Godoy [EMAIL PROTECTED] wrote:
Bruno Wolff III [EMAIL PROTECTED] writes:
No, it has to be inside the function so that the modular arithmetic is
applied to it.
Then there's the error I've shown from your command. Can you give me a
working one? This was with PostgreSQL 8.2.3.
postgres=# select date_trunc('week', current_date + 1);
date_trunc
2007-03-05 00:00:00-06
(1 row)
It turns out DOW isn't available for date_trunc. You can probably use
extract to get what you want. You probably should check that it works
at DST transitions, since the date value is cast to a timestamp and
if DST transitions happen at in your time zone, you might get an
unexpected answer.
postgres=# select extract(dow from current_date + 1);
date_part
---
6
(1 row)
But how to get the date if the first day of the week is a Wednesday? This
example is like the ones I've sent with separate queries that needed being
combined -- in a function, probably -- to get the desired result.
--
Jorge Godoy [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Setting week starting day
On Fri, Mar 09, 2007 at 23:07:26 -0300,
Jorge Godoy [EMAIL PROTECTED] wrote:
But how to get the date if the first day of the week is a Wednesday? This
example is like the ones I've sent with separate queries that needed being
combined -- in a function, probably -- to get the desired result.
If you want to group on weeks that start on Wednesdays add 5.
postgres=# select date_trunc('week', '2007-03-07'::date + 5);
date_trunc
2007-03-12 00:00:00-05
(1 row)
postgres=# select date_trunc('week', '2007-03-06'::date + 5);
date_trunc
2007-03-05 00:00:00-06
(1 row)
postgres=# select date_trunc('week', '2007-03-08'::date + 5);
date_trunc
2007-03-12 00:00:00-05
(1 row)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Setting week starting day
But you're always returning Monday, right? Your grouping will be
correct, but to get the actual truncation date, you have to subtract back.
select (date_trunc('week', '2007-03-07'::date + 5)::date-5);
select (date_trunc('week', '2007-03-06'::date + 5)::date-5);
select (date_trunc('week', '2007-03-08'::date + 5)::date-5);
Bruno Wolff III wrote:
On Fri, Mar 09, 2007 at 23:07:26 -0300,
Jorge Godoy [EMAIL PROTECTED] wrote:
But how to get the date if the first day of the week is a Wednesday? This
example is like the ones I've sent with separate queries that needed being
combined -- in a function, probably -- to get the desired result.
If you want to group on weeks that start on Wednesdays add 5.
postgres=# select date_trunc('week', '2007-03-07'::date + 5);
date_trunc
2007-03-12 00:00:00-05
(1 row)
postgres=# select date_trunc('week', '2007-03-06'::date + 5);
date_trunc
2007-03-05 00:00:00-06
(1 row)
postgres=# select date_trunc('week', '2007-03-08'::date + 5);
date_trunc
2007-03-12 00:00:00-05
(1 row)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Setting week starting day
Bruno Wolff III [EMAIL PROTECTED] writes:
On Fri, Mar 09, 2007 at 23:07:26 -0300,
Jorge Godoy [EMAIL PROTECTED] wrote:
But how to get the date if the first day of the week is a Wednesday? This
example is like the ones I've sent with separate queries that needed being
combined -- in a function, probably -- to get the desired result.
If you want to group on weeks that start on Wednesdays add 5.
I believe you either missed my post with several queries showing what I wanted
or you didn't understand the point.
If I run this query:
select date_trunc('week', '2007-03-08'::date + 5);
it fails even for that date. The correct answer, would be 2007-03-07 and not
2007-03-12. I want the first day of the week to be Wednesday and hence I want
the Wednesday for the week the date is in. (Wednesday was arbitrarily chosen,
it could be Thursday, Tuesday, Friday, etc.)
postgres=# select date_trunc('week', '2007-03-07'::date + 5);
date_trunc
2007-03-12 00:00:00-05
(1 row)
This should be 2007-03-07 since 2007-03-07 *is* a Wednesday and that's when
the week starts.
postgres=# select date_trunc('week', '2007-03-06'::date + 5);
date_trunc
2007-03-05 00:00:00-06
(1 row)
This should be 2007-02-28 since this is the first day of the week for the week
that starts on Wednesday 2007-02-28 and ends on 2007-03-06.
postgres=# select date_trunc('week', '2007-03-08'::date + 5);
date_trunc
2007-03-12 00:00:00-05
(1 row)
This should return the same date as the first query (2007-03-07). 2007-03-12
is a Monday, and weeks should always start on Wednesday on my arbitrary
question.
This is why I can't envision a simple query for that but it is easy with a
function.
Again, the function should do something like:
- make the date calculation (e.g. add some interval or nothing at all...)
- get the resulting 'dow'
- if it is than the arbitrary day that was determined to be the first
day of the week (Wednesday on my example), then return
date_trunc('week') + 2 days (2 for moving from Monday to Wednesday,
for different first days the shift should be different)
- if it is than the arbitrary day that was determined to be the first
day of the week (Wednesday, again), then return date_trunc('week') -
5 days (-5 for moving from Monday to the previous Wednesday)
The result when asked for the first day should always be the Wednesday that is
equal to the date or that ocurred right before it. It is the same idea that
is implemented today that returns Monday, but instead of Monday I want another
day that in my posts happened to be exemplified by Wednesday.
I don't want you to expend your time. It was just a question that got
answered indirectly with a there's no way to do that without using a
function due to the complexity above and the lack of such feature in
PostgreSQL. It is simple to have it as a function, though.
I don't know any RDBMS that implements that. All of them require some
operations to get the desired result.
Be seeing you,
--
Jorge Godoy [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Setting week starting day
Omar Eljumaily [EMAIL PROTECTED] writes:
But you're always returning Monday, right? Your grouping will be correct, but
to get the actual truncation date, you have to subtract back.
select (date_trunc('week', '2007-03-07'::date + 5)::date-5);
select (date_trunc('week', '2007-03-06'::date + 5)::date-5);
select (date_trunc('week', '2007-03-08'::date + 5)::date-5);
Indeed. This gives the correct result. So, we can change '5' for:
7 - ('dow desired' - 1)
Replacing the above queries, then:
# select (date_trunc('week', '2007-03-07'::date + (7 - (3 - 1::date - (7 -
(3 - 1));
?column?
2007-03-07
(1 row)
# select (date_trunc('week', '2007-03-06'::date + (7 - (3 - 1::date - (7 -
(3 - 1));
?column?
2007-02-28
(1 row)
# select (date_trunc('week', '2007-03-08'::date + (7 - (3 - 1::date - (7 -
(3 - 1));
?column?
2007-03-07
(1 row)
Parameterizing the desired day shouldn't be hard. ;-)
We subtract one from the desired day because PostgreSQL returns '1' for the
date_part('week') considering Mondays as the first day of the week.
Thanks, Omar. This makes the function easier to write. I hope it also solves
your problem.
Be seeing you,
--
Jorge Godoy [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Setting week starting day
On Sat, Mar 10, 2007 at 00:03:04 -0300,
Jorge Godoy [EMAIL PROTECTED] wrote:
If I run this query:
select date_trunc('week', '2007-03-08'::date + 5);
it fails even for that date. The correct answer, would be 2007-03-07 and not
2007-03-12. I want the first day of the week to be Wednesday and hence I want
the Wednesday for the week the date is in. (Wednesday was arbitrarily chosen,
it could be Thursday, Tuesday, Friday, etc.)
If for some reason you actually need to display the date of the first day
of the week, rather than just group by it, then subtract the number of
days that were added inside, on the outside. Because date_trunc returns
a timestamp with timezone, you need to subtract an interval (or cast
back to date and subtract an integer). If you are getting the '5' from
somewhere hard coded you might want to use (5 * '1 day'::interval) rather
than '5 days'::interval .
So you would use:
select date_trunc('week', '2007-03-08'::date + 5) - '5 days'::interval;
postgres=# select date_trunc('week', '2007-03-08'::date + 5) - '5
days'::interval;
?column?
2007-03-07 00:00:00-06
(1 row)
postgres=# select date_trunc('week', '2007-03-07'::date + 5) - '5
days'::interval;
?column?
2007-03-07 00:00:00-06
(1 row)
postgres=# select date_trunc('week', '2007-03-06'::date + 5) - '5
days'::interval;
?column?
2007-02-28 00:00:00-06
(1 row)
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Setting week starting day
Alvaro Herrera [EMAIL PROTECTED] writes:
Jorge Godoy escribió:
I mean, if I wanted to do the above but instead of Sunday or Monday as the
starting day I'd like using Fridays or Wednesdays...
Is it possible? Writing a new function shouldn't be too hard -- it's a
matter
of truncating the week on a day and shifting the date forward or backward --,
but something like a SET bow=5 (to make the API consistent with the 'dow'
that already exists) would be really great!
Is it not just a matter of adding a constant and then taking modulo 7?
As I said, it is easy with a function. :-) I was just curious to see if we
had something like Oracle's NEXT_DAY function or something like what I
described (SET BOW=4; -- makes Thursday the first day of week):
NEXT_DAY
Syntax
Purpose
Returns the date of the first weekday named by char that is later than the
date d. The argument char must be a day of the week in the date language of
your session, either the full name or the abbreviation. The minimum number of
letters required is the number of letters in the abbreviated version. Any
characters immediately following the valid abbreviation are ignored. The
return value has the same hours, minutes, and seconds component as the
argument d.
Example
This example returns the date of the next Tuesday after March 15, 1998.
SELECT NEXT_DAY('15-MAR-98','TUESDAY') NEXT DAY
FROM DUAL;
NEXT DAY
-
16-MAR-98
So, I'd have something like: SELECT NEXT_DAY(now()+'5 weeks'::INTERVAL,
'THURSDAY'); to give me the next Thursday 5 weeks from now.
Be seeing you,
--
Jorge Godoy [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
