RE: Unreadable time_stamp format

Nguyen, David M Wed, 17 Dec 2003 09:02:42 -0800

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

jmdavis 1.0693E+12

edscott 1.0693E+12

Thanks,
David

RE: Unreadable time_stamp format

Reply via email to