How do you convert scientific time to standard


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How do you convert scientific time to standard
# 1  
Old 08-20-2009
Question How do you convert scientific time to standard

Hi All, I'm new to this forum, and appreciate any assistance with my issue.
I have a shell script that logs into an oracle DB and runs a sqlplus query. Everything works great except for the time I get. I'm new to shell so bare with me. What would be the code and where do I place it?

My results are this
myjob1
1.1915E+12
I need it to be seen as myjob1 hh:mm:ss: MM/DD/YY

Here is my code

Code:
 
#!/usr/bin/sh

sqlplus -S myusername/mypassword@MYDATABASE <<eof> myfile
       set heading off feedback off verify off
       select JOB.JOBNAME, JOBRUN.ENDTIME from JOB, JOBRUN where JOB.JOBID = JOBRUN.JOBID and JOB.JOBNAME in ('myjob1');
exit
 
EOF
‘

Thanks for any help
# 2  
Old 08-20-2009
Is not the solution, but, try this:

Code:
leo@lein:~$ echo 1.1915E+12|bc -l
13.19159
leo@lein:~$

Format more friendly.
# 3  
Old 08-20-2009
Hi.

Try using to_char...

Code:
sqlplus -S myusername/mypassword@MYDATABASE <<eof> myfile
set heading off feedback off verify off
select JOB.JOBNAME, to_char( JOBRUN.ENDTIME, 'HH24:MI:SS MM/DD/YY' ) from JOB, JOBRUN where JOB.JOBID = JOBRUN.JOBID and JOB.JOBNAME in ('myjob1');

# 4  
Old 08-20-2009
you could use the TO_CHAR function.
Code:
#!/usr/bin/sh

sqlplus -S myusername/mypassword@MYDATABASE <<eof> myfile
       set heading off feedback off verify off
       select JOB.JOBNAME, TO_CHAR(JOBRUN.ENDTIME, 'HH24:MI:SS MM/DD/YY') from JOB, JOBRUN where JOB.JOBID = JOBRUN.JOBID and JOB.JOBNAME in ('myjob1');
exit
 
EOF
‘

alternatively, you can set the global date format in sqlplus like this:
Code:
alter session set NLS_DATE_FORMAT='<my_format>';

more info on time/date formatting here.
# 5  
Old 08-20-2009
That almost got it, at least now I'm in epoc time
I didwhat you posted, and it gave me an error, so I removed the 'MM/DD/YY' from the query and that's how I got the epoc time. So almost there, I may be able to find some more info now. unless you guys have a quick code. Then I have to start working on grabbing the newest Time

Now it reads
myjob1
1193191665000
myjob1
1193278063000
myjob1
1193366454000
# 6  
Old 08-20-2009
I don't believe the query is wrong (certainly not from two identical posts). Show us exactly what your query was, and the error.
# 7  
Old 08-20-2009
You must've typed something incorrectly.
Have a look at this Oracle session to understand both techniques:

Code:
test@XE> 
test@XE> create table t (x number, y date);

Table created.

test@XE> 
test@XE> insert into t (x,y)
  2  select 1, to_date('12/31/2008 13:23:47','mm/dd/yyyy hh24:mi:ss') from dual union all
  3  select 2, to_date('2/4/2009 9:19:34','mm/dd/yyyy hh24:mi:ss') from dual union all
  4  select 3, to_date('5/24/2009 19:23:58','mm/dd/yyyy hh24:mi:ss') from dual;

3 rows created.

test@XE> commit;

Commit complete.

test@XE> 
test@XE> -- (1) using to_char function
test@XE> select y, to_char(y,'hh24:mi:ss mm/dd/yy') y_fmt from t;

Y           Y_FMT
------------------ -----------------
31-DEC-08       13:23:47 12/31/08
04-FEB-09       09:19:34 02/04/09
24-MAY-09       19:23:58 05/24/09

test@XE> 
test@XE> -- (2) using nls_date_format
test@XE> alter session set nls_date_format = 'hh24:mi:ss mm/dd/yy';

Session altered.

test@XE> 
test@XE> -- now you don't have to put that to_char around the date column
test@XE> select y from t;

