round timestamp up in 5 minute interval
(1) By Bob (bob202) on 2022-02-22 17:18:47 [link] [source]
I have a table that has a timestamp column and an integer column. I want to do a query so that the timestamp is rounded up e.g. 2022/02/22 13:03:45 becomes 2022/02/22 13:05:00 and I also want to count the number of rows that are within the 5-minute interval and get an average integer value over the 5-minute interval. Any help would be appreciated.
(2) By Larry Brasfield (larrybr) on 2022-02-22 17:40:54 in reply to 1 [link] [source]
Here is a way to round up to the next 5 minute marker:
select datetime(ceil(julianday('now')*24*12)/24/12);
.
You can use GROUP BY <a similar expression> to get your aggregate functions to follow such a rounding.
(3.1) By Bob (bob202) on 2022-02-22 18:06:28 edited from 3.0 in reply to 2 [link] [source]
Thanks for the help but ceil doesn't seem to be working with sqlite
(4) By Stephan Beal (stephan) on 2022-02-22 18:03:44 in reply to 3.0 [link] [source]
but ciel doesn't seem to be working with sqlite
Maybe "ceil" will?
(5) By Bob (bob202) on 2022-02-22 18:06:11 in reply to 4 [link] [source]
Oh sorry I ment ceil
(6.2) By Larry Brasfield (larrybr) on 2022-02-22 18:16:40 edited from 6.1 in reply to 5 [link] [source]
Deleted(7) By Bob (bob202) on 2022-02-22 18:16:58 in reply to 6.0 [link] [source]
Thank you very much, it works
(8) By Larry Brasfield (larrybr) on 2022-02-22 18:19:01 in reply to 7 [link] [source]
It fails for times that are exactly rounded already.
(9) By Bob (bob202) on 2022-02-22 18:20:49 in reply to 8 [link] [source]
Ohhhh I see
(10.1) By Bob (bob202) on 2022-02-22 21:17:06 edited from 10.0 in reply to 8 [link] [source]
Deleted(11) By anonymous on 2022-02-22 23:30:55 in reply to 8 [source]
This doesn't:
datetime(ceil(julianday('now','-1 second')*24*12)/24/12)
(12) By Larry Brasfield (larrybr) on 2022-02-22 23:44:17 in reply to 11 [link] [source]
The solution using ceil(...) was fine. That's the simplest way to round up. The OP apparently does not have ceil() built into the library version being used. So I concocted a variation using round(...) that does almost the same thing as ceil(), but not for inputs that are already rounded. That was the failure to which I referred, in a post deleted for irreparable wrongness.
(13.1) Originally by anonymous with edits by Stephan Beal (stephan) on 2022-09-01 16:43:49 from 13.0 in reply to 12 [link] [source]
datetime(ceil(julianday('now','localtime')*24*12)/24./12)
----------------------------------------------------^
no need for '-1 second' but rather division needs to be floating point
((edited by admin to fix markdown formatting.))