David,
<SOAPBOX>
Not trying to sound condescending or disrespectful, but I think you need
some serious practice time with SQL. Break out the SQL manual, and find
out how to join various tables. A good place to start would be the demo
account scott/tiger, with the demo tables in $ORACLE_HOME/sqlplus/demo.
Knock yourself out playing with joins, experimenting with different SQL functions.
Create foreign keys, find out why FK's should have an index, what the requirements
are for the index, etc. Become *fluent* in PL/SQL as well, and learn when to
use it and when not to use it.
</SOAPBOX>
Try this:
select
user_name
, to_date('01/01/1970','mm/dd/yyyy') + ( time_stamp / (24*60*60*1000))
from user_activity_log
where rownum <= 100
HTH
Jared
| "Nguyen, David M" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 12/17/2003 07:54 AM
|
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: RE: Unreadable time_stamp format |
Jared,
It is epochtime. When I run your given SQL I got TO_DATE output is "24-NOV-03". Is it actual date translated from epochtime "1069716948959" ?
How do I query to select only 100 records?
SQL> select to_date('01/01/1970','mm/dd/yyyy') + ( 1069716948959 / (24*60*60*100
0) ) from dual ;
TO_DATE('
---------
24-NOV-03
Thanks,
David
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 7:14 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Unreadable time_stamp format
Try milliseconds from the epoch - 1/1/1970
select to_date('01/01/1970','mm/dd/yyyy') + ( 1069716948959 / (24*60*60*1000) ) from dual
You really need to know how the timestamp column is defined by your app.
Jared
| "Nguyen, David M" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 12/16/2003 02:09 PM | To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: RE: Unreadable time_stamp format |
Now I got.
USER_NAME TIME STAMP
-------------------- ----------------------
eholley 1069716948959
jmdavis 1069715246467
jmdavis 1069715324466
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bobak, Mark
Sent: Tuesday, December 16, 2003 3:54 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Unreadable time_stamp format
Since it's a NUMBER datatype, try:
SQL> column time_stamp format 99999999999999999999
SQL> select user_name, time_stamp from USER_ACTIVITY_LOG;
-----Original Message-----
From: Nguyen, David M [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 4:39 PM
To: Multiple recipients of list ORACLE-L
Subject: Unreadable time_stamp format
I need to view time_stamp column in USER_ACTIVITY_LOG table but it displays unreadable format as shown below. Is there a way to decode it to be readable?
SQL> desc USER_ACTIVITY_LOG
Name Null? Type
----------------------------------------- -------- ----------------------------
TIME_STAMP NOT NULL NUMBER(20)
NODE_NAME NOT NULL VARCHAR2(20)
USER_NAME NOT NULL VARCHAR2(20)
CLIENT_IP NOT NULL VARCHAR2(15)
DESCRIPTION NOT NULL VARCHAR2(2048)
SQL>select user_name, time_stamp from USER_ACTIVITY_LOG;
edscott 1.0693E+12
edscott 1.0693E+12
edscott 1.0693E+12
edscott 1.0693E+12
jmdavis 1.0693E+12
edscott 1.0693E+12
edscott 1.0693E+12
Thanks,
David
