RE: Unreadable time_stamp format

From: Nguyen, David M <david.m.nguyen_at_xo.com>
Date: Wed, 17 Dec 2003 09:44:37 -0800
Message-ID: <F001.005DA309.20031217094437@fatcity.com>

Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Jared,

=20

Sure I will. Thanks for you help.

=20

Regards,
David

=20

-----Original Message-----
Jared.Still_at_radisys.com
Sent: Wednesday, December 17, 2003 11:10 AM To: Multiple recipients of list ORACLE-L

=20

David,=20

<SOAPBOX>=20

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.=20

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

Create foreign keys, find out why FK's should have an index, what the requirements=20
are for the index, etc. Become *fluent* in PL/SQL as well, and learn when to=20
use it and when not to use it.=20

</SOAPBOX>=20

Try this:=20

select=20

user_name=20
, to_date('01/01/1970','mm/dd/yyyy') + ( time_stamp / (24*60*60*1000))=20
from user_activity_log=20
where rownum <=3D 100=20

HTH=20 Jared=20

=20

"Nguyen, David M" <david.m.nguyen_at_xo.com>=20 Sent by: ml-errors_at_fatcity.com=20

12/17/2003 07:54 AM=20
Please respond to ORACLE-L=20

       =20
        To:        Multiple recipients of list ORACLE-L


<ORACLE-L_at_fatcity.com>=20

        cc:        =20
        Subject:        RE: Unreadable time_stamp format

Jared,=20
=20
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" ? =20
=20
How do I query to select only 100 records?=20 =20
=20
SQL> select to_date('01/01/1970','mm/dd/yyyy') + ( 1069716948959 / (24*60*60*100=20
0) ) from dual ;=20
=20

TO_DATE('=20
---------=20
24-NOV-03=20

=20
=20
Thanks,
David=20
=20
=20
-----Original Message-----
Jared.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_date('01/01/1970','mm/dd/yyyy') + ( 1069716948959 / (24*60*60*1000) ) from dual=20

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

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


<ORACLE-L_at_fatcity.com>=20

       cc:        =20
       Subject:        RE: Unreadable time_stamp format

Now I got.=20
=20

USER_NAME             TIME STAMP=20
--------------------              ----------------------=20
eholley                       1069716948959=20
jmdavis                       1069715246467=20
jmdavis                       1069715324466=20

=20
=20
-----Original Message-----
Bobak, 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_stamp format 99999999999999999999=20

SQL> select user_name, time_stamp from USER_ACTIVITY_LOG;=20 =20
-----Original Message-----
Sent: Tuesday, December 16, 2003 4:39 PM To: Multiple recipients of list ORACLE-L

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?=20

SQL> desc USER_ACTIVITY_LOG=20

 Name                                      Null?    Type=20

 ----------------------------------------- --------
----------------------------=20

 TIME_STAMP                              NOT NULL NUMBER(20)=20

 NODE_NAME                               NOT NULL VARCHAR2(20)=20

 USER_NAME                                NOT NULL VARCHAR2(20)=20

 CLIENT_IP                                    NOT NULL VARCHAR2(15)=20

 DESCRIPTION                              NOT NULL VARCHAR2(2048)=20

=20

SQL>select user_name, time_stamp from USER_ACTIVITY_LOG;=20

edscott 1.0693E+12=20

jmdavis 1.0693E+12=20

edscott 1.0693E+12=20

Thanks,
David=20

------_=_NextPart_001_01C3C4C4.FFF20250
Content-Type: text/html;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

<html>

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">

<meta name=3DGenerator content=3D"Microsoft Word 10 (filtered)">

<style>
<!--

/* Font Definitions */
@font-face

	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
	{font-family:sans-serif;
	panose-1:0 0 0 0 0 0 0 0 0 0;}

/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal

	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
p
	{margin-right:0in;
	margin-left:0in;
	font-size:12.0pt;
	font-family:"Times New Roman";}
span.EmailStyle18
	{font-family:Arial;
	color:navy;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}

-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

Jared,

Sure I will.  Thanks for you = help.

Regards, David

-----Original = Message----- 
From: =
ml-errors_at_fatcity.com
[mailto:ml-errors_at_fatcity.com] On = Behalf Of Jared.Still_at_radisys.com 
Sent: Wednesday, December =
17, 2003
11:10 AM 
To: Multiple recipients =
of list
ORACLE-L 
Subject: RE: Unreadable =
time_stamp
format

 
David, 
 
<SOAPBOX><= /font>
 
 
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 <=3D 100 
 
 
HTH
 
 
Jared
 
 
 
 


<table class=3DMsoNormalTable border=3D0 cellpadding=3D0 width=3D"100%"
style=3D'width:100.0%;margin-left:.5in'> <tr>
<td valign=3Dtop style=3D'padding:.75pt .75pt .75pt .75pt'>

  </td>
  <td valign=3Dtop style=3D'padding:.75pt .75pt .75pt .75pt'>
  <p class=3DMsoNormal><b><font size=3D1 face=3Dsans-serif><span =

style=3D'font-size:
 7.5pt;font-family:sans-serif;font-weight:bold'>"Nguyen, David = M"
 <david.m.nguyen_at_xo.com> Sent by: ml-errors_at_fatcity.com  12/17/2003 07:54 AM  <fo= nt
 size=3D1 face=3Dsans-serif>Please respond to ORACLE-L

  </td>
  <td valign=3Dtop style=3D'padding:.75pt .75pt .75pt .75pt'>
  <p class=3DMsoNormal><font size=3D1 face=3DArial><span =

style=3D'font-size:7.5pt;
 font-family:Arial'>                To:        Multiple = recipients of
 list ORACLE-L <ORACLE-L_at_fatcity.com>         cc:         =
 

         Subject:        RE: = Unreadable
 time_stamp format
 </td>
</tr>
</table>

 
 
 
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: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] =
<span

