SQL & PL/SQL

Hi,

using intervals...

with sample_data(bd) as

(select to_date('18/08/2016 14:30', 'dd/mm/rrrr hh24:mi') from dual union all

select to_date('16/09/2016 15:30', 'dd/mm/rrrr hh24:mi') from dual )

select bd

   from sample_data

   where bd - to_timestamp(sysdate, 'dd/mm/rrrr hh24:mi') > interval '30' minute

Hope it helps,

Sim

Hello,

as written by gdanby, "date arithmetic" is something that is really nice. In addition to "dateB - dateA" giving a number of days, you can also do things like "dateA + nb_of_days" to get a new date (nb_of_days can have decimal part and can be negative).

Example: 7 hours ago is: SELECT TO_CHAR( sysdate - 7 / 24, 'DD-MON-YYYY HH24:MI:SS' ) FROM dual;

So: a small enhancement if you intend to do things like

in this statement, the WHERE clause has to compute "sysdate - begin_date" for each candidate row. Not only this is extra work, but it might also prevent the use of an index. Prefer a comparison of the column begin_date with a constant:

    SELECT * FROM xxcp_oe_lines WHERE begin_date < sysdate - 30 / 1440;

Best regards,

Bruno Vroman.

Thank you for your query. Its working ... (y)

Hi,

One more concern, can we get avg date from these two columns, because if i am running a query with id column using these two dates(sysdate-begin_date) getting few records.

3240146 wrote:
Hi, 
One more concern, can we get avg date from these two columns,

HUH?

How can you add 2 dates together?

What is Dec 7, 1941 plus Sept 11, 2001?

What is result of July 4, 1776 divided by 2?

Hi,

3240146 wrote:
Hi, 
One more concern, can we get avg date from these two columns, because if i am running a query with id column using these two dates(sysdate-begin_date) getting few records.

Sorry, it's not clear what you want.

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

Explain, using specific examples, how you get those results from that data.

Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

See the forum FAQ: 

Oracle only computes averages for NUMBERs.  You can get the average of DATEs if you can convert them to NUMBERs (e.g., the number of days after some reference date), compute the average of those numbers, then add that number back to the reference date.

For example, if d1 and d2 are both DATEs, then the average of those 2 DATEs (that is, the DATE that is between them, equally distant from each) is

d1 + ((d2 - d1) / 2)

If just doing it matters the following might be one way (within certain limitations - sticking to julian date)

What is Dec 7, 1941 plus Sept 11, 2001?

select to_char(date_1,'dd-MON-yyyy')||' + '||to_char(date_2,'dd-MON-yyyy')||' = '||

       to_char(to_date(to_number(to_char(date_1,'j')) + to_number(to_char(date_2,'j')),'j'),'dd-MON-yyyy') two_dates_addition

  from (select date '1941-12-07' date_1,date '2001-09-11' date_2

          from dual

       )

What is result of July 4, 1776 divided by 2?

select july_04_1776,

       to_char(to_date(to_number(to_char(july_04_1776,'j')) / 2,'j'),'syyyy-mm-dd') july_04_1776_halved

  from (select date '1776-07-04' july_04_1776

          from dual

       )

Regards

Etbin

Thank you for your reply... its helpful...

One concern about Constraints, were are having a composite index on a table but in one of your select query were are using only one column in where clause, will it use the index while joining?

Regards

Hello,

replying seriously to your question about "averaging 2 dates": maybe you mean "take the middle point between the 2 dates".

Then at first sight this is very easy, as we know that we can do things like "dateb - datea gives nb of days" and things like "datea + nb_days gives another date": if you want the middle point between dateA and dateB:  (dateB - dateA) is the time between the 2 dates; we divide it by two and we have "half the interval". We add this to first date (or we subtract it from second date) and we have the middle point:

    middle_date := datea + ( dateb - datea ) / 2:

If we want more than "first sight" we have to decide a few things... Indeed: dateA and dateB are Oracle dates, this means that they can have something else then 00:00:00 for their hour=min:sec component. No problem, but middle_date will be computed "exactly" (well, max 0.5 second of mistake) with this in mind, and maybe you don't have this in mind... In some cases we store dates for which we don't want "hour:min:sec", like for example "hire_date", "birth_date", "date_of_promotion", ...  If we store thoise dates with 00:00:00, the computation for "middle day" might be the cause of dispute ;-)

What is the middle time between 18-SEP-2016 and 19-SEP-2016? Answer: 18-SEP-2016 12:00:00, but if you don't look at hour:min:sec, this is seen as 18-SEP-2016. OK for you? Maybe you will disagree if <<in your mind>> the interval was in fact [18-SEP-2016 00:00:00    19-SEP-2016 23:59:59.999] (thinking at a date as "the whole day"), then the middle is rather 19-SEP-2016 00:00:00... (decide and write your rules accordingly)

(and BTW: beware, this "averaging understood as taking the middle point" is not something that you can generalize to more dates by doing for example "averaging( d1, d2, d3 ) = middle_point( middle_point( d1, d2 ), d3 )", except if you accept the consequences (avg( d1, d2, d3 ) is not the same as avg( d1, d3, d2 ), ...)

Best regards,

Bruno

I can accept a joke being helpful to make us think as jokes are mostly based on observing real life situations and exposing the funny side of events authors witnessed to.

In my post I just wanted to stress that when something can be done it does not necessarily mean that that action automatically makes sense.

I did somehow add two dates and halved a given date without making Oracle complain, but I cannot give a meaningfull interpretation of the results of the two operations.

Sorry for the confusion I might have made.

Regards

Etbin

HI,

3240146 wrote:
Thank you for your reply... its helpful...
One concern about Constraints, were are having a composite index on a table but in one of your select query were are using only one column in where clause, will it use the index while joining?
Regards

You'll get better replies faster if you start a new thread for each new question.

See the Forum FAQ: for what you need to post.

If the question concerns performances (e.g., Will this query use this index?)  also see