Received: from mx99.openu.ac.il (arbel.openu.ac.il [147.233.32.4]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA29162 for ; Tue, 13 Jul 1999 12:06:43 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from talmon.openu.ac.il (talmon.openu.ac.il [147.233.32.11]) by mx99.openu.ac.il (Postfix) with ESMTP for id 42FA34B637; Tue, 13 Jul 1999 19:06:42 +0300 (IDT) Received: from [147.233.159.109] (mac-herouth.pc.openu.ac.il) by talmon.openu.ac.il (Netscape Messaging Server 3.54) with ESMTP id AAA2D91; Tue, 13 Jul 1999 19:09:50 +0300 X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: <3.0.5.32.19990713082450.00927100@poptop.llnl.gov> References: <378AE74F.C804CB78@tissat.es> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Tue, 13 Jul 1999 19:06:54 +0300 To: Steven Bradley , "Esteban Chiner Sanz" From: Herouth Maoz Subject: Re: [SQL] Problem with timestamp and primary key. Cc: pgsql-sql@postgreSQL.org At 18:24 +0300 on 13/07/1999, Steven Bradley wrote: > I have the same problem, except that my TIMESTAMP column is not the PK; > instead it's just a column that I wanted to index. Unfortunately, there is > no solution since Postgres currently does not support indexes on TIMESTAMPS > (Postgres automatically tries to create an index on all PKs) and does not > provide JDBC access to DATETIME columns. For your particular situation you > might try placing the TIMESTAMP column outside the PK (and not have it > indexed) and then use a surrogate key with a sequence. This isn't entirely > normalized, but I've seen worse! I think I have a good solution. The column in the database should be datetime, because that's what you can index. Right? The column returned from a query should be timestamp, because that's what JDBC parses correctl. Right? So, when you are doing something like: SELECT the_datetime_col, other_cols FROM the_table WHERE the_datetime_col = 'some value'; You should put a function that converts the_datetime_col to timestamp. But only in the returned columns! If you put a conversion function in the WHERE clause, the index will not be used. But how to convert? timestamp( the_datetime_col) doesn't work (Hey, it's a bug. A function exists which is supposed to be doing this). Well, define it yourself: testing=> CREATE FUNCTION to_stamp( datetime ) RETURNS timestamp AS testing-> 'SELECT timestamp_in( datetime_out( $1 ) ) WHERE $1 IS NOT NULL' testing-> LANGUAGE 'sql'; CREATE (I found that without WHERE clause it will bug on NULL input, so don't leave it out). testing=> select dt, to_stamp( dt ) as ts from test2; dt |ts ----------------------------+---------------------- Sat May 15 13:30:00 1948 IST|1948-05-15 13:30:00+02 Wed Jan 15 16:00:00 1969 IST|1969-01-15 16:00:00+02 Sun Oct 21 02:00:00 1973 IST|1973-10-21 02:00:00+02 Tue Jul 13 14:05:00 1999 IDT|1999-07-13 14:05:00+03 (4 rows) (dt is a datetime column). Thus, your query should be: SELECT to_stamp( the_datetime_col ), other_cols FROM the_table WHERE the_datetime_col = 'some value'; HTH, Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma