Jared,
Sure I will. Thanks for you help.
Regards,
David
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Wednesday, December 17, 2003
11:10 AM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Unreadable time_stamp
format
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
Please
respond to ORACLE-L
|
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
Please respond to ORACLE-L
|
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
