Home » SQL & PL/SQL » Client Tools » Time stored as a NUMBER field
Time stored as a NUMBER field [message #609753] Tue, 11 March 2014 21:43 Go to next message
GretelDePaepe
Messages: 4
Registered: March 2014
Junior Member
Our application shows a time in one of the forms. When I look at the database table, the values are stored in a number field. When I extract and load the data into my data analysis tool, the time comes like: 0.999999999999. The numbers look random to me. How can I transform this field so I can actually analyse the field as a time value?
Re: Time stored as a NUMBER field [message #609755 is a reply to message #609753] Tue, 11 March 2014 21:51 Go to previous messageGo to next message
BlackSwan
Messages: 23133
Registered: January 2009
Senior Member
Welcome to this forum!

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>How can I transform this field so I can actually analyse the field as a time value?
Realize that we only know what you post here & that we are NOT mind readers,

Before we can proceed to give you a possible solution we need to know 2 details; a specific actual date/time is stored as what value in this table & field.

one possible guess is that it is standard *NIX epoch time
Re: Time stored as a NUMBER field [message #609759 is a reply to message #609755] Wed, 12 March 2014 00:28 Go to previous messageGo to next message
GretelDePaepe
Messages: 4
Registered: March 2014
Junior Member
For example
.172222 shows as 4:08 AM
.183332998 shows as 4:24 AM
Re: Time stored as a NUMBER field [message #609761 is a reply to message #609759] Wed, 12 March 2014 01:12 Go to previous messageGo to next message
Littlefoot
Messages: 19882
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you talk to person who designed that system? What did he/she say?

Did you read documentation? What's written in there?
Re: Time stored as a NUMBER field [message #609762 is a reply to message #609761] Wed, 12 March 2014 01:22 Go to previous messageGo to next message
GretelDePaepe
Messages: 4
Registered: March 2014
Junior Member
Documentation no use - all it says is the field is numeric
Is third party software - have written mail to vendor - but may take a while before I get reply - hence wanted to see if I could get some answers here.
It must be something simple - since if I paste the value in Excel and format the cell as time it shows the correct time
I have not worked with time often, hence not sure how this works.
Re: Time stored as a NUMBER field [message #609763 is a reply to message #609759] Wed, 12 March 2014 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select .172222 * 24 from dual;
.172222*24
----------
  4.133328

1 row selected.

SQL> select .183332998 * 24 from dual;
.183332998*24
-------------
   4.39999195

1 row selected.


So it is time in day unit.

Re: Time stored as a NUMBER field [message #609764 is a reply to message #609763] Wed, 12 March 2014 01:41 Go to previous messageGo to next message
GretelDePaepe
Messages: 4
Registered: March 2014
Junior Member
THANKS! GOT IT!
Re: Time stored as a NUMBER field [message #610355 is a reply to message #609764] Wed, 19 March 2014 09:28 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 450
Registered: July 2003
Location: WPB, FL
Senior Member
Are you sure it's "time of day" or just a time interval?
SQL> SELECT NUMTODSINTERVAL ( .172222, 'DAY')
  2       , NUMTODSINTERVAL ( .183332998, 'DAY')
  3*   FROM DUAL
SQL> /

NUMTODSINTERVAL(.172222,'DAY')
---------------------------------------------------------------------------
NUMTODSINTERVAL(.183332998,'DAY')
---------------------------------------------------------------------------
+000000000 04:07:59.980800000
+000000000 04:23:59.971027200

[Updated on: Wed, 19 March 2014 09:32] by Moderator

Report message to a moderator

Re: Time stored as a NUMBER field [message #610357 is a reply to message #610355] Wed, 19 March 2014 09:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And then what is the difference here? How does this matter in this case?
If OP says it is a time in the day then it is a time in the day and the proof is that he wants the output as "4:08 AM". Do you ever see an interval with "AM"?

Re: Time stored as a NUMBER field [message #610396 is a reply to message #610357] Wed, 19 March 2014 13:56 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 450
Registered: July 2003
Location: WPB, FL
Senior Member
Quote:
. . . How can I transform this field so I can actually analyze the field as a time value?. . . and
... I paste the value in Excel and format the cell as time

The fact that they show within excel as 4:08 AM and 4:24 AM does not mean he wants them that way.

[Updated on: Wed, 19 March 2014 13:58]

Report message to a moderator

Re: Time stored as a NUMBER field [message #610398 is a reply to message #610396] Wed, 19 March 2014 14:02 Go to previous message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
How can I transform this field so I can actually analyse the field as a time value?

Quote:
For example
.172222 shows as 4:08 AM
.183332998 shows as 4:24 AM


Nothing ambiguous for me.
Otherwise what could be the question and the purpose of the topic?

[Updated on: Wed, 19 March 2014 14:03]

Report message to a moderator

Previous Topic: How to login to Sql *Plus
Next Topic: execute long time ( table lock )
Goto Forum:
  


Current Time: Wed Dec 17 19:23:19 CST 2014

Total time taken to generate the page: 0.10378 seconds