StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I use Oracle's date data type and educate developers on the issue of time components affecting boundary conditions.</p> <p>A database constraint will also prevent the accidental specification of a time component in a column that should have none and also tells the optimizer that none of the values have a time component.</p> <p>For example, the constraint CHECK (MY_DATE=TRUNC(MY_DATE)) prevents a value with a time other than 00:00:00 being placed into the my_date column, and also allows Oracle to infer that a predicate such as MY_DATE = TO_DATE('2008-09-12 15:00:00') will never be true, and hence no rows will be returned from the table because it can be expanded to:</p> <pre><code>MY_DATE = TO_DATE('2008-09-12 15:00:00') AND TO_DATE('2008-09-12 15:00:00') = TRUNC(TO_DATE('2008-09-12 15:00:00')) </code></pre> <p>This is automatically false of course.</p> <p>Although it is sometimes tempting to store dates as numbers such as 20080915 this can cause query optimization problems. For example, how many legal values are there between 20,071,231 and 20,070,101? How about between the dates 31-Dec-2007 abnd 01-Jan-2008? It also allows illegal values to be entered, such as 20070100.</p> <p>So, if you have dates without time components then defining a range becomes easy:</p> <pre><code>select ... from ... where my_date Between date '2008-01-01' and date '2008-01-05' </code></pre> <p>When there is a time component you can do one of the following:</p> <pre><code>select ... from ... where my_date >= date '2008-01-01' and my_date < date '2008-01-06' </code></pre> <p>or</p> <pre><code>select ... from ... where my_date Between date '2008-01-01' and date '2008-01-05'-(1/24/60/60) </code></pre> <p>Note the use of (1/24/60/60) instead of a magic number. It's pretty common in Oracle to perform date arithmetic by adding defined fractions of a day ... 3/24 for three hours, 27/24/60 for 27 minutes. Oracle math of this type is exact and doesn't suffer rounding errors, so:</p> <pre><code>select 27/24/60 from dual; </code></pre> <p>... gives 0.01875, not 0.01874999999999 or whatever.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload