By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table_name (user_id, modified_datetime, flag) as
select 123, to_date('2021/10/01 13:00:00', 'YYYY/MM/DD HH24:MI:SS'), 'A' from dual
union all
select 123, to_date('2021/10/01 12:30:00', 'YYYY/MM/DD HH24:MI:SS'), 'B' from dual
2 rows affected
select value from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
| VALUE |
|---|
| DD-MON-RR |
-- implicit (NLS) and explicit formatting of the dates
select modified_datetime, to_char(modified_datetime, 'yyyy/mm/dd hh24:mi:ss')
from table_name;
| MODIFIED_DATETIME | TO_CHAR(MODIFIED_DATETIME,'YYYY/MM/DDHH24:MI:SS') |
|---|---|
| 01-OCT-21 | 2021/10/01 13:00:00 |
| 01-OCT-21 | 2021/10/01 12:30:00 |
-- how implicit (NLS) string conmverts back to a date - with midnight time
select to_date('01-OCT-21'), to_char(to_date('01-OCT-21'), 'yyyy/mm/dd hh24:mi:ss')
from dual;
| TO_DATE('01-OCT-21') | TO_CHAR(TO_DATE('01-OCT-21'),'YYYY/MM/DDHH24:MI:SS') |
|---|---|
| 01-OCT-21 | 2021/10/01 00:00:00 |
-- how your dates appear after implicit conversion to string and backto dates
select modified_datetime,
to_date(modified_datetime, 'yyyy/mm/dd hh24:mi:ss'),
to_char(to_date(modified_datetime, 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss')
from table_name;
| MODIFIED_DATETIME | TO_DATE(MODIFIED_DATETIME,'YYYY/MM/DDHH24:MI:SS') | TO_CHAR(TO_DATE(MODIFIED_DATETIME,'YYYY/MM/DDHH24:MI:SS'),'YYYY/MM/DDHH24:MI:SS') |
|---|---|---|
| 01-OCT-21 | 21-OCT-01 | 0001/10/21 00:00:00 |
| 01-OCT-21 | 21-OCT-01 | 0001/10/21 00:00:00 |
-- your query using implicit conversion
select
a.user_id as user_id,
(to_date(a.modified_datetime, 'yyyy/mm/dd hh24:mi:ss') - to_date(b.modified_datetime, 'yyyy/mm/dd hh24:mi:ss')) * 60 * 24 as minutes
from
(select user_id, modified_datetime from table_name where /* some_conditions */ flag = 'A') a,
(select user_id, modified_datetime from table_name where /* other_conditions */ flag = 'B') b
where a.user_id = b.user_id
;
| USER_ID | MINUTES |
|---|---|
| 123 | 0 |
-- set session NLS to match your assumptions - do not rely on this
alter session set nls_date_format = 'yyyy/mm/dd/hh24:mi:ss';
-- implicit (NLS) and explicit formatting of the dates
select modified_datetime, to_char(modified_datetime, 'yyyy/mm/dd hh24:mi:ss')
from table_name;
| MODIFIED_DATETIME | TO_CHAR(MODIFIED_DATETIME,'YYYY/MM/DDHH24:MI:SS') |
|---|---|
| 2021/10/01/13:00:00 | 2021/10/01 13:00:00 |
| 2021/10/01/12:30:00 | 2021/10/01 12:30:00 |
-- how implicit (NLS) string conmverts back to a date - with midnight time
select to_date('01-OCT-21'), to_char(to_date('01-OCT-21'), 'yyyy/mm/dd hh24:mi:ss')
from dual;
| TO_DATE('01-OCT-21') | TO_CHAR(TO_DATE('01-OCT-21'),'YYYY/MM/DDHH24:MI:SS') |
|---|---|
| 0001/10/21/00:00:00 | 0001/10/21 00:00:00 |
-- how your dates appear after implicit conversion to string and backto dates
select modified_datetime,
to_date(modified_datetime, 'yyyy/mm/dd hh24:mi:ss'),
to_char(to_date(modified_datetime, 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss')
from table_name;
| MODIFIED_DATETIME | TO_DATE(MODIFIED_DATETIME,'YYYY/MM/DDHH24:MI:SS') | TO_CHAR(TO_DATE(MODIFIED_DATETIME,'YYYY/MM/DDHH24:MI:SS'),'YYYY/MM/DDHH24:MI:SS') |
|---|---|---|
| 2021/10/01/13:00:00 | 2021/10/01/13:00:00 | 2021/10/01 13:00:00 |
| 2021/10/01/12:30:00 | 2021/10/01/12:30:00 | 2021/10/01 12:30:00 |
-- your query using implicit conversion (but matching NLS)
select
a.user_id as user_id,
(to_date(a.modified_datetime, 'yyyy/mm/dd hh24:mi:ss') - to_date(b.modified_datetime, 'yyyy/mm/dd hh24:mi:ss')) * 60 * 24 as minutes
from
(select user_id, modified_datetime from table_name where /* some_conditions */ flag = 'A') a,
(select user_id, modified_datetime from table_name where /* other_conditions */ flag = 'B') b
where a.user_id = b.user_id
;
| USER_ID | MINUTES |
|---|---|
| 123 | 30 |
-- your query modified to avoid conversions (matching NLS, but not used)
select
a.user_id as user_id,
(a.modified_datetime - b.modified_datetime) * 60 * 24 as minutes
from
(select user_id, modified_datetime from table_name where /* some_conditions */ flag = 'A') a,
(select user_id, modified_datetime from table_name where /* other_conditions */ flag = 'B') b
where a.user_id = b.user_id
;
| USER_ID | MINUTES |
|---|---|
| 123 | 30 |
-- revert NLS setting to show it works with either, since it isn't used
alter session set nls_date_format = 'DD-MON-RR';
-- your query modified to avoid conversions (re-run with mismatched NLS, but not used, so still OK)
select
a.user_id as user_id,
(a.modified_datetime - b.modified_datetime) * 60 * 24 as minutes
from
(select user_id, modified_datetime from table_name where /* some_conditions */ flag = 'A') a,
(select user_id, modified_datetime from table_name where /* other_conditions */ flag = 'B') b
where a.user_id = b.user_id;
| USER_ID | MINUTES |
|---|---|
| 123 | 30 |
-- same query with ANSI joins
select
a.user_id as user_id,
(a.modified_datetime - b.modified_datetime) * 60 * 24 as minutes
from
(select user_id, modified_datetime from table_name where /* some_conditions */ flag = 'A') a
join
(select user_id, modified_datetime from table_name where /* other_conditions */ flag = 'B') b
on a.user_id = b.user_id;
| USER_ID | MINUTES |
|---|---|
| 123 | 30 |