Re: Timestamp SQL question

From: Oxnard <shankeypNO_SPAM_at_comcast.net>
Date: Mon, 18 Jul 2005 07:23:24 -0500
Message-ID: <YfadnfrTLafdAkbfRVn-gA@comcast.com>

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1121663940.489270_at_yasure...
> Oxnard wrote:
>> Oracle 9.2.0.5
>>
>> Assuming I have only one connection as SYS:
>>
>>
>> select username, sid, serial#, logon_time, to_timestamp(logon_time)
>> from v$session
>> where to_timestamp(logon_time) = (select to_timestamp(logon_time)
>> from v$session where
>> username = 'SYS')
>> order by logon_time
>>
>> select username, sid, serial#, logon_time, to_timestamp(logon_time)
>> from v$session
>> where logon_time = (select logon_time
>> from v$session where username = 'SYS')
>> order by logon_time
>>
>>
>> I would think that both would return only one row assuming no user logged
>> on at the exact same time as SYS. However, the SQL using the timestamp
>> returns all rows of v$session.
>>
>> I would normally just use date and be done with it, but the SQL is being
>> used in JAVA code. I need the time and a date object does not seem to do
>> it.
>>
>> What am I doing wrong? How can I compare timestamps in a where clause?
>
>
> set linesize 121
> col LOT format a20
> col username format a20
> col program format a20
>
> select program, username, sid, logon_time, to_timestamp(logon_time) LOT
> from v$session
> where to_timestamp(logon_time) = (
> select to_timestamp(logon_time)
> from v$session
> where username = 'SYS')
> order by logon_time
> /
>
> Does not restrict returned rows to those where username = SYS
>
> Take a look at your query after I modified it ... specifically the
> PROGRAM column values.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)

Sorry if I did not make myself clear.
The query

select username, sid, serial#, logon_time, to_timestamp(logon_time)  from v$session
 where to_timestamp(logon_time) = (select to_timestamp(logon_time)

                                                     from v$session  where
                                                     username = 'SYS')

order by logon_time

should only be returning one row assuming there is one connection as SYS and no other user logged on at the same time as SYS. However I find all rows are returned. That is what I am wondering why.   Received on Mon Jul 18 2005 - 07:23:24 CDT