SQL - How to print calculated datetime result

Question

[user@hostname ~]$ sqlplus -v
SQL*Plus: Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

select
a.user_id as user_id, 
/* convert to minutes */
(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) a,
(select user_id, modified_datetime, from table_name where other_conditions) b,
where a.user_id = b.user_id
;

If a.modified_datetime is '2021/10/01 13:00:00' and b.modified_datetime is '2021/10/01 12:30:00', the result of this query would be:

user_id minutes
------- -------
  12345      30

However, I run this query via sqlplus, it returns

user_id minutes
------- -------
  12345       0

What's wrong with this query, or need to set some options to sqlplus?

Answer 1

it is stored in a date column

As @a_horse_with_no_name pointed out in a comment, calling to_date() on a value that is already a date is incorrect, and is causing your apparent problem.

When you do:

to_date(a.modified_datetime, 'yyyy/mm/dd hh24:mi:ss')

you are implicitly converting the date to a string using the current session NLS settings; with the still-default DD-MON-RR for example, that is really doing:

to_date(a.modified_datetime, 'yyyy/mm/dd hh24:mi:ss')
-> to_date(to_char(a.modified_datetime), 'yyyy/mm/dd hh24:mi:ss')
-> to_date(to_char(a.modified_datetime, <NLS_DATE_FORMAT>), 'yyyy/mm/dd hh24:mi:ss')
-> to_date(to_char(a.modified_datetime, 'DD-MON-RR'), 'yyyy/mm/dd hh24:mi:ss')
-> to_date('01-OCT-21', 'yyyy/mm/dd hh24:mi:ss')
-> 0001-10-21 00:00:00

As both values end up as midnight, the difference between them is calculated as zero minutes.

You could change the NLS settings, which is fragile; or explicitly convert the the date to a string in the right format - but neither is necessary or useful.

You should not have the to_date() calls at all, and can just subtract the date values directly from each other:

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) a,
(select user_id, modified_datetime from table_name where other_conditions) b
where a.user_id = b.user_id;

or using 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) a
join
(select user_id, modified_datetime from table_name where other_conditions) b
on a.user_id = b.user_id;

db<>fiddle showing the results of the implicit conversions, and the correct output.