How to join two tables while one is using Date and another is using Timestamp?
Mullin Yu
Timestamp on Oracle, for example,
select * from Orders o, AuditLog a
where o.TradeDate = a.EventTs
Orders
=======
TradeDate (Date)
e.g. 14-JUN-08
AuditLog
=========
EventTs (Timestamp)
e.g. 12-JUN-08 10.28.53.083000000 AM
Thanks!
Regards
steph
by removing the time component of the time stamp like:
select * from Orders o, AuditLog a
where o.TradeDate = trunc(a.EventTs)
Walt
Joining on a timestamp and date field? Man that's ugly. Much better to
fk the auditlog to the orders table and do the lookup by the pk of the
orders table. Assuming you can't change this horrific data design:
Using trunc() is one way to do it, but as written it'll only work if the
tradedate field has no time component (i.e. all values are midnight).
Depending on whether you want to join on the same day, or the same
second, look at the second parameter to trunc() (or maybe even consider
using round()). Of course, the query will be dog slow with these functions.
Have you tried letting the implicit conversion do it's magic? It
*should* simply snip off fractional seconds when converting timestamp to
a date, but I haven't tried this.
//Walt
Mladen Gogala
ry will be dog slow with these
> functions.
>
> Have you tried letting the implicit conversion do it's magic? It
> *should* simply snip off fractional seconds when converting timestamp to
> a date, but I haven't tried this.
>
Also, with the advent of functional indexes, joining over to_char
shouldn't be such a problem.
--
Mladen Gogala
http://mgogala.freehostia.com
Walt
> On Thu, 12 Jun 2008 16:20:16 -0400, Walt wrote:
> ...query will be dog slow with these
>> functions.
>
> Also, with the advent of functional indexes, joining over to_char
> shouldn't be such a problem.
Agreed. Once whatever combination of to_char, trunc and round is seen
to give the desired results it can be made into a function based index
to give better performance.
The problem is I don't see how anything is really going to work
locic-wise. The OP is trying to join an "orders" table to an audit log
table based on time stamp information. What happens when two orders
come in at the same time?
//Walt
Mladen Gogala
> What happens when two orders
> come in at the same time?
Sales rep delays one customer until there is no more "duplicate key in
index" issue or the customer dies trying.