RE: Unreadable time_stamp format

From: <Jared.Still_at_radisys.com>
Date: Wed, 17 Dec 2003 09:09:34 -0800
Message-ID: <F001.005DA306.20031217090934@fatcity.com>

Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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=5FHOME/sqlplus/demo.

Knock yourself out playing with joins, experimenting with different SQL=20 functions.

Create foreign keys, find out why FK's should have an index, what the=20 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=20

user=5Fname
, to=5Fdate('01/01/1970','mm/dd/yyyy') + ( time=5Fstamp / (24*60*60*1000= ))
from user=5Factivity=5Flog
where rownum <=3D 100

HTH Jared

"Nguyen, David M" <david.m.nguyen_at_xo.com> Sent by: ml-errors_at_fatcity.com
12/17/2003 07:54 AM
Please respond to ORACLE-L

=20

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc:=20
        Subject:        RE: Unreadable time=5Fstamp format

Jared,
=20
It is epochtime. When I run your given SQL I got TO=5FDATE output is=20 "24-NOV-03". Is it actual date translated from epochtime "1069716948959"=20 ?=20
=20
How do I query to select only 100 records? =20
=20
SQL> select to=5Fdate('01/01/1970','mm/dd/yyyy') + ( 1069716948959 /=20 (24*60*60*100
0) ) from dual ;
=20
TO=5FDATE('

24-NOV-03
=20
=20
Thanks,
David
=20
=20
-----Original Message-----
ed.Still_at_radisys.com
Sent: Tuesday, December 16, 2003 7:14 PM To: Multiple recipients of list ORACLE-L =20

Try milliseconds from the epoch - 1/1/1970=20

select to=5Fdate('01/01/1970','mm/dd/yyyy') + ( 1069716948959 /=20 (24*60*60*1000) ) from dual=20

You really need to know how the timestamp column is defined by your app.=20

Jared=20

=20
"Nguyen, David M" <david.m.nguyen_at_xo.com>=20 Sent by: ml-errors_at_fatcity.com=20
12/16/2003 02:09 PM=20
Please respond to ORACLE-L=20

       =20
        To:        Multiple recipients of list ORACLE-L=20


<ORACLE-L_at_fatcity.com>=20

        cc:        =20
        Subject:        RE: Unreadable time=5Fstamp format

Now I got.=20
=20

USER=5FNAME             TIME STAMP=20
--------------------              ----------------------=20
eholley                       1069716948959=20
jmdavis                       1069715246467=20
jmdavis                       1069715324466=20

=20
=20
-----Original Message-----
ak, Mark
Sent: Tuesday, December 16, 2003 3:54 PM To: Multiple recipients of list ORACLE-L =20
Since it's a NUMBER datatype, try:=20

SQL>  column time=5Fstamp format 99999999999999999999=20

SQL> select user=5Fname, time=5Fstamp from USER=5FACTIVITY=5FLOG;=20 =20
-----Original Message-----
Sent: Tuesday, December 16, 2003 4:39 PM To: Multiple recipients of list ORACLE-L I need to view time=5Fstamp column in USER=5FACTIVITY=5FLOG table but it di= splays unreadable format as=20
shown below. Is there a way to decode it to be readable?=20 SQL> desc USER=5FACTIVITY=5FLOG=20

 Name                                      Null?    Type=20
 ----------------------------------------- --------=20
----------------------------=20
 TIME=5FSTAMP                              NOT NULL NUMBER(20)=20
 NODE=5FNAME                               NOT NULL VARCHAR2(20)=20
 USER=5FNAME                                NOT NULL VARCHAR2(20)=20
 CLIENT=5FIP                                    NOT NULL VARCHAR2(15)=20
 DESCRIPTION                              NOT NULL VARCHAR2(2048)=20

=20
SQL>select user=5Fname, time=5Fstamp from USER=5FACTIVITY=5FLOG;=20

edscott              1.0693E+12=20
edscott              1.0693E+12=20
edscott              1.0693E+12=20
edscott              1.0693E+12=20
jmdavis              1.0693E+12=20
edscott              1.0693E+12=20
edscott              1.0693E+12=20

Thanks,
David=20

--=_alternative 005E2C4288256DFF_=
Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

 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. &nbs=
p;A good place to start would be the demo
 account scott/tiger, with the demo t=
ables in $ORACLE=5FHOME/sqlplus/demo.
 
 Knock yourself out playing with join=
s, 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=5Fname<=
/font>
    , to=5Fdate(=
'01/01/1970','mm/dd/yyyy') + ( time=5Fstamp / (24*60*60*1000))
 from user=5Factivity=5Flo=
g
 where rownum <=3D 100<=
/font>
 
 
 HTH
 
 Jared
 

 
 
 
<table width=3D100%>
<tr valign=3Dtop>
<td>
<td>"Nguyen, David M" <d=
avid.m.nguyen_at_xo.com>
 Sent by: ml-errors_at_fatcity.com
 12/17/2003 07:54 AM
  Please respond to ORACLE-L
 
<td>        
         To: &nbs=
p;      Multiple recipients of list ORACLE-L <ORACLE-L_at_fa= tcity.com>
         cc: &nbs=
p;      
         Subject:=
       RE: Unreadable time=5Fstamp format</tabl= e>
 
 
 Jared,
  
 It is epochtime.  Wh=
en I run your given SQL I got TO=5FDATE 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=5Fdate(=
'01/01/1970','mm/dd/yyyy') + ( 1069716948959 / (24*60*60*100
 0) ) from dual ;
  
 TO=5FDATE('
 ---------
 24-NOV-03
  
  
 Thanks, 
David
  
  
 -----Original Message----- 
From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On Behalf= Of Jared.Still_at_radisys.com

Sent:</b> Tuesday, December 16, 2003 7:14 PM<b><br>
To:</b> Multiple recipients of list ORACLE-L<b><br>
Subject:</b> RE: Unreadable time=5Fstamp format</font>

  
 
Try milliseconds from the epoch - 1/1/1970 
 
select to=5Fdate('01/01/1970','mm/dd/yyyy') + ( 1069716948959 / (24*60*60*1= 000) ) from dual 
 
You really need to know how the timestamp column is defined by your app.</f= ont> 
 
Jared 
 
 


<table width=3D100%>
<tr valign=3Dtop>
<td width=3D1%> 
<td width=3D42%>"Nguyen, David M=
" <david.m.nguyen_at_xo.com> Sent by: ml-errors_at_fatcity.com 
 12/16/2003 02:09 PM<font=
size=3D3 face=3D"Times New Roman"> 
Please respond to ORACLE-L</font= > 
<td width=3D56%>        <=
/font>        To:        Multiple recipie= nts of list ORACLE-L <ORACLE-L_at_fatcity.com>        cc:         <= br>
       Subject:        RE: Unreada= ble time=5Fstamp format</table>
 
 
 
Now I got.   
USER=5FNAME             TIME STAMP<fon= t size=3D3 face=3D"Times New Roman">

-------------------- &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;------=
----------------</font><font size=3D3 face=3D"Times New Roman"> </font><fon=

t size=3D2 color=3D#000080 face=3D"Arial"> eholley                   &nbs= p;   1069716948959 </fo= nt> jmdavis                   &nbs= p;   1069715246467 </fo= nt> jmdavis                   &nbs= p;   1069715324466 </fo= nt>

 </font><font size=3D3 face=3D"Times New Roman">&nbsp;</font><font size=3D2=
 color=3D#000080 face=3D"Arial"><br>
 </font><font size=3D3 face=3D"Times New Roman">&nbsp;</font><font size=3D2=

face=3D"Tahoma"> 
-----Original Message----- 
From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On Behalf= Of Bobak, Mark

Sent:</b> Tuesday, December 16, 2003 3:54 PM<b><br>
To:</b> Multiple recipients of list ORACLE-L<b><br>
Subject:</b> RE: Unreadable time=5Fstamp format</font><font size=3D3 face=

=3D"Times New Roman"> 
  Since it's a NUMBER datatype, try:

SQL&gt; &nbsp;column time=5Fstamp format 99999999999999999999</font><font s=

ize=3D3 face=3D"Times New Roman"> 
SQL>  select user=5Fname, time=5Fstamp from USER=5FACTIVITY=5FLOG;<= /font>   -----Original Message----- 
From: Nguyen, David M [mailto:david.m.nguyen_at_xo.com] Sent: Tuesday, December 16, 2003 4:39 PM To: Multiple recipients of list ORACLE-L Subject: Unreadable time=5Fstamp format 
I need to<font size=3D3 face=3D"Tim=

es New Roman"> </font><font size=3D2 face=3D"Arial">view time=5Fstamp</font=
><font size=3D3 face=3D"Times New Roman"> </font><font size=3D2 face=3D"Ari=
al">column in USER=5FACTIVITY=5FLOG table but it displays unreadable format=
 as shown below. &nbsp;Is there a</font><font size=3D3 face=3D"Times New Ro=
man"> </font><font size=3D2 face=3D"Arial">way to decode it to be readable?=

 
SQL> desc USER=5FACTIVITY=5FLOG<=
font size=3D3 face=3D"Times New Roman"> 
 Name         &nb=
sp;                     &= nbsp;      Null?    Type 
 ------------------------------------=

----- -------- ----------------------------</font><font size=3D3 face=3D"Ti=

mes New Roman"> 
 TIME=5FSTAMP           &nbs= p;                  NOT NULL N= UMBER(20) 
 NODE=5FNAME       &nb=
sp;                     &= nbsp; NOT NULL VARCHAR2(20) =

 USER=5FNAME       &nb=
sp;                     &= nbsp;  NOT NULL VARCHAR2(20) 
 CLIENT=5FIP       &nb=

sp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &=
nbsp; &nbsp;</font><font size=3D3 face=3D"Times New Roman"> </font><font si=
ze=3D2 face=3D"Arial">&nbsp; </font><font size=3D3 face=3D"Times New Roman"=
>&nbsp;</font><font size=3D2 face=3D"Arial">NOT NULL VARCHAR2(15)</font><fo=

nt size=3D3 face=3D"Times New Roman"> 
 DESCRIPTION       &nb=
sp;                     &= nbsp;NOT NULL VARCHAR2(2048)= 
 
SQL>select user=5Fname, time=5Fstamp=
 from USER=5FACTIVITY=5FLOG; 
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 



--=_alternative 005E2C4288256DFF_=--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Dec 17 2003 - 11:09:34 CST