OraFAQ Forum: Client Tools » Time stored as a NUMBER field

Home » Other » Client Tools » Time stored as a NUMBER field

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Time stored as a NUMBER field [message #609753]

Tue, 11 March 2014 21:43

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?

Report message to a moderator

Re: Time stored as a NUMBER field [message #609755 is a reply to message #609753]

Tue, 11 March 2014 21:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

Report message to a moderator

Re: Time stored as a NUMBER field [message #609759 is a reply to message #609755]

Wed, 12 March 2014 00:28

GretelDePaepe
Messages: 4
Registered: March 2014

Junior Member

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

Report message to a moderator

Re: Time stored as a NUMBER field [message #609761 is a reply to message #609759]

Wed, 12 March 2014 01:12

Littlefoot
Messages: 21823
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?

Report message to a moderator

Re: Time stored as a NUMBER field [message #609762 is a reply to message #609761]

Wed, 12 March 2014 01:22

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.

Report message to a moderator

Re: Time stored as a NUMBER field [message #609763 is a reply to message #609759]

Wed, 12 March 2014 01:30

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Time stored as a NUMBER field [message #609764 is a reply to message #609763]

Wed, 12 March 2014 01:41

GretelDePaepe
Messages: 4
Registered: March 2014

Junior Member

THANKS! GOT IT!

Report message to a moderator

Re: Time stored as a NUMBER field [message #610355 is a reply to message #609764]

Wed, 19 March 2014 09:28

LKBrwn_DBA
Messages: 487
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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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"?

Report message to a moderator

Re: Time stored as a NUMBER field [message #610396 is a reply to message #610357]

Wed, 19 March 2014 13:56

LKBrwn_DBA
Messages: 487
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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 02 15:32:33 CST 2025