Re: Timestamp SQL question

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Mon, 18 Jul 2005 15:07:55 +0200
Message-ID: <42dbaaa9$0$16686$9b4e6d93@newsread2.arcor-online.net>

Oxnard schrieb:

> "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.
>  
> 
> 

First you should look on the definition of to_timestamp function. It takes an argument of char( nchar,varchar2,nvarchar2) datatype. Then you should look on the definition of v$session dynamic view. The column logon_time is of date datatype. Then you can imagine what happens - you call a function expecting a character argument with a parameter with date datatype - it will be converted. As you don't specify the format model, default will be used ( in mostly cases depends on NLS_DATE_FORMAT   from nls_session_parameters can be seen and equals DD-MON-RR ). That means, you convert your date to an character string and truncate time part of that date. It makes your where clause to somewhat like WHERE TRUNC(LOGON_TIME) = ( SELECT trunc(LOGON_TIME) of your session ) . As you don't correlate the inner query to outer query on some other condition - it is very likely , that many sessions are logged in on the same *day* as your sys user - all that records are returned.

Best regards

Maxim Received on Mon Jul 18 2005 - 08:07:55 CDT