SQL - How to print calculated datetime result-CodePudding

[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?

CodePudding user response：

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.

CodePudding user response：

It is 30 minutes, if values really are as you stated:

SQL> with test (a_time, b_time) as
  2    (select to_date('2021/10/01 13:00:00', 'yyyy/mm/dd hh24:mi:ss'),
  3            to_date('2021/10/01 12:30:00', 'yyyy/mm/dd hh24:mi:ss')
  4     from dual
  5    )
  6  select (a_time - b_time) * 60 * 24 as minutes
  7  from test;

   MINUTES
----------
        30

SQL>

CodePudding user response：

Another option to print the value in format as elapsed_time hh:mi:ss

with test (a_time, b_time) as
  (select to_date('2021/10/01 13:00:00', 'yyyy/mm/dd hh24:mi:ss'),
          to_date('2021/10/01 12:27:30', 'yyyy/mm/dd hh24:mi:ss')
   from dual
  )
select (a_time - b_time) * 60 * 24 as minutes , 
       TO_CHAR(EXTRACT(HOUR FROM NUMTODSINTERVAL(a_time-b_time, 'DAY')), 'FM00')
       || ':' ||
       TO_CHAR(EXTRACT(MINUTE FROM NUMTODSINTERVAL(a_time-b_time, 'DAY')), 'FM00')
       || ':' ||
       TO_CHAR(EXTRACT(SECOND FROM NUMTODSINTERVAL(a_time-b_time, 'DAY')), 'FM00') as elapsed_time
from test;

Demo

SQL> with test (a_time, b_time) as
  (select to_date('2021/10/01 13:00:00', 'yyyy/mm/dd hh24:mi:ss'),
          to_date('2021/10/01 12:27:30', 'yyyy/mm/dd hh24:mi:ss')
   from dual
  )
select (a_time - b_time) * 60 * 24 as minutes ,
       TO_CHAR(EXTRACT(HOUR FROM NUMTODSINTERVAL(a_time-b_time, 'DAY')), 'FM00')
       || ':' ||
       TO_CHAR(EXTRACT(MINUTE FROM NUMTODSINTERVAL(a_time-b_time, 'DAY')), 'FM00')
       || ':' ||
       TO_CHAR(EXTRACT(SECOND FROM NUMTODSINTERVAL(a_time-b_time, 'DAY')), 'FM00') as elapsed_time
from test;

   MINUTES ELAPSED_TIME
---------- --------------
      32.5 00:32:30