TIME COLUMN [message #390421] Fri, 06 March 2009 05:13 Go to next message
Messages: 1
Registered: March 2009
Junior Member
Hi all.

I need to store time information in a table (only hours and minutes are important). ┬┐What data type would you use for that column?

Re: TIME COLUMN [message #390423 is a reply to message #390421] Fri, 06 March 2009 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Re: TIME COLUMN [message #390505 is a reply to message #390423] Fri, 06 March 2009 12:46 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Be very careful when selecting special purpose datatypes like interval, to ensure that any reporting / development tool you use also supports the datatype.

The old fashioned way to keep "intervals" is to use a date or number datatype. Number data type is easier to use than date when your intervals could exceed 1 day.
SQL> create table old_way(
  2    col1 varchar2(30),
  3    col2 number);

Table created.

SQL> insert into old_way values (
  2    '1 dy, 23 hr, 2 min, 5 sec',
  3    1 + 23/24 + 2/24/60 + 5/24/60/60);

1 row created.

SQL> CREATE OR REPLACE FUNCTION to_hms (p_days IN number)
  2     RETURN varchar2
  3  IS
  4  BEGIN
  5     RETURN TO_CHAR (TRUNC (p_days)) || ' days ' ||
  6                   TO_CHAR (TRUNC (SYSDATE) + MOD (p_days, 1), 'HH24:MI:SS');
  7  END to_hms;
  8  /

Function created.

SQL> column col2 format a30
SQL> select col1, to_hms(col2) col2 from old_way;

COL1                           COL2
------------------------------ ------------------------------
1 dy, 23 hr, 2 min, 5 sec      1 days 23:02:05


To use a date column, you basically just select an arbitrary date, add your hrs, min, sec to that and then use the date formatting picture to display the hrs, min, sec again. See the function above.
Previous Topic: Materialized View corrupted (merged)
Next Topic: Help Needed in Ref Cursors
Goto Forum:

Current Time: Mon Jan 23 17:10:22 CST 2017

Total time taken to generate the page: 0.09274 seconds