re: Help...(summerd@cs.unm.edu)

I'd like to issue a query that lists all the rows of a table whose 'date'
columns(its type is datetime) is falls in a month, i.e. these rows were
inserted in , say August. How can I do that?

When I bugged with that problem I found an interesting bug:
elmu=> select date_trunc('month','now'::datetime);
date_trunc
-----------------------------
Thu Oct 01 00:00:00 1998 CEST
(1 row)

That's OK.

elmu=> select date_trunc('month','now'::datetime) + timespan('1
month'::reltime)
;
?column?
----------------------------
Sat Oct 31 23:00:00 1998 CET
(1 row)

Why not Nov 1?

--------------------------------------------------------------------------------
Sebesty���n Zolt���n AKA Memphisto It all seems so stupid,
it makes me want to give up.
szoli@neumann.cs.elte.hu But why should I give up,
when it all seems so stupid?

MAKE INSTALL NOT WAR And please avoid Necrosoft Widows

At 13:50 +0200 on 21/10/98, Memphisto wrote:

I'd like to issue a query that lists all the rows of a table whose 'date'
columns(its type is datetime) is falls in a month, i.e. these rows were
inserted in , say August. How can I do that?

SELECT *
FROM the_table
WHERE the_date BETWEEN '1998-08-01' AND '1998-08-31';

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

At 15:45 +0200 on 21/10/98, Memphisto wrote:

I'm terrible sorry, I meant to type login_start and no login_time which is
and intervallum. Thanks again for your help.

By the way, I was thinking, and my WHERE clause may not catch datetimes
which are on Aug 31, 1998, in the middle of the day. Better use:

WHERE the_date >= '1998-08-01' AND the_date < '1998-09-01';

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

On Wed, 21 Oct 1998, Herouth Maoz wrote:

At 15:45 +0200 on 21/10/98, Memphisto wrote:

I'm terrible sorry, I meant to type login_start and no login_time which is
and intervallum. Thanks again for your help.

By the way, I was thinking, and my WHERE clause may not catch datetimes
which are on Aug 31, 1998, in the middle of the day. Better use:

WHERE the_date >= '1998-08-01' AND the_date < '1998-09-01';

Thanks.
Another problem. I issued the following query:
select * from annex_log where login_start
between
(select date_trunc('month','now'::datetime)) and
(select date_trunc('month','now'::datetime)) +
timespan('1 month'::reltime);

but postgresql said there's a parse error near select
while the following query did work

select * from annex_log where login_start >=
(select date_trunc('month','now'::datetime)) and login_start <
(select date_trunc('month','now'::datetime) +
timespan('1 month'::reltime));

Why?

--------------------------------------------------------------------------------
Sebesty���n Zolt���n AKA Memphisto It all seems so stupid,
it makes me want to give up.
szoli@neumann.cs.elte.hu But why should I give up,
when it all seems so stupid?

MAKE INSTALL NOT WAR And please avoid Necrosoft Widows

At 17:14 +0200 on 21/10/98, Memphisto wrote:

Another problem. I issued the following query:
select * from annex_log where login_start
between
(select date_trunc('month','now'::datetime)) and
(select date_trunc('month','now'::datetime)) +
timespan('1 month'::reltime);

but postgresql said there's a parse error near select
while the following query did work

select * from annex_log where login_start >=
(select date_trunc('month','now'::datetime)) and login_start <
(select date_trunc('month','now'::datetime) +
timespan('1 month'::reltime));

Why?

Probably because the parentheses are not balanced in the first query. But
why the subqueries, anyway? Why not simply:

SELECT * FROM annex_log
WHERE login_start
BETWEEN date_trunc('month','now'::datetime)
AND ( date_trunc('month','now'::datetime) + '1 month'::timespan);

(Note that I also removed the redundant type conversion you did on '1 month').

Again, I recommend doing the >=, < thing rather than 'between', because
'between' will also allow the actual value of 1998-09-01 (for example) to
be included. It's a close interval, rather than a half-open one.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

Probably because the parentheses are not balanced in the first query. But
why the subqueries, anyway? Why not simply:

SELECT * FROM annex_log
WHERE login_start
BETWEEN date_trunc('month','now'::datetime)
AND ( date_trunc('month','now'::datetime) + '1 month'::timespan);

(Note that I also removed the redundant type conversion you did on '1 month').

Noted, thanks. I'm a newbie in postgreSQL and happy that these types and
functions exist, but I think the documentation is a bit spartan(lacks a
lot of pieces of information) and depend on those bits that are there in
the documentation.

Again, I recommend doing the >=, < thing rather than 'between', because
'between' will also allow the actual value of 1998-09-01 (for example) to
be included. It's a close interval, rather than a half-open one.

That's right, I didn't know about the behaviour of 'between'. I'm going
to use '>=' and '<'.

Another question. Is there way to these truncation to weeks instead of
months. As far as I know, postgreSQL does not support it.

--------------------------------------------------------------------------------
Sebesty���n Zolt���n AKA Memphisto It all seems so stupid,
it makes me want to give up.
szoli@neumann.cs.elte.hu But why should I give up,
when it all seems so stupid?

MAKE INSTALL NOT WAR And please avoid Necrosoft Widows

At 17:48 +0200 on 21/10/98, Memphisto wrote:

Noted, thanks. I'm a newbie in postgreSQL and happy that these types and
functions exist, but I think the documentation is a bit spartan(lacks a
lot of pieces of information) and depend on those bits that are there in
the documentation.

The correction for the subqueries is not specific to PostgreSQL. My guess
is that you are not quite familiar with SQL in general. Perhaps you should
consider buying a book.

Another question. Is there way to these truncation to weeks instead of
months. As far as I know, postgreSQL does not support it.

Well, how does one truncate to weeks? Are Sundays in the next week or the
last week? This differs from culture to culture. For some, Friday is the
last day of the week...

Let's rephrase the question. You want to know if date D1 is within the same
week as date D2, given that a week starts on Sunday?

Well, calculate the day of week for Date D1, by taking date_part( 'dow', D1
). The result is an integer between 0 and 6.

Subtract that number of days from D1:

D1 - timespan( text( date_part( 'dow', D1 ) ) || ' days' );

You get the date of the Sunday on or before D1.

Do the same for D2. You get the date of the Sunday on or before D2.

Now see if you got the same date in both calculations...

Intricate, but you can define this in an SQL function, and avoid queries
which have too many parantheses for comfort.

CREATE FUNCTION sunday_of_date( datetime ) RETURNS datetime
AS 'SELECT $1 - timespan( text( date_part( ''dow'', $1 ) ) || '' days'' )'
LANGUAGE 'sql';

(Can't test this myself, because I only have Postgres 6.3.1, which doesn't
allow converting integer to text.)

Now, your queries will be something like:

SELECT *
FROM annex_log
WHERE sunday_of_date( login_start ) = sunday_of_date( 'now' );

If a week doesn't start on Sunday in your culture, you'll have to take the
result of the dow, add the appropriate number of days and take the modulo
of seven...

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma