[kronolith] configuring kronolith for use w/ sql

[Chuck Hagenbuch]

Quoting Cliff Green <green@UMDNJ.EDU>:

> Can't say about Oracle, but PostgreSQL doesn't like it.

Fudge. Anyone have any ideas on a portable way of doing dates? Or do we have to 
rewrite the sql driver to just store unix timestamps?

-chuck

--
Charles Hagenbuch, <chuck@horde.org>
"What was and what may be lie, like children whose faces we cannot see, in the 
arms of silence. All we ever have is here, now." - Ursula K. Le Guin


>From  Tue Sep 18 15:53:40 2001
Return-Path: <liamr@umich.edu>
Mailing-List: contact kronolith-help@lists.horde.org; run by ezmlm
Delivered-To: mailing list kronolith@lists.horde.org
Received: (qmail 70689 invoked from network); 18 Sep 2001 20:01:14 -0000
Received: from berzerk.gpcc.itd.umich.edu (smtp@141.211.2.162)
  by clark.horde.org with SMTP; 18 Sep 2001 20:01:14 -0000
Received: from esperanto.web.itd.umich.edu (smtp@esperanto.web.itd.umich.edu [141.213.231.69])
        by berzerk.gpcc.itd.umich.edu (8.8.8/4.3-mailhub) with ESMTP id QAA06892
        for <kronolith@lists.horde.org>; Tue, 18 Sep 2001 16:01:11 -0400 (EDT)
Received: from localhost (liamr@localhost)
	by esperanto.web.itd.umich.edu (8.8.8/4.9.1-cyrus) with ESMTP id QAA15255
	for <kronolith@lists.horde.org>; Tue, 18 Sep 2001 16:01:11 -0400 (EDT)
X-Authentication-Warning: esperanto.web.itd.umich.edu: liamr owned process doing -bs
Date: Tue, 18 Sep 2001 16:01:11 -0400 (EDT)
From: Liam Hoekenga <liamr@umich.edu>
X-X-Sender:  <liamr@esperanto.web.itd.umich.edu>
To: <kronolith@lists.horde.org>
Message-ID: <Pine.SOL.4.33.0109181556540.15238-100000@esperanto.web.itd.umich.edu>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Subject: Re: [kronolith] configuring kronolith for use w/ sql

> SELECT DISTINCT e.event_id FROM
> kronolith_events WHERE calendar_id = 'liamr'
> AND event_start >= {ts '2001-09-01 00:00:00.000'}
> AND (event_end <= {ts '2001-09-30 23:59:59.999'}
> OR (event_recurEnddate <= {ts '2001-09-30 23:59:59.999'}
> AND event_recurType != 0))

I couldn't execute the sql from sqlplus either:

	ORA-00911: invalid character

Here's what our DBA had
to say about it...

---------- Forwarded message ----------
Date: Tue, 18 Sep 2001 15:53:40 -0400 (EDT)
From: Kasthuri
To: Liam Hoekenga <liamr@umich.edu>
Subject: Re: [kronolith] configuring kronolith for use w/ sql (fwd)

Liam, THe following sql should work provided event_start, event_end,
event_recurEnddate are all defined as date datatype. If those are varchar2
then you have to change the to_date function to to_char function. Also
there is one other problem. I don't know how you can store & retrieve from
oracle a column with fraction of a second like how you have it in the
query you sent me. I have taken the millisecond info from the following
sql. If you need to track millisecond & microsecond, you need to define an
another column to store just millisecond and concatenate the two columns
to get the full date& time. I'm not very sure about this. I will search
around and find out about this milli/micro second information.

SELECT DISTINCT e.event_id FROM
kronolith_events WHERE calendar_id = 'liamr'
AND event_start >= to_date('2001-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),
AND (event_end <= to_date('2001-09-30 23:59:59','YYYY-MM-DD HH24:MI:SS'),
OR (event_recurEnddate <= to_date('2001-09-30 23:59:59','YYYY-MM-DD
HH24:MI:SS')
AND event_recurType != 0))

-Kasthuri

On Tue, 18 Sep 2001, Liam Hoekenga wrote:

> Hey Kasthuri -
>
> Can you help me come up with a properly formatted oracle version of this
> sql query?
>
> SELECT DISTINCT e.event_id FROM
> kronolith_events WHERE calendar_id = 'liamr'
> AND event_start >= {ts '2001-09-01 00:00:00.000'}
> AND (event_end <= {ts '2001-09-30 23:59:59.999'}
> OR (event_recurEnddate <= {ts '2001-09-30 23:59:59.999'}
> AND event_recurType != 0))
>
> thanks!
> Liam