﻿ How to split the two timestamp based on interval time in IRIS Database? |
Question
· Mar 28

How to split the two timestamp based on interval time in IRIS Database?

I have two timestamp values and also I have interval time, I need to split the timestamp based on the interval time. Like

Start Time is '2024-01-01T10:00:00'
End Time is '2024-01-01T11:00:00'
Interval Time is 15 minutes

I expected the result is:

'2024-01-01T10:15:00'
'2024-01-01T10:30:00'
'2024-01-01T10:45:00'
'2024-01-01T11:00:00'

Discussion (11)1
Log in or sign up to continue
``````/// start, end: timestamp format
/// int       : the interval in seconds
/// mod       : 0 = use the time value as is
///            +1 = round-up the timestamp to a multiple of <int>
///            -1 = round-down the timestamp to a multiple of <int>
///
/// return an JSON array: [time1, time2, ... timeN]
///
ClassMethod Intervals(start, end, int = 15*60, mod = 0) As %DynamicArray
{
set res=[], t(0)=\$zdth(start,3,1), t(1)=\$zdth(end,3,1)

for i=0,1 {
set t(i)=t(i)*86400+\$p(t(i),",",2)
if mod,t(i)#int { set t(i)=t(i)-(t(i)#int) set:mod>0 t(i)=t(i)+int }
}

for t=t(0):int:t(1) do res.%Push(\$zdt(t\86400_","_(t#86400),3,1))
quit res
}
``````

Hai @Julius Kavay

Thank you for your suggestion,

I have retrieved the huge records, so it will little more complicated compared with query calls,  Maybe possibly causing the performance issue of using this implementation. So we must use the database query.

If you process thousands of time splits,  it's a good idea using %List or just a simple %String instead of JSON - you can have a time savings by factor of about 10!

``````/// old line
set res=[]
for t=t(0):int:t(1) do res.%Push(\$zdt(t\86400_","_(t#86400),3,1))
quit res

/// new line
set res=""
for t=t(0):int:t(1) set res=res_\$lb(\$zdt(t\86400_","_(t#86400),3,1))
quit res

/// or
set res=""
for t=t(0):int:t(1) set res=res_","_\$zdt(t\86400_","_(t#86400),3,1)
quit \$e(res,2,*)``````

To see the differences, try loops like this

``````/// with JSON
s h=\$zh f i=1:1:1E5 { s r=[] f j=1:1:10 { d r.%Push("abcd") } } w \$zh-h,!

/// with %List
s h=\$zh f i=1:1:1E6 { s r="" f j=1:1:10 { s r=r_\$lb("abcd") } } w \$zh-h,!

/// with %String
s h=\$zh f i=1:1:1E6 { s r="" f j=1:1:10 { s r=r_","_"abcd" } } w \$zh-h,!``````

Can you please ensure, my answer matches the original question? Thanks! Usually one makes a new question instead of changing an old question to a new one. Currently both my answers hang around without an context to the above question.

Hello @Julius Kavay

SORRY for the inconvenience!! I have updated my query in the below thread. I am not sure your answers match my question, because my expected answer is different. But I would like to thank and respect to you​​​​​. Can you please assist me?

PLEASE!
if you have a different question then PLEASE don't change the original content
this breaks the total information flow.
Existing replies will look like nonsense!

Hello @Robert Cemper

SORRY for the inconvenience!! I made some additional changes to the original content to add more clarity, that is the reason I have changed the content. This won't happen again in the future. I have updated the original content the same as previously. But I would like to thank and respect you.

Here, is the My Query

​

``````SELECT
COALESCE(SUM(CASE WHEN service_log.value_after_changing = 'GENERATE' THEN 1 ELSE 0 END), 0) AS generatedCount,
COALESCE(SUM(CASE WHEN service_log.value_after_changing = 'FINISH' THEN 1 ELSE 0 END), 0) AS finishedCount,
COALESCE(SUM(CASE WHEN service_log.value_after_changing = 'DROPOUT' THEN 1 ELSE 0 END), 0) AS dropoutCount,
COALESCE(SUM(CASE WHEN service_log.value_after_changing = 'CANCEL' THEN 1 ELSE 0 END), 0) AS cancelledCount
FROM
service_log
WHERE
service_log.id = 11 AND
service_log.created_at BETWEEN '2024-03-28T07:00:00' AND '2024-03-28T08:00:00'
GROUP BY
FLOOR(DATEDIFF('MINUTE', TO_TIMESTAMP('2024-03-28T07:00:00', 'YYYY-MM-DDTHH:MI:SS'), created_at) / 15 (i.e.,interval time));``````

This query implementation currently retrieve the data when available in each interval period if data not available in respective interval period no providing any data but my expectation is when data not available need to provide all counts as 0 value.

This is current response for fourth interval (07:46 to 08:00) :
[
{
"cancelled": 0,
"dropOut": 0,
"finished": 16,
"issued": 2
}
]
Expected response like this:
[
{
"cancelled": 0,
"dropOut": 0,
"finished": 0,
"issued": 0
},
{
"cancelled": 0,
"dropOut": 0,
"finished": 0,
"issued": 0
},
{
"cancelled": 0,
"dropOut": 0,
"finished": 0,
"issued": 0
},
{
"cancelled": 0,
"dropOut": 0,
"finished": 2,
"issued": 16
}
]

Thanks once again! @Robert Cemper for guidance

Example of a stored procedure

The result of calling a stored procedure in the Management Portal:

`SELECT * FROM dc.Intervals({ts '2024-01-01 10:00:00'},{ts '2024-01-01 11:00:00'},15)`
Display/ODBC Mode
intStart intEnd
2024-01-01 10:00:00 2024-01-01 10:14:59.999999
2024-01-01 10:15:00 2024-01-01 10:29:59.999999
2024-01-01 10:30:00 2024-01-01 10:44:59.999999
2024-01-01 10:45:00 2024-01-01 11:00:00.000000

Logical Mode

intStart intEnd
1154625607806846976 1154625608706846975
1154625608706846976 1154625609606846975
1154625609606846976 1154625610506846975
1154625610506846976 1154625611406846976

Accordingly, your query needs to be rewritten, for example:

```SELECT
intStart,
intEnd,
COALESCE((SELECT SUM(CASE WHEN value_after_changing = 'GENERATE' THEN 1 ELSE 0 END) FROM service_log WHERE id = 11 AND created_at BETWEEN intStart AND intEnd), 0) generatedCount,
COALESCE((SELECT SUM(CASE WHEN value_after_changing = 'FINISH' THEN 1 ELSE 0 END) FROM service_log WHERE id = 11 AND created_at BETWEEN intStart AND intEnd), 0) finishedCount,
COALESCE((SELECT SUM(CASE WHEN value_after_changing = 'DROPOUT' THEN 1 ELSE 0 END) FROM service_log WHERE id = 11 AND created_at BETWEEN intStart AND intEnd), 0) dropoutCount,
COALESCE((SELECT SUM(CASE WHEN value_after_changing = 'CANCEL' THEN 1 ELSE 0 END) FROM service_log WHERE id = 11 AND created_at BETWEEN intStart AND intEnd), 0) cancelledCount
FROM dc.Intervals({ts '2024-01-01 10:00:00'},{ts '2024-01-01 11:00:00'},15)```