Thread: Re: [GENERAL] datetime problems
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