Win a copy of Murach's MySQL this week in the JDBC and Relational Databases forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

DATE and sysdate

 
Ranch Hand
Posts: 316
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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>
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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:
Regards, Jan
[ November 21, 2007: Message edited by: Jan Cumps ]
 
I'm so happy! And I wish to make this tiny ad happy too:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic