Home » SQL & PL/SQL » SQL & PL/SQL » Time Datatype (Oracle 10g)
Time Datatype [message #596345] Sat, 21 September 2013 02:03 Go to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

Hi All,

How to maintain only the time in a column .Is there any time datatype.
Now we are maintaining like in number eg for 08:15:34 as 081534 in a number datatype.

Regards,
Nathan
Re: Time Datatype [message #596346 is a reply to message #596345] Sat, 21 September 2013 02:35 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
sss111ind wrote on Sat, 21 September 2013 12:33

How to maintain only the time in a column .Is there any time datatype.
Now we are maintaining like in number eg for 08:15:34 as 081534 in a number datatype.


T.Kyte says - Never never use a number or character string to store that which has a native format like
a DATE. Don't use two fields either -- they always go together and you can always mask
one or the other off for display.

+1 to him.

The bottomline is, there is no such data type which will allow you to store ONLY the time. Even if you try to insert(I know you would have tried it by now), you would end up with a DATE followed by time always, so it has to be together ALWAYS.

Below code demonstrates that it's impossible.

SQL> DROP TABLE T;
 
Table dropped

SQL> CREATE TABLE T(TIME_COL TIMESTAMP(6));
 
Table created
SQL> INSERT INTO T VALUES(TO_TIMESTAMP('12:12:12','HH:MI:SS'));
 
1 row inserted
SQL> COMMIT;
 
Commit complete
SQL> SELECT * FROM T;
 
TIME_COL
-------------------------------------------------
01-SEP-13 12.12.12.000000 PM


So, store as DATE, and display using TO_CHAR in your desired format.

Regards,
Lalit

[Updated on: Sat, 21 September 2013 02:49]

Report message to a moderator

icon4.gif  Re: Time Datatype [message #596347 is a reply to message #596345] Sat, 21 September 2013 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In Oracle, the only correct way to store a time only value is to store the number of seconds since midnight and to have a constraint on the column to forbid any value that is not >= 0 and < 86400.

Now, I don't see any case where you want to store time part only and not a real date with a day part.

What is your actual case?
Re: Time Datatype [message #596350 is a reply to message #596345] Sat, 21 September 2013 03:31 Go to previous messageGo to next message
John Watson
Messages: 4870
Registered: January 2010
Location: Global Village
Senior Member
Can you use the interval data type?
orclz> create table t1(c1 interval day to second,c2 varchar2(20));

Table created.

orclz> insert into t1 values(interval '90' minute,'01:30');

1 row created.

orclz> select * from t1;

C1                                                                          C2
--------------------------------------------------------------------------- -----------
+00 01:30:00.000000                                                         01:30

orclz>
Re: Time Datatype [message #596351 is a reply to message #596350] Sat, 21 September 2013 05:34 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

There are two columns like start_time is 10:00 AM and end_time is 07:00. So everyday employee entries needs to inserted into the table.If they are failing to come in with that range attendance will not be calculated.
And for that now we have an entry with number start_time is 1000 and end_time is 1900.Please suggest for this.

[Updated on: Sat, 21 September 2013 05:40]

Report message to a moderator

Re: Time Datatype [message #596352 is a reply to message #596351] Sat, 21 September 2013 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about what I said?

Regards
Michel
Re: Time Datatype [message #596362 is a reply to message #596352] Sat, 21 September 2013 09:52 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

In number datatype case we are validating the things like
WITH TEMP AS
 (SELECT TO_DATE('21-09-2013 10:23:11', 'DD-MM-YYYY HH24:MI:SS') entry_time,
         TO_DATE('21-09-2013 18:59:51', 'DD-MM-YYYY HH24:MI:SS') exit_time
    FROM DUAL
  UNION ALL
  SELECT TO_DATE('21-09-2013 13:24:31', 'DD-MM-YYYY HH24:MI:SS') entry_time,
         TO_DATE('21-09-2013 19:33:10', 'DD-MM-YYYY HH24:MI:SS') exit_time
    FROM DUAL
  UNION ALL
  SELECT TO_DATE('21-09-2013 10:00:51', 'DD-MM-YYYY HH24:MI:SS') ENTRY_TIME,
         TO_DATE('21-09-2013 19:00:43', 'DD-MM-YYYY HH24:MI:SS') EXIT_TIME
    FROM DUAL)
SELECT *
  FROM TEMP
 WHERE to_char(entry_time, 'hh24miss') >= 100000
   and to_char(exit_time, 'hh24miss') <= 190000;


In where condition 100000 represnts as 10:00:00 AM start time and 19:00:00 pm represents end time.But for Michel's case I do not in which way I can validate.Please give some idea.

[Updated on: Sat, 21 September 2013 09:52]

Report message to a moderator

Re: Time Datatype [message #596364 is a reply to message #596362] Sat, 21 September 2013 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sss111ind wrote on Sat, 21 September 2013 16:52
In number datatype case we are validating the things like
WITH TEMP AS
 (SELECT TO_DATE('21-09-2013 10:23:11', 'DD-MM-YYYY HH24:MI:SS') entry_time,
         TO_DATE('21-09-2013 18:59:51', 'DD-MM-YYYY HH24:MI:SS') exit_time
    FROM DUAL
  UNION ALL
  SELECT TO_DATE('21-09-2013 13:24:31', 'DD-MM-YYYY HH24:MI:SS') entry_time,
         TO_DATE('21-09-2013 19:33:10', 'DD-MM-YYYY HH24:MI:SS') exit_time
    FROM DUAL
  UNION ALL
  SELECT TO_DATE('21-09-2013 10:00:51', 'DD-MM-YYYY HH24:MI:SS') ENTRY_TIME,
         TO_DATE('21-09-2013 19:00:43', 'DD-MM-YYYY HH24:MI:SS') EXIT_TIME
    FROM DUAL)
SELECT *
  FROM TEMP
 WHERE to_char(entry_time, 'hh24miss') >= 100000
   and to_char(exit_time, 'hh24miss') <= 190000;


In where condition 100000 represnts as 10:00:00 AM start time and 19:00:00 pm represents end time.But for Michel's case I do not in which way I can validate.Please give some idea.


1/ "to_char(entry_time, 'hh24miss') >= 100000" you compare a string with a number?
2/ Who say there will not be 1888888 or '188888' or 'MICHEL' somewhere?
3/ I already told you the best way

Regards
Michel
Re: Time Datatype [message #596423 is a reply to message #596364] Mon, 23 September 2013 01:43 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

Hi All,

I WANT BASICALLY THAT if some EMPLOYEE WHICH BELONG to IT COMES BEYOND the SPECIFIED range then IT WILL MARKED as INVALID STATUS WHILE INSERTING a RECORD.
How I can convert this into Michels case.Please give some input.

create table dept_time(start_time number,end_time number,dept varchar2(50));

insert into DEPT_TIME values (100000,190000,'SALESS');
insert into DEPT_TIME values(90000,180000,'FINANCE');
insert into dept_time VALUES(113000,203000,'IT');
insert into dept_time VALUES(143000,233000,'SUPPORT');

create or replace procedure validate_entry_time(p_entry_time date default sysdate ,p_dept in varchar2,p_status out varchar2) is
l_entry_time number;
l_start_time number;
l_end_time number;
l_dept varchar2(50);
begin
l_entry_time:=to_number(to_char(sysdate,'hh24miss'));
select start_time,end_time,dept into l_start_time,l_end_time,l_dept from dept_time where dept=p_dept;
if l_entry_time not between l_start_time and l_end_time then 
p_status:='Invalid';
ELSE
p_status:='Valid';
end if;
end;

declare
p_status varchar2(100);
begin
validate_entry_time(null,'IT',p_status);
dbms_output.put_line('status ' ||p_status);
end;



[Updated on: Mon, 23 September 2013 01:46]

Report message to a moderator

icon13.gif  Re: Time Datatype [message #596425 is a reply to message #596423] Mon, 23 September 2013 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please format your code.
If you don't know how to do it, learn it using SQL Formatter.

Re: Time Datatype [message #596427 is a reply to message #596425] Mon, 23 September 2013 02:03 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member


create table dept_time(start_time number,end_time number,dept varchar2(50));

insert into DEPT_TIME values (100000,190000,'SALESS');
insert into DEPT_TIME values(90000,180000,'FINANCE');
insert into dept_time VALUES(113000,203000,'IT');
insert into dept_time VALUES(143000,233000,'SUPPORT');


 CREATE OR replace PROCEDURE Validate_entry_time(
p_entry_time DATE DEFAULT SYSDATE,
p_dept       IN VARCHAR2,
p_status     OUT VARCHAR2)
IS
  l_entry_time NUMBER;
  l_start_time NUMBER;
  l_end_time   NUMBER;
  l_dept       VARCHAR2(50);
BEGIN
    l_entry_time := To_number(To_char(SYSDATE, 'hh24miss'));

    SELECT start_time,
           end_time,
           dept
    INTO   l_start_time, l_end_time, l_dept
    FROM   dept_time
    WHERE  dept = p_dept;

    IF l_entry_time NOT BETWEEN l_start_time AND l_end_time THEN
      p_status := 'Invalid';
    ELSE
      p_status := 'Valid';
    END IF;
END;  


declare
p_status varchar2(100);
begin
validate_entry_time(null,'IT',p_status);
dbms_output.put_line('status ' ||p_status);
end;

icon8.gif  Re: Time Datatype [message #596428 is a reply to message #596427] Mon, 23 September 2013 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do you think you last PL/SQL block is formatted?
What is the purpose of the procedure? Do you expect we reverse engineer a code that may be incorrect (otherwise why do you post it).
For instance, what is the purpose of parameter "p_entry_time" which is never used?



Re: Time Datatype [message #596430 is a reply to message #596428] Mon, 23 September 2013 02:36 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

Sorry Michel,

It's my mistake while preparing the test case.Actually the line should replace with this following.

l_entry_time := To_number(To_char(SYSDATE, 'hh24miss'));
replace with
l_entry_time := To_number(To_char(p_entry_time, 'hh24miss'));


Re: Time Datatype [message #596431 is a reply to message #596430] Mon, 23 September 2013 02:49 Go to previous messageGo to next message
John Watson
Messages: 4870
Registered: January 2010
Location: Global Village
Senior Member
Your type conversions are corrupting the data. For instance:
 orclz>
orclz> select sysdate from dual;

SYSDATE
-------------------
2013-09-23 08:47:08

orclz> select (To_char(SYSDATE, 'hh24miss')) from dual;

(TO_CH
------
084718

orclz> select To_number(To_char(SYSDATE, 'hh24miss')) from dual;

TO_NUMBER(TO_CHAR(SYSDATE,'HH24MISS'))
--------------------------------------
                                 84721

orclz>

You would need to compare the values as strings, to_number loses leading zeros.
Re: Time Datatype [message #596432 is a reply to message #596431] Mon, 23 September 2013 03:06 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

I think there is no issue while comparing the things.

SELECT *
FROM dept_time
WHERE TO_NUMBER(TO_CHAR(SYSDATE,'HH24MISS')) BETWEEN start_time AND end_time
AND dept='SUPPORT'; --invalid one

SELECT *
FROM dept_time
WHERE TO_NUMBER(TO_CHAR(SYSDATE,'HH24MISS')) BETWEEN start_time AND end_time
AND dept='IT'; --valid one


icon5.gif  Re: Time Datatype [message #596438 is a reply to message #596430] Mon, 23 September 2013 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
What is the purpose of the procedure?


What should it do, display, return from the parameters?

Re: Time Datatype [message #596446 is a reply to message #596438] Mon, 23 September 2013 05:23 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

The purpose of procedure is validating employees entry or exit timing.
If anything misses those boundaries then the status field marked as invalid against the record
which is inserted into the employee_time table else valid .The table is like as follows.
--employee_time (entry_time date,exit_time date,status varchar2(10),emp_id varchar2(30))
DECLARE
    p_status VARCHAR2(100);
    p_emp_id VARCHAR2(30);
BEGIN
    INSERT INTO employee_time(entry_time,exit_time, status,emp_id)
    VALUES     (SYSDATE, NULL, NULL,p_emp_id);--first time emtry

    Validate_entry_time(NULL, 'IT', p_status);

      UPDATE employee_time SET status = p_status,exit_time = SYSDATE
      WHERE  employee_id = p_emp_id; --status updating for entyr or exit
END;  

[Updated on: Mon, 23 September 2013 05:31]

Report message to a moderator

icon13.gif  Re: Time Datatype [message #596456 is a reply to message #596446] Mon, 23 September 2013 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/
There is no relation between the current employee and your procedure but its department which means all employees of each department will have the same status.

2/
There is no reason to use PL/SQL for this which can be done in SQL with UPDATE + INSERT statements.

Re: Time Datatype [message #596459 is a reply to message #596446] Mon, 23 September 2013 07:17 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
sss111ind wrote on Mon, 23 September 2013 15:53
The purpose of procedure is validating employees entry or exit timing.


Well, there is no employee column to validate for each employee. The output will always be for an entire department. There should be a column for employees too.

Let the dept_time table be one time created with the values as of created date appended with the cut off times of entry and exit, finally stored as timestamp data type.

I tried this, see if helps in anyway -

SQL> CREATE TABLE dept_time
  2    (
  3       start_time TIMESTAMP,
  4       end_time   TIMESTAMP,
  5       dept       VARCHAR2(50)
  6    );

Table created.

SQL>
SQL> INSERT INTO dept_time
  2  VALUES      (To_timestamp(Trunc(SYSDATE)
  3                            ||' '
  4                            ||'10:00:00 AM'),
  5               To_timestamp(Trunc(SYSDATE)
  6                            ||' '
  7                            ||'7:00:00 PM'),
  8               'SALES');

1 row created.

SQL>
SQL> INSERT INTO dept_time
  2  VALUES      (To_timestamp(Trunc(SYSDATE)
  3                            ||' '
  4                            ||'7:00:00 AM'),
  5               To_timestamp(Trunc(SYSDATE)
  6                            ||' '
  7                            ||'4:00:00 PM'),
  8               'FINANCE');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT dept,
  2         To_char(SYSDATE, 'hh12:mi:ss AM') curr_time,
  3         CASE
  4           WHEN systimestamp BETWEEN To_timestamp(Trunc(SYSDATE)
  5                                                  ||' '
  6                                                  ||To_char(start_time,
  7                                                    'hh12:mi:ss AM'))
  8                                     AND
  9                                            To_timestamp(
 10                                            Trunc(SYSDATE)
 11                                            ||' '
 12                                            ||
 13                                            To_char(end_time, 'hh12:mi:ss AM'
))
 14         THEN
 15           'valid'
 16           ELSE 'invalid'
 17         END                               status
 18  FROM   dept_time;

DEPT                                               CURR_TIME   STATUS
-------------------------------------------------- ----------- -------
SALES                                              05:55:42 PM valid
FINANCE                                            05:55:42 PM invalid


Regards,
Lalit

[Updated on: Mon, 23 September 2013 07:26]

Report message to a moderator

Re: Time Datatype [message #596460 is a reply to message #596351] Mon, 23 September 2013 07:35 Go to previous messageGo to next message
EdStevens
Messages: 351
Registered: September 2013
Senior Member
sss111ind wrote on Sat, 21 September 2013 05:34
There are two columns like start_time is 10:00 AM and end_time is 07:00. So everyday employee entries needs to inserted into the table.If they are failing to come in with that range attendance will not be calculated.
And for that now we have an entry with number start_time is 1000 and end_time is 1900.Please suggest for this.


So if it is "every day", then that start and stop time are specific to a particular date. So why are you not wanting to keep that date with it? Please don't say it's because you are keeping the date in a different column.
Re: Time Datatype [message #596466 is a reply to message #596460] Mon, 23 September 2013 10:52 Go to previous messageGo to next message
sss111ind
Messages: 508
Registered: April 2012
Location: India
Senior Member

EdStevens ,

The dept_time is maintained only once in that table and check is made for every details of employee while entering into the table.Hence if we make the datatype as date that will not work else we have to check the only time part for validating the things.

Lalit,

The code is working fine when we will validate only the time part. But what is the use of keeping all the timestamp but validating only the time part. It will just create confusion if the entry is made on different date into the dept_time table.

But what is the issue of our approach,Is there any loophole with that. If nothing is there why should someone will move for a different approach.
Please suggest.

Regards,
Nathan
Re: Time Datatype [message #596469 is a reply to message #596466] Mon, 23 September 2013 11:29 Go to previous message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Mon, 23 September 2013 13:06

1/
There is no relation between the current employee and your procedure but its department which means all employees of each department will have the same status.

2/
There is no reason to use PL/SQL for this which can be done in SQL with UPDATE + INSERT statements.


Previous Topic: Model clause
Next Topic: cursor implement
Goto Forum:
  


Current Time: Sun Dec 28 07:09:24 CST 2014

Total time taken to generate the page: 0.07089 seconds