1

I want to create a column in a table, which stores the time at which to run a job. Which datatype should I use for this column? Please explain with some examples.

turbo88
  • 423
  • 2
  • 8
  • 23
  • 1
    Probably need more info on how you plan to use this column (time to run a job using a scheduler? what scheduler? If cron (or quartz), you might want to store the cron expression itself. But my guess is you probably will want to use a date (even if you just want the time component). – tbone Feb 13 '14 at 14:41
  • Not sure it's a duplicate but [this question](http://stackoverflow.com/q/12215281/266304) might be related. – Alex Poole Feb 13 '14 at 15:35
  • I am planning to write a custom scheduler in c#. If I use DATE datatype, I need to parse the time component everytime I access it. So, I want to save the time in HH:MM:SS format, for the job to run. – turbo88 Feb 14 '14 at 04:58
  • If you use a `DATE` then you can retrieve just the time portion with `TO_CHAR(, 'HH24:MI:SS')`; you imply that's a lot of overhead, but not sure why? You could store that as a string but then you don't get validation that it is actually a valid time. Using an interval would also enforce that but is a little tricker to extract; or you could have separate hour, minute and second columns with range constraints. Or one value as the number of seconds from midnight and let c# convert that to the time. Using a `DATE` and ignore the date part may be the simplest thing to do though. – Alex Poole Feb 14 '14 at 10:38

1 Answers1

1

You can use the DATE datatype, if you need to manage Year, month, day, hour, minute and second. You can use the TIMESTAMP datatype (with the desired precision) if you need fractional seconds too.

CREATE TABLE T
(D1     DATE,
 T1     TIMESTAMP(9));  -- Default: 6

INSERT INTO T
VALUES (SYSDATE, SYSTIMESTAMP);

SELECT *
FROM T;

D1                      T1
-------------------     -------------------------------
02/13/2014 16:03:25     13-FEB-14 04.03.25.772000000 PM

If you need the time component - just for example - even with a DATE datatype you can get it, using TO_CHAR function with the format mask that you need:

SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') AS HMS
FROM T;

HMS
--------
10:19:51
Corrado Piola
  • 859
  • 1
  • 14
  • 18
  • Thank you Corrado for the detailed reply. I am planning to write a custom scheduler in c#. If I use DATE datatype, I need to parse the time component everytime I access it. So, I want to save the time in HH:MM:SS format, for the job to run. – turbo88 Feb 14 '14 at 05:15
  • moreover I want the job to run everyday. So it doesn't make sense if the column has a date in it. – turbo88 Feb 14 '14 at 05:16
  • Hi Stany, I have updated my answer with an example to get the time component only, from a DATE datatype. I don't Know if this was your question.... :( – Corrado Piola Feb 14 '14 at 09:22