Y
-----------------
13:23:47 12/31/08
09:19:34 02/04/09
19:23:58 05/24/09

test@XE> 
test@XE>

tyler_durden
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert UTC time into current UNIX sever time zone

Hi guys thanks for the help for my previous posts.Now i have a requirement that i download a XMl file which has UTC time stamp.I need to convert UTC time into Unix server timezone. For ex if the time zone of unix server is CDT then i need to convert into CDT.whatever may be the system time... (5 Replies)
Discussion started by: mohanalakshmi
5 Replies

2. Shell Programming and Scripting

Calculating the epoch time from standard time using awk and calculating the duration

Hi All, I have the following time stamp data in 2 columns Date TimeStamp(also with milliseconds) 05/23/2012 08:30:11.250 05/23/2012 08:30:15.500 05/23/2012 08:31.15.500 . . etc From this data I need the following output. 0.00( row1-row1 in seconds) 04.25( row2-row1 in... (5 Replies)
Discussion started by: ks_reddy
5 Replies

3. Shell Programming and Scripting

Shell script to convert epoch time to real time

Dear experts, I have an epoch time input file such as : - 1302451209564 1302483698948 1302485231072 1302490805383 1302519244700 1302492787481 1302505299145 1302506557022 1302532112140 1302501033105 1302511536485 1302512669550 I need the epoch time above to be converted into real... (4 Replies)
Discussion started by: aismann
4 Replies

4. Shell Programming and Scripting

Find and Convert UTC Time to PST Time

Hello All - I have a script that grabs data from the net and outputs the following data 46029 46.144 -124.510 2010 07 26 22 50 320 4.0 6.0 2.2 9 6.8 311 1012.1 -0.9 13.3 13.5 13.3 - - 46041 47.353 -124.731 2010 07 26 22 50 250 2.0 3.0 1.6 8 6.4 - 1011.6 - ... (0 Replies)
Discussion started by: drexnefex
0 Replies

5. Shell Programming and Scripting

how to convert date time to epoch time in solaris

Hi, Is there any easy way to convert date time(stored in shell variable ) to epoch time in solaris box? As +%s is working on linux but not on solaris, also -d option is not working. Any suggestion please? (6 Replies)
Discussion started by: anshuman0507
6 Replies

6. Shell Programming and Scripting

Convert Unix Time to Standard Time

I have a list of interfaces and time the interface was last active. I can't figure out how to convert the time in the second column, Fa1/14 0 Se0/0/0 0 Fa1/11 0 Fa1/9 0 Fa1/0 0 Se0/0/1 1240401408 Gi1/0 0 Fa0/0 1240401408 Fa1/3 0 Fa1/8 0 Fa1/15 0 Fa1/13 0 Fa1/10 0 Fa1/1 0 Fa1/12... (7 Replies)
Discussion started by: mrlayance
7 Replies

7. Shell Programming and Scripting

Convert Epoch Time to Standard Date and Time & Vice Versa

Hi guys, I know that this topic has been discuss numerous times, and I have search the net and this forum for it. However, non able to address the problem I faced so far. I am on Solaris Platform and unable to install additional packages like the GNU date and gawk to make use of their... (5 Replies)
Discussion started by: DrivesMeCrazy
5 Replies

8. Shell Programming and Scripting

Convert milliseconds to standard time

hello, I have the uptime of the server showing as upTime=2427742050 How do I convert it to standard time. Thanks Chiru (1 Reply)
Discussion started by: chiru_h
1 Replies

9. Shell Programming and Scripting

How to Convert scientific notation to normal ?

Hell friends, I wrote a script gets the summation of particular column using awk. The awk output is given in scientific notation. How do I convert the scientific notation to normal. My awk syntax : awk '{sum += $2} END { printf sum }' temprep.txt Out put is like 1.5365e+07 I want it as... (2 Replies)
Discussion started by: maheshsri
2 Replies

10. Shell Programming and Scripting

Convert from standard epoch time from a shell script?

Is there an easy method to do an on the fly conversion of a standard epoch time (seconds from 1970) to more readable date format? Does Unix have anything built in to do this? (4 Replies)
Discussion started by: LordJezo
4 Replies
Login or Register to Ask a Question