On Thursday 05 February 2004 14:59, Raman Garg wrote:
> Hi Richard,
>
> What I am having is
>
> CREATE TABLE "customer_events" (
> "event_id" numeric (10) NOT NULL,
> "customer_id" numeric (10) NOT NULL,
> "event_name" varchar (100) ,
> "event_datetime" date ,
> "start_time" time ,
> "repeat_untill_date" date ,
> "send_before_time" time,
> "time_difference" time
> PRIMARY KEY ("event_id"))
>
> So my "send_before_time" is of datatype "time" only as you have assumed.
> only difference was, In your table you are having time_difference field as
> "interval" type.
>
> I am subtracting time field from a time field. i.e. start_time::time -
> send_before_time::time
> so creating (start_time::time - send_before_time::time) as start_pt is not
> making any difference at my side.
> (I hope I am getting your point what you have explained)
My point is that subtracting one time from another does not give you a time.
3:30pm (time) - 2:00pm (time) = 1h30m (interval)
If "send_before_time" is an interval (e.g. '1 hour and 30 minutes') you should
probably make it an interval.
If it is in fact a time (e.g. '2:00 pm') then it doesn't make sense to
subtract it.
Even if your solution works, I'd recommend getting your types right - it'll
save effort later.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
