Jason Palmer
asked on
Difference in number of minutes between 2 timestamps
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
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
- 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
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.
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.
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.
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.
ASKER
thanks Sdstuber I'll give both your options a go right now
ASKER
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.016666666666666666666666 6666666666 6667
I would like to see the output as follows:
3
Right now the output is as follows:
3.016666666666666666666666
I would like to see the output as follows:
3
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
when I include Round it sets the output of number_of_mins to '0' any ideas why?
ASKER
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.
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.
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.
great, glad you found it
if you need nothing else, please close the question
if you need nothing else, please close the question
ASKER
Thanks Sdstuber you have helped me achieve what i set out to do very quickly , many thanks.
You might try wrapping a trunc(timezone,'MI') around your timezone conversions -
TRUNC(FROM_TZ(min(q.create d_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
TRUNC(FROM_TZ(min(q.create
- TRUNC(min(m.act_onblk_datt
Glad I could help.
I see by your profile you're new here. Welcome aboard!
I see by your profile you're new here. Welcome aboard!
I forgot to wrap my comment with multiplying by 1440, which it needs. Just as a note, that method automatically rounds to the minute.
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
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
ASKER
Thanks to everyone who replied all have been very helpful.
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