{"id":512,"date":"2021-08-31T17:18:01","date_gmt":"2021-08-31T15:18:01","guid":{"rendered":"https:\/\/ilmarkerm.eu\/blog\/?p=512"},"modified":"2021-08-31T17:18:01","modified_gmt":"2021-08-31T15:18:01","slug":"timestamp-rollup-functions-for-oracle","status":"publish","type":"post","link":"https:\/\/ilmarkerm.eu\/blog\/2021\/08\/timestamp-rollup-functions-for-oracle\/","title":{"rendered":"TIMESTAMP rollup functions for Oracle"},"content":{"rendered":"\n<p>I&#8217;ve been using InfluxDB for storing metrics, but it has hit its limitations &#8211; pretty huge memory consumption, so can&#8217;t really store some metrics for longer time and InfluxQL language itself is very limiting for analytics. So looking now into replacing it with Oracle 21c with JSON in-memory store and other goodies.<\/p>\n\n\n\n<p>But <a rel=\"noreferrer noopener\" href=\"https:\/\/docs.influxdata.com\/influxdb\/v1.8\/query_language\/explore-data\/#group-by-time-intervals\" target=\"_blank\">InfluxDB has a nice GROUP BY time(&lt;time interval>) clause<\/a> that lets you group timestamps into arbitary length time groups &#8211; 1 minute, 2 minute, 5 minute, 15 minute, 3 hours&#8230;<\/p>\n\n\n\n<p>In Oracle you can use built in TRUNC() or ROUND() to round timestamp into 1 MINUTE, 1 HOUR, 1 DAY, but not to 5 minute and so on.<\/p>\n\n\n\n<p>Here are my functions to fix this issue.<\/p>\n\n\n\n<script src=\"https:\/\/gist.github.com\/ilmarkerm\/6520eae3ee546723de8ef714a0b2e92e.js\"><\/script>\n\n\n\n<p>Yes you are seeing correct, all TIMESTAMP data types. No DATE. Please stop using this old expired DATE data type. TIMESTAMPS are better as ISO joins are better \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;ve been using InfluxDB for storing metrics, but it has hit its limitations &#8211; pretty huge memory consumption, so can&#8217;t really store some metrics for longer time and InfluxQL language itself is very limiting for analytics. So looking now into replacing it with Oracle 21c with JSON in-memory store and other goodies. But InfluxDB has [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4,13],"class_list":["post-512","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oracle","tag-plsql"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/512","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=512"}],"version-history":[{"count":1,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/512\/revisions"}],"predecessor-version":[{"id":513,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/512\/revisions\/513"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=512"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=512"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=512"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}