CODEWITHSUNDEEP
sqloracle-database
TomJava
TomJava

Reputation: 529

Subtract minutes from a date column based on a value in another table

I am trying to subtract minutes from a date column based on the value in another table. But query is getting into error. Can someone advise on this?

TABLE 1 NAME - MYTABLE1 

COLUMN NAME "IN_DATE" Data type - DATE

TABLE 2
NAME - CONFIG_TABLE
COLUMN NAME - PARAM_NAME VALUE = SUBTRACT_MINUTE_VALUE
COLUMN NAME   PARAM_VALUE  VALUE =15

SELECT IN_DATE , IN_DATE - interval (SELECT PARAM_VALUE FROM CONFIG_TABLE WHERE PARAM_NAME='SUBTRACT_MINUTE_VALUE') minute FROM MYTABLE1

Upvotes: 1

Views: 757

Answers (3)

eifla001
eifla001

Reputation: 1157

you can also try this,

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH:MI:SS AM';

SELECT SYSDATE current_time, 
       SYSDATE - (1/24/60)*:p_min current_time_minus_mins
  FROM DUAL;

Upvotes: 0

user330315
user330315

Reputation:

Another option is to use an interval of one minute and multiply that with the number stored in the config table. 

select m.in_date,
       m.in_date - (interval '1' minute * c.param_value) as result
from mytable1 m 
  cross join config_table c on c.param_name = 'SUBTRACT_MINUTE_VALUE'

Upvotes: 4

Littlefoot
Littlefoot

Reputation: 143083

Here's how I do it: subtract minutes as number of minutes divided by (24 hours (in a day) x 60 minutes (in an hour))

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi';

Session altered.

SQL> create table config (subtract_minute_value number);

Table created.

SQL> insert into config values (15);

1 row created.

SQL> create table mytable1 (in_date date);

Table created.

SQL> insert into mytable1 values (sysdate);

1 row created.

SQL>
SQL> select m.in_date, m.in_date - c.subtract_minute_value / (24 * 60) result
  2  from mytable1 m, config c;

IN_DATE          RESULT
---------------- ----------------
02.02.2018 06:56 02.02.2018 06:41

SQL>

[EDIT based on Aleksej's ANSI JOIN suggestion, along with NUMTODSINTERVAL option]

SQL> select m.in_date,
  2         m.in_date - c.subtract_minute_value / (24 * 60) result
  3  from mytable1 m join config c on 1 = 1;

IN_DATE          RESULT
---------------- ----------------
02.02.2018 07:51 02.02.2018 07:36

SQL>
SQL> select m.in_date,
  2         m.in_date - numtodsinterval(c.subtract_minute_value, 'minute') result
  3  from mytable1 m join config c on 1 = 1;

IN_DATE          RESULT
---------------- ----------------
02.02.2018 07:51 02.02.2018 07:36

SQL>

Upvotes: 4

Related Questions