style=3D'font-weight:bold'>On Behalf Of =
Jared.Still_at_radisys.com<span
style=3D'font-weight:bold'> 
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 
 


<table class=3DMsoNormalTable border=3D0 cellpadding=3D0 width=3D"100%"
style=3D'width:100.0%;margin-left:.5in'> <tr>
 <td width=3D"1%" valign=3Dtop style=3D'width:1.04%;padding:.75pt .75pt = .75pt .75pt'>
   </td>
 <td width=3D"41%" valign=3Dtop style=3D'width:41.82%;padding:.75pt = .75pt .75pt .75pt'>
 "Nguyen, David = M"
 <david.m.nguyen_at_xo.com> Sent by: ml-errors_at_fatcity.com  12/16/2003 02:09 PM Please respond to ORACLE-L </td>
 <td width=3D"55%" valign=3Dtop style=3D'width:55.78%;padding:.75pt = .75pt .75pt .75pt'>
                To:        Multiple = recipients
 of list ORACLE-L <ORACLE-L_at_fatcity.com>        cc:       =          Subject:        RE: = Unreadable
 time_stamp format
 </td>
</tr>
</table>

 
 
 
 
Now I got. 
  USER_NAME             TIME = STAMP

-------------------- &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp;----------------------</span></font> <font size=3D2 color=3Dnavy =

face=3DArial> eholley                   =  
  1069716948959 jmdavis                   =  
  1069715246467 jmdavis                   =  
  1069715324466 
  
  
-----Original Message----- From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] =
<span

style=3D'font-weight:bold'>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</span></font> <font

size=3D2 color=3Dblue face=3DArial> 
SQL>  select user_name, time_stamp from = USER_ACTIVITY_LOG;
 

  -----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_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           =

&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; =
&nbsp;
&nbsp; &nbsp;Null? &nbsp; &nbsp;Type</span></font> </p>

<span =
style=3D'font-size:

10.0pt;font-family:Arial'>&nbsp;-----------------------------------------=

-------- ----------------------------</span></font> </p>

 <f= ont
size=3D2 face=3DArial>TIME_STAMP                 =  
           NOT NULL = NUMBER(20)

 NODE_NAME         =  
                    = NOT NULL
VARCHAR2(20)

 USER_NAME         =

&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; =
&nbsp;NOT

NULL VARCHAR2(20)

 CLIENT_IP         =

&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; =
&nbsp;</span></font>

 
 NOT NULL VARCHAR2(15)

 DESCRIPTION         =  
                   NOT = NULL
VARCHAR2(2048)

SQL>select user_name, time_stamp from USER_ACTIVITY_LOG;

edscott           =    1.0693E+12<= /span>


jmdavis           =    1.0693E+12<= /span>


Thanks, 
David

</div>

</body>

</html>

=00
------_=_NextPart_001_01C3C4C4.FFF20250--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nguyen, David M
  INET: david.m.nguyen_at_xo.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:44:37 CST