Solved

Difference in number of minutes between 2 timestamps

Posted on 2016-11-21
16
39 Views
Last Modified: 2016-11-21
I'm using the below sql in my query and the output I get on my report is as shown in the output below:

Both the created_date and m.act_onblk_datt_bu are timestamps that i'm coverting to America/New_York time.

FROM_TZ(min(q.created_date) over (partition by k.car_id,m.fll_id), 'Europe/London') AT TIME ZONE 'America/New_York'
- min(m.act_onblk_datt_bu) over (partition by k.car_id, m.fll_id) AT TIME ZONE 'America/New_York' as Number_of Mins


SQL query output format:

Number of Mins

+00 00:03:01.850000

I would like to format the output on my report to show the returned value as the number of minutes only. Based on this example I would like to display '3' minutes

Ultimately what I'm trying to achieve is the difference in number of minutes between 2 timestamps regardless of whether the difference runs into days, hours or just minutes. Rounding  up or down to the nearest minute would be fine.

I am a newbie to SQL and have tried to round the value, I have also tried using to_number but I cannot get these to work to get the output format I desire. Any help you can provide will be fantastic.

Please remember I'm an amateur so I'd really appreciate it if you could keep things simple for me.

Tks Jason
0
Comment
Question by:Jason Palmer
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 41895979
unfortunately timestamp differences are returned in intervals, but there is no direct interval-to-number translation.

fortunately, it's not that hard (albeit tedious) to do yourself.


SELECT start_ts,
       end_ts,
       end_ts - start_ts diff,
         EXTRACT(DAY FROM (end_ts - start_ts)) * 1440
       + EXTRACT(MINUTE FROM (end_ts - start_ts))
       + EXTRACT(SECOND FROM (end_ts - start_ts)) / 60 diff_minutes
  FROM (SELECT SYSTIMESTAMP start_ts, SYSTIMESTAMP + NUMTODSINTERVAL(98765, 'second') end_ts
          FROM DUAL)


and then extending that example to your specific timestamp math...

SELECT     EXTRACT(
               DAY FROM (  FROM_TZ(
                               MIN(q.created_date) OVER (PARTITION BY k.car_id, m.fll_id),
                               'Europe/London'
                           )
                               AT TIME ZONE 'America/New_York'
                         - MIN(m.act_onblk_datt_bu) OVER (PARTITION BY k.car_id, m.fll_id)
                               AT TIME ZONE 'America/New_York')
           )
         * 1440
       + EXTRACT(
             MINUTE FROM (  FROM_TZ(
                                MIN(q.created_date) OVER (PARTITION BY k.car_id, m.fll_id),
                                'Europe/London'
                            )
                                AT TIME ZONE 'America/New_York'
                          - MIN(m.act_onblk_datt_bu) OVER (PARTITION BY k.car_id, m.fll_id)
                                AT TIME ZONE 'America/New_York')
         )
       +   EXTRACT(
               SECOND FROM (  FROM_TZ(
                                  MIN(q.created_date) OVER (PARTITION BY k.car_id, m.fll_id),
                                  'Europe/London'
                              )
                                  AT TIME ZONE 'America/New_York'
                            - MIN(m.act_onblk_datt_bu) OVER (PARTITION BY k.car_id, m.fll_id)
                                  AT TIME ZONE 'America/New_York')
           )
         / 60
           AS number_of_mins
  FROM your_table
0
 
LVL 34

Expert Comment

by:johnsone
ID: 41895982
What you are being returned from timestamp math is an interval.  To convert that to seconds, take out the pieces and convert them.

You would need to work this into your query:

60 * extract(hour from number_of_mins) + extract(minute from number_of_mins)

That assumes that it doesn't exceed a day.  If it does, you need to add that.

Without the whole query it is difficult to give you something to plug in, but that is the idea.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41896001
another option...

convert your timestamp to dates, then substract.  That result will be in days (including fractions of a day), so simply multiply the difference by 1440 to convert days to minutes


SELECT   (  CAST(
                FROM_TZ(
                    MIN(q.created_date) OVER (PARTITION BY k.car_id, m.fll_id),
                    'Europe/London'
                )
                    AT TIME ZONE 'America/New_York' AS DATE
            )
          - CAST(
                MIN(m.act_onblk_datt_bu) OVER (PARTITION BY k.car_id, m.fll_id)
                    AT TIME ZONE 'America/New_York' AS DATE
            ))
       * 1440
           AS number_of_mins
  FROM your_table


the only downside to this method is you'll lose fractional second precision, but since you're ok with rounding the results to the minute anyway, that small deviation should be fine for your purposes.
1
 

Author Comment

by:Jason Palmer
ID: 41896031
thanks Sdstuber I'll give both your options a go right now
0
 

Author Comment

by:Jason Palmer
ID: 41896041
Excellent the 2nd options works, only minor issue i have is I would like to round the output to the nearest minute. is this easily achieved?

Right now the output is as follows:

3.01666666666666666666666666666666666667

I would like to see the output as follows:

3
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 41896047
SELECT ROUND(
             (  CAST(
                    FROM_TZ(
                        MIN(q.created_date) OVER (PARTITION BY k.car_id, m.fll_id),
                        'Europe/London'
                    )
                        AT TIME ZONE 'America/New_York' AS DATE
                )
              - CAST(
                    MIN(m.act_onblk_datt_bu) OVER (PARTITION BY k.car_id, m.fll_id)
                        AT TIME ZONE 'America/New_York' AS DATE
                ))
           * 1440
       )
           AS number_of_mins
  FROM your_table
1
 

Author Comment

by:Jason Palmer
ID: 41896070
when I include Round it sets the output of number_of_mins to '0' any ideas why?
0
 

Author Comment

by:Jason Palmer
ID: 41896076
Ignore my previous comment, my mistake I placed the closing bracket in the wrong place, query now outputs as I desire, many thanks for your kind help.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 41896077
did you put the round around the entire calculation?  or only around the subtraction?

round(x-y) * 1440  = 0  if x-y is only 3 minutes, because that's 3/1440 of one day, which rounds to 0,and then multiplied by 1440 is still 0

round((x-y) * 1440) = 3 minutes  if x-y is 3 minutes, because it generates the entire result:  3:01.6666666...   and then rounds.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41896079
great, glad you found it

if you need nothing else, please close the question
0
 

Author Closing Comment

by:Jason Palmer
ID: 41896081
Thanks Sdstuber you have helped me achieve what i set out to do very quickly , many thanks.
0
 
LVL 32

Expert Comment

by:awking00
ID: 41896086
You might try wrapping a trunc(timezone,'MI') around your timezone conversions -
TRUNC(FROM_TZ(min(q.created_date) over (partition by k.car_id,m.fll_id), 'Europe/London') AT TIME ZONE 'America/New_York','MI')
 - TRUNC(min(m.act_onblk_datt_bu) over (partition by k.car_id, m.fll_id) AT TIME ZONE 'America/New_York' ,'MI') as number_of_minutes
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41896088
Glad I could help.

I see by your profile you're new here.  Welcome aboard!
0
 
LVL 32

Expert Comment

by:awking00
ID: 41896097
I forgot to wrap my comment with multiplying by 1440, which it needs. Just as a note, that method automatically rounds to the minute.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 41896101
awking00 - using trunc can cause small rounding errors as it implicitly casts the timestamp to a date, and then truncates it.

for example

5:37:54 -   4:23:12 =   1:15:42 = 75.7,  or after rounding: 76 minutes

trunc(5:37:54) -trunc(4:23:12) = 5:37 - 4:23 = 1:15 = 75 minutes
0
 

Author Comment

by:Jason Palmer
ID: 41896107
Thanks to everyone who replied all have been very helpful.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problems using Provider=OraOLEDB.Oracle via VBScript/Classic ASP 5 47
join 2 views with 5 conditions 3 46
Export table into csv file in oracle 10 47
help on oracle query 5 33
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now