DATE and sysdate

[Raj Ohadi]

I have a column "log_time" with type "DATE". When I insert rows, I always use "sysdate" for this column. From Oracle book I find "DATE" type can include up to second precision. So I tried to enter some rows, with several minutes apart for any two consecutitive inserts. However, when I do

select * from Table order by log_time desc;

I found they are NOT sorted by "log_time" because all of the log_time values shows the same (Today). So it seems Oracle doesn't distinguish "sysdate" to narrow down to second level. Is there anything I miss here ?

Thanks.

[Chris Hendy]

Try

select to_char(t.log_time,'DD-MON-YYYY HH24:MI:SS') log_time_full, t.*
from Table t
order by t.log_time desc;

and see what happens

[Chris Hendy]

The date datatype is stored in a proprietory internal format, but displayed according to the database initialization parameter NLS_DATE_FORMAT (which at least for me defaults to 'DD-MON-YY' and so chops off the hours/minutes/seconds). You can alter this either for your session using ALTER SESSION (see below), or system wide by changing the init paramter - but this requires a database bounce.

SQL> select sysdate from dual;

SYSDATE
---------
21-NOV-07

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
21-NOV-2007 02:56:49

SQL>

[Jan Cumps]

I always use "sysdate" for this column

Can you show us how you do that (via sql literal, via java PreparedStatement.setDate() or via via java PreparedStatement.setTime())?

So it seems Oracle doesn't distinguish "sysdate" to narrow down to second level.

It does. When using ORDER BY LOG_TIME DESC, Oracle will use the date and the time to sort.
To help us, can you please show some results for this query where the sorting order is returned incorrect:

select to_char(log_time, 'YYYYMMDDHH24MISS') from table order by log_time desc;

Regards, Jan
[ November 21, 2007: Message edited by: Jan Cumps ]