Mark Forums Read

How to join two tables while one is using Date and another is usingTimestamp?

Go Back

comp.databases.oracle.server comp.databases.oracle.server

Reply

Page 1 of 3

1

#1

Old

Mullin Yu

Posts: n/a

How to join two tables while one is using Date and another is usingTimestamp? - 06-12-2008 , 06:06 AM

How to join two tables while one is using Date and another is using
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

Reply With Quote

#2

Old

steph

Posts: n/a

Re: How to join two tables while one is using Date and another isusing Timestamp? - 06-12-2008 , 07:26 AM

On 12 Jun., 13:06, Mullin Yu <mullin... (AT) gmail (DOT) com> wrote:

Quote:

How to join two tables while one is using Date and another is using
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

by removing the time component of the time stamp like:

select * from Orders o, AuditLog a
where o.TradeDate = trunc(a.EventTs)

Reply With Quote

#3

Old

steph

Posts: n/a

Re: How to join two tables while one is using Date and another isusing Timestamp? - 06-12-2008 , 07:26 AM

On 12 Jun., 13:06, Mullin Yu <mullin... (AT) gmail (DOT) com> wrote:

Quote:

How to join two tables while one is using Date and another is using
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

by removing the time component of the time stamp like:

select * from Orders o, AuditLog a
where o.TradeDate = trunc(a.EventTs)

Reply With Quote

#4

Old

steph

Posts: n/a

Re: How to join two tables while one is using Date and another isusing Timestamp? - 06-12-2008 , 07:26 AM

On 12 Jun., 13:06, Mullin Yu <mullin... (AT) gmail (DOT) com> wrote:

Quote:

How to join two tables while one is using Date and another is using
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

by removing the time component of the time stamp like:

select * from Orders o, AuditLog a
where o.TradeDate = trunc(a.EventTs)

Reply With Quote

#5

Old

steph

Posts: n/a

Re: How to join two tables while one is using Date and another isusing Timestamp? - 06-12-2008 , 07:26 AM

On 12 Jun., 13:06, Mullin Yu <mullin... (AT) gmail (DOT) com> wrote:

Quote:

How to join two tables while one is using Date and another is using
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

by removing the time component of the time stamp like:

select * from Orders o, AuditLog a
where o.TradeDate = trunc(a.EventTs)

Reply With Quote

#6

Old

steph

Posts: n/a

Re: How to join two tables while one is using Date and another isusing Timestamp? - 06-12-2008 , 07:26 AM

On 12 Jun., 13:06, Mullin Yu <mullin... (AT) gmail (DOT) com> wrote:

Quote:

How to join two tables while one is using Date and another is using
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

by removing the time component of the time stamp like:

select * from Orders o, AuditLog a
where o.TradeDate = trunc(a.EventTs)

Reply With Quote

#7

Old

steph

Posts: n/a

Re: How to join two tables while one is using Date and another isusing Timestamp? - 06-12-2008 , 07:26 AM

On 12 Jun., 13:06, Mullin Yu <mullin... (AT) gmail (DOT) com> wrote:

Quote:

How to join two tables while one is using Date and another is using
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

by removing the time component of the time stamp like:

select * from Orders o, AuditLog a
where o.TradeDate = trunc(a.EventTs)

Reply With Quote

#8

Old

steph

Posts: n/a

Re: How to join two tables while one is using Date and another isusing Timestamp? - 06-12-2008 , 07:26 AM

On 12 Jun., 13:06, Mullin Yu <mullin... (AT) gmail (DOT) com> wrote:

Quote:

How to join two tables while one is using Date and another is using
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

by removing the time component of the time stamp like:

select * from Orders o, AuditLog a
where o.TradeDate = trunc(a.EventTs)

Reply With Quote

#9

Old

Mladen Gogala

Posts: n/a

Re: How to join two tables while one is using Date and another isusing Timestamp? - 06-13-2008 , 02:10 AM

On Thu, 12 Jun 2008 16:20:16 -0400, Walt wrote:
ry will be dog slow with these

Quote:

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

Reply With Quote

#10

Old

Mladen Gogala

Posts: n/a

Re: How to join two tables while one is using Date and another isusing Timestamp? - 06-13-2008 , 02:10 AM

On Thu, 12 Jun 2008 16:20:16 -0400, Walt wrote:
ry will be dog slow with these

Quote:

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

Reply With Quote

Reply

Page 1 of 3

1

« Previous Thread | Next Thread »

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.