{"id":2683,"date":"2019-03-25T17:40:44","date_gmt":"2019-03-25T20:40:44","guid":{"rendered":"http:\/\/xexeu.elipse.com.br\/pt\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/"},"modified":"2025-09-23T15:02:22","modified_gmt":"2025-09-23T18:02:22","slug":"retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/","title":{"rendered":"Retrieving averages of time (minutes, hours, and days) from an SQL Server database."},"content":{"rendered":"
In this article, we will show you how to retrieve the averages from the table fields in an SQL Server database of three different time units: minutes, hours, and days. To do so, you must isolate all values from the field DateTime – E3TimeStamp – in the desired time units, and then group all data by using this same unit. This means that in order to generate the average of the values grouped by minute, you must manipulate E3TimeStamp so that any bit of information smaller than one minute is omitted, (in this case, information regarding seconds and milliseconds). This can be accomplished by using CAST command (which converts data types) and by performing some calculations. As for these calculations, you must remember that one unit of this date value is equal to one day. To calculate this average, you can use AVG command.<\/p>\n

– Averages per minute<\/span>: to obtain the averages per minute, you must retrieve E3TimeStamp’s seconds and milliseconds. To do so, multiply this field by 24 (to convert the time base into hours), and then by 60 (to convert it into minutes). The result must then be handled to isolate the integer, which corresponds to the minutes. Finally, divide everything by the same vale (1440 = 24 x 60) so that the data is converted again to date format. The result of these calculations will be used to group the data.
\n
SELECT CAST(CAST(CAST(CAST(E3TimeStamp as float)*1440 as bigint) as float)\/1440 as datetime) as Minute, AVG(Field) as Average<\/span>
FROM Table<\/span>
GROUP BY CAST(CAST(CAST(CAST(E3TimeStamp as float)*1440 as bigint) as float)\/1440 as datetime)<\/span><\/p>\n

– Averages per hour<\/span>:<\/p>\n

SELECT CAST(CAST(CAST(CAST(E3TimeStamp as float)*24 as bigint) as float)\/24 as datetime) as Hour, AVG(Field) as Average<\/span>
FROM Table<\/span>
GROUP BY CAST(CAST(CAST(CAST(E3TimeStamp as float)*24 as bigint) as float)\/24 as datetime)<\/span><\/p>\n

– Averages per day<\/span>:<\/p>\n

SELECT CAST(FLOOR(CAST(E3TimeStamp as float)) as datetime) as Dia, AVG(Field) as Average
\nFROM Tabela
\nGROUP BY CAST(FLOOR(CAST(E3TimeStamp as float)) as datetime)<\/span><\/div>\n","protected":false},"excerpt":{"rendered":"

In this article, we will show you how to retrieve the averages from the table fields in an SQL Server database of three different time units: minutes, hours, and days.…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0},"categories":[743],"tags":[],"yoast_head":"\nRetrieving averages of time (minutes, hours, and days) from an SQL Server database. - Elipse Knowledgebase<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Retrieving averages of time (minutes, hours, and days) from an SQL Server database.\" \/>\n<meta property=\"og:description\" content=\"In this article, we will show you how to retrieve the averages from the table fields in an SQL Server database of three different time units: minutes, hours, and days.…\" \/>\n<meta property=\"og:url\" content=\"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/\" \/>\n<meta property=\"og:site_name\" content=\"Elipse Knowledgebase\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/elipsesoftware\" \/>\n<meta property=\"article:published_time\" content=\"2019-03-25T20:40:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-09-23T18:02:22+00:00\" \/>\n<meta name=\"author\" content=\"Elipse Software\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Elipse Software\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/\"},\"author\":{\"name\":\"Elipse Software\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/person\/def69ea453ea60b250497b89225a9f87\"},\"headline\":\"Retrieving averages of time (minutes, hours, and days) from an SQL Server database.\",\"datePublished\":\"2019-03-25T20:40:44+00:00\",\"dateModified\":\"2025-09-23T18:02:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/\"},\"wordCount\":357,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#organization\"},\"articleSection\":[\"E3Query\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/\",\"url\":\"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/\",\"name\":\"[:pt]Retrieving averages of time (minutes, hours, and days) from an SQL Server database.[:] - Elipse Knowledgebase\",\"isPartOf\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#website\"},\"datePublished\":\"2019-03-25T20:40:44+00:00\",\"dateModified\":\"2025-09-23T18:02:22+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"In\u00edcio\",\"item\":\"https:\/\/kb.elipse.com.br\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Retrieving averages of time (minutes, hours, and days) from an SQL Server database.\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/kb.elipse.com.br\/#website\",\"url\":\"https:\/\/kb.elipse.com.br\/\",\"name\":\"Elipse Knowledgebase\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/kb.elipse.com.br\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/kb.elipse.com.br\/#organization\",\"name\":\"Elipse Software\",\"url\":\"https:\/\/kb.elipse.com.br\/\",\"sameAs\":[\"http:\/\/www.facebook.com\/elipsesoftware\"],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png\",\"contentUrl\":\"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png\",\"width\":161,\"height\":58,\"caption\":\"Elipse Software\"},\"image\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/person\/def69ea453ea60b250497b89225a9f87\",\"name\":\"Elipse Software\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ff1f7ec38f4687b06f6851d97b3cd2d0?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ff1f7ec38f4687b06f6851d97b3cd2d0?s=96&d=mm&r=g\",\"caption\":\"Elipse Software\"},\"url\":\"https:\/\/kb.elipse.com.br\/en\/author\/webmasterelipse-com-br\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Retrieving averages of time (minutes, hours, and days) from an SQL Server database. - Elipse Knowledgebase","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/","og_locale":"en_US","og_type":"article","og_title":"[:pt]Retrieving averages of time (minutes, hours, and days) from an SQL Server database.[:] - Elipse Knowledgebase","og_description":"In this article, we will show you how to retrieve the averages from the table fields in an SQL Server database of three different time units: minutes, hours, and days.…","og_url":"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/","og_site_name":"Elipse Knowledgebase","article_publisher":"http:\/\/www.facebook.com\/elipsesoftware","article_published_time":"2019-03-25T20:40:44+00:00","article_modified_time":"2025-09-23T18:02:22+00:00","author":"Elipse Software","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Elipse Software","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/#article","isPartOf":{"@id":"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/"},"author":{"name":"Elipse Software","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/person\/def69ea453ea60b250497b89225a9f87"},"headline":"Retrieving averages of time (minutes, hours, and days) from an SQL Server database.","datePublished":"2019-03-25T20:40:44+00:00","dateModified":"2025-09-23T18:02:22+00:00","mainEntityOfPage":{"@id":"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/"},"wordCount":357,"commentCount":0,"publisher":{"@id":"https:\/\/kb.elipse.com.br\/#organization"},"articleSection":["E3Query"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/","url":"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/","name":"[:pt]Retrieving averages of time (minutes, hours, and days) from an SQL Server database.[:] - Elipse Knowledgebase","isPartOf":{"@id":"https:\/\/kb.elipse.com.br\/#website"},"datePublished":"2019-03-25T20:40:44+00:00","dateModified":"2025-09-23T18:02:22+00:00","breadcrumb":{"@id":"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/kb.elipse.com.br\/retrieving-averages-of-time-minutes-hours-and-days-from-an-sql-server-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"In\u00edcio","item":"https:\/\/kb.elipse.com.br\/en\/"},{"@type":"ListItem","position":2,"name":"Retrieving averages of time (minutes, hours, and days) from an SQL Server database."}]},{"@type":"WebSite","@id":"https:\/\/kb.elipse.com.br\/#website","url":"https:\/\/kb.elipse.com.br\/","name":"Elipse Knowledgebase","description":"","publisher":{"@id":"https:\/\/kb.elipse.com.br\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/kb.elipse.com.br\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/kb.elipse.com.br\/#organization","name":"Elipse Software","url":"https:\/\/kb.elipse.com.br\/","sameAs":["http:\/\/www.facebook.com\/elipsesoftware"],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/","url":"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png","contentUrl":"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png","width":161,"height":58,"caption":"Elipse Software"},"image":{"@id":"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/person\/def69ea453ea60b250497b89225a9f87","name":"Elipse Software","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ff1f7ec38f4687b06f6851d97b3cd2d0?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ff1f7ec38f4687b06f6851d97b3cd2d0?s=96&d=mm&r=g","caption":"Elipse Software"},"url":"https:\/\/kb.elipse.com.br\/en\/author\/webmasterelipse-com-br\/"}]}},"_links":{"self":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts\/2683"}],"collection":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/comments?post=2683"}],"version-history":[{"count":1,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts\/2683\/revisions"}],"predecessor-version":[{"id":17261,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts\/2683\/revisions\/17261"}],"wp:attachment":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/media?parent=2683"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/categories?post=2683"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/tags?post=2683"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}