1

I have to calculate time difference in minutes from current(sysdate) and modified time:-

to_date(to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')
- to_date(to_char(modified, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')

but problem is to_char returns proper time:-

to_char(whenmodified, 'YYYY-MM-DD HH24:MI:SS')

Outputs 2016-05-23 14:55:50

and to_date doesn’t show time:-

to_date(to_char(modified, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')

Outputs: 2016-05-23

Please assist how I can get time difference by converting to_char to to_date.

NOTE:

  1. I cant do sysdate-modified because both sysdate and modified gives date without time e.g 2016-05-23

  2. Using to_char for sysdate or modified give date with time 2016-05-23 14:55:50

  3. As we cant subtracts dates in to_char function I am again converting back them to to_date for getting time.

I am expecting: 2016-05-23 14:55:50 - 2016-05-23 14:53:50 = 2 min

nilFi
  • 197
  • 2
  • 18

1 Answers1

3

I have to calculate time difference in minutes from current(sysdate) and modified time

Oracle Setup:

CREATE TABLE table_name ( modified DATE );

INSERT INTO table_name
SELECT TIMESTAMP '2016-05-23 14:20:00' FROM DUAL UNION ALL
SELECT TIMESTAMP '2016-05-23 00:00:00' FROM DUAL UNION ALL
SELECT TIMESTAMP '2016-05-01 00:00:00' FROM DUAL UNION ALL
SELECT TIMESTAMP '2016-01-01 00:00:00' FROM DUAL;

Query:

SELECT ( sysdate - modified ) * 24 * 60 AS minute_difference
FROM   table_name;

Output:

MINUTE_DIFFERENCE
-----------------
       3.66666667 
       863.666667 
       32543.6667 
       206783.667

And to address your comment that:

to_date doesn’t show time

A date always has a time component and never has a format internally to the database (it is represented by 7 or 8 bytes) - the formatting of a date is done by the client program that you use to access the database (and often the default is not to show the time component - however, the time component still exists).

You can change this either in the preferences of your client program or, if they don't use that to control it, by changing the NLS_DATE_FORMAT session parameter:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
MT0
  • 143,790
  • 11
  • 59
  • 117
  • not sure what it does exactly, I am working on prod data. Do I need to run ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; before running my select query? will if affect other any things, please let me know – nilFi May 23 '16 at 14:09
  • and important thing is I just have read only access to database – nilFi May 23 '16 at 14:15
  • @nilFi: As MT0 said: The date is a datetime really, and you can format it according to your wishes in your app when you want to display it. (I guess there is an app, because you say you are reading production data.) If you are using an app that uses the Oracle setting NLS_DATE_FORMAT in order to display a datetime and you cannot change that setting, then you can use TO_CHAR to specify the desired format in your query. You know that function already. – Thorsten Kettner May 23 '16 at 14:32
  • @nilFi Try it yourself on a non-production database `SHUTDOWN IMMEDIATE` then `STARTUP MOUNT` then `ALTER DATABASE OPEN READ ONLY` then `ALTER SESSION SET NLS_DATE_FORMAT = 'hh24 yyyy ss mi mm dd'`. It will work even though the DB is read-only and you will get the date in your very own weird format. Try connecting as a different user and they do not get your date format as session parameters are per-user. – MT0 May 23 '16 at 14:35
  • @ThorstenKettner yes I can use TO_CHAR to specify desired format but can not do date time subtraction, where date are in text format. – nilFi May 23 '16 at 14:44
  • @nilFi: Seems you haven't understood yet what MT0 told you. You store datetimes in an Oracle database in the DATE format; it consists of a date plus a time. You can add minutes, subtract seconds, compare datetimes, get the difference in days, minutes, hours, ... This has nothing to do with displaying the data. When you want to show a datetime specify the Format. Usually this is done in your app (e.g. in Java, PHP, whatever) and you decide there whether to show the date only or date plus time or time only... – Thorsten Kettner May 23 '16 at 15:57
  • ... Some apps (sqlplus, Toad, etc.) use the `NLS_DATE_FORMAT` to decide how to display a datetime. So when you select `SYSDATE` for example, they take the `NLS_DATE_FORMAT` and display the datetime accordingly. If you don't want this, then either change this setting or use `TO_CHAR` in your query to force a specific display (e.g. `TO_CHAR(SYSDATE,'hh24:mi')`). – Thorsten Kettner May 23 '16 at 16:00
  • And anyway, a "time difference in minutes" is not a datetime, but a time interval. When subtracting two datetimes in Oracle you get the difference in days as a decimal number. You can multiply it to get hours, minutes or seconds, etc. as shown. Or use `numtodsinterval(, 'minute')` to display the minutes as an interval. – Thorsten Kettner May 23 '16 at 16:15
  • The other alternative to get the difference between two times as an `INTERVAL` datatype is to cast both `DATE`s to `TIMESTAMP`s - like this: `SELECT CAST( SYSDATE AS TIMESTAMP ) - CAST( modified AS TIMESTAMP ) FROM table_name` which will output something like `+01 05:30:39.000000` as the days, hours, minutes and seconds between the two times. – MT0 May 23 '16 at 20:21
  • @Thorsten Kettner thanks to notify that thereis nothing to do with displaying the data. I was under impression that as I am unable to see time it means time is not associated with the fields :) – nilFi May 24 '16 at 06:26