{"id":4042,"date":"2024-09-18T08:34:31","date_gmt":"2024-09-18T00:34:31","guid":{"rendered":"https:\/\/five.co\/?p=4042"},"modified":"2026-05-07T08:24:00","modified_gmt":"2026-05-07T00:24:00","slug":"sql-date-truncation-how-to-guide","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/sql-date-truncation-how-to-guide\/","title":{"rendered":"SQL Date Truncation (How to Guide)"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Breakdown of SQL Date Truncation <\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Date manipulation is a crucial skill for any database developer or data analyst. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Among the various date operations, truncation stands out as a particularly useful technique for time-based analysis and reporting. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This comprehensive guide delves into the intricacies of date truncation in SQL, exploring its applications across different database systems and showcasing how it can transform your data analysis capabilities.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-mm2yj wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-mm2yj \"><div class=\"eb-toc-container eb-toc-mm2yj  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;Breakdown of SQL Date Truncation &quot;,&quot;text&quot;:&quot;Breakdown of SQL Date Truncation &quot;,&quot;link&quot;:&quot;breakdown-of-sql-date-truncation&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Understanding Date Truncation&quot;,&quot;text&quot;:&quot;Understanding Date Truncation&quot;,&quot;link&quot;:&quot;understanding-date-truncation&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;SQL Server: Using DATETRUNC()&quot;,&quot;text&quot;:&quot;SQL Server: Using DATETRUNC()&quot;,&quot;link&quot;:&quot;sql-server-using-datetrunc&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;PostgreSQL: Using DATE_TRUNC()&quot;,&quot;text&quot;:&quot;PostgreSQL: Using DATE_TRUNC()&quot;,&quot;link&quot;:&quot;postgresql-using-date_trunc&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;MySQL: Emulating Date Truncation&quot;,&quot;text&quot;:&quot;MySQL: Emulating Date Truncation&quot;,&quot;link&quot;:&quot;mysql-emulating-date-truncation&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Oracle: DATE_TRUNC() and TRUNC()&quot;,&quot;text&quot;:&quot;Oracle: DATE_TRUNC() and TRUNC()&quot;,&quot;link&quot;:&quot;oracle-date_trunc-and-trunc&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Practical Applications of Date Truncation&quot;,&quot;text&quot;:&quot;Practical Applications of Date Truncation&quot;,&quot;link&quot;:&quot;practical-applications-of-date-truncation&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Time-Based Aggregations&quot;,&quot;text&quot;:&quot;Time-Based Aggregations&quot;,&quot;link&quot;:&quot;time-based-aggregations&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Fiscal Year Analysis&quot;,&quot;text&quot;:&quot;Fiscal Year Analysis&quot;,&quot;link&quot;:&quot;fiscal-year-analysis&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Cohort Analysis&quot;,&quot;text&quot;:&quot;Cohort Analysis&quot;,&quot;link&quot;:&quot;cohort-analysis&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Performance Considerations&quot;,&quot;text&quot;:&quot;Performance Considerations&quot;,&quot;link&quot;:&quot;performance-considerations&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Common Issues and How to Avoid Them&quot;,&quot;text&quot;:&quot;Common Issues and How to Avoid Them&quot;,&quot;link&quot;:&quot;common-issues-and-how-to-avoid-them&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Advanced Techniques&quot;,&quot;text&quot;:&quot;Advanced Techniques&quot;,&quot;link&quot;:&quot;advanced-techniques&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Rolling Time Windows&quot;,&quot;text&quot;:&quot;Rolling Time Windows&quot;,&quot;link&quot;:&quot;rolling-time-windows&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Custom Time Periods&quot;,&quot;text&quot;:&quot;Custom Time Periods&quot;,&quot;link&quot;:&quot;custom-time-periods&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Conclusion&quot;,&quot;text&quot;:&quot;Conclusion&quot;,&quot;link&quot;:&quot;conclusion&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;Breakdown of SQL Date Truncation &quot;,&quot;value&quot;:&quot;breakdown-of-sql-date-truncation&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Understanding Date Truncation&quot;,&quot;value&quot;:&quot;understanding-date-truncation&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;SQL Server: Using DATETRUNC()&quot;,&quot;value&quot;:&quot;sql-server-using-datetrunc&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;PostgreSQL: Using DATE_TRUNC()&quot;,&quot;value&quot;:&quot;postgresql-using-date_trunc&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;MySQL: Emulating Date Truncation&quot;,&quot;value&quot;:&quot;mysql-emulating-date-truncation&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Oracle: DATE_TRUNC() and TRUNC()&quot;,&quot;value&quot;:&quot;oracle-date_trunc-and-trunc&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Practical Applications of Date Truncation&quot;,&quot;value&quot;:&quot;practical-applications-of-date-truncation&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Time-Based Aggregations&quot;,&quot;value&quot;:&quot;time-based-aggregations&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Fiscal Year Analysis&quot;,&quot;value&quot;:&quot;fiscal-year-analysis&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Cohort Analysis&quot;,&quot;value&quot;:&quot;cohort-analysis&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Performance Considerations&quot;,&quot;value&quot;:&quot;performance-considerations&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Common Issues and How to Avoid Them&quot;,&quot;value&quot;:&quot;common-issues-and-how-to-avoid-them&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Advanced Techniques&quot;,&quot;value&quot;:&quot;advanced-techniques&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Rolling Time Windows&quot;,&quot;value&quot;:&quot;rolling-time-windows&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Custom Time Periods&quot;,&quot;value&quot;:&quot;custom-time-periods&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Conclusion&quot;,&quot;value&quot;:&quot;conclusion&quot;,&quot;isDelete&quot;:false}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#breakdown-of-sql-date-truncation\">Breakdown of SQL Date Truncation <\/a><li><a href=\"#understanding-date-truncation\">Understanding Date Truncation<\/a><li><a href=\"#sql-server-using-datetrunc\">SQL Server: Using DATETRUNC()<\/a><li><a href=\"#postgresql-using-date_trunc\">PostgreSQL: Using DATE_TRUNC()<\/a><li><a href=\"#mysql-emulating-date-truncation\">MySQL: Emulating Date Truncation<\/a><li><a href=\"#oracle-date_trunc-and-trunc\">Oracle: DATE_TRUNC() and TRUNC()<\/a><li><a href=\"#practical-applications-of-date-truncation\">Practical Applications of Date Truncation<\/a><li><a href=\"#performance-considerations\">Performance Considerations<\/a><li><a href=\"#common-issues-and-how-to-avoid-them\">Common Issues and How to Avoid Them<\/a><li><a href=\"#advanced-techniques\">Advanced Techniques<\/a><li><a href=\"#conclusion\">Conclusion<\/a><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Understanding Date Truncation<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">At its core, date truncation involves reducing a date or timestamp to a less precise time interval. For instance, you might want to truncate a timestamp to the nearest day, month, or year. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This operation is invaluable when you need to group data by time periods or when you want to remove the time component from a datetime field.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">SQL Server: Using DATETRUNC()<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">SQL Server 2022 introduced the DATETRUNC() function, aligning it with other major database systems. Here&#8217;s how you can use it:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT DATETRUNC(month, '2023-03-15 14:30:00')<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">DATETRUNC<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">month<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;2023-03-15 14:30:00&#39;<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This would return &#8216;2023-03-01 00:00:00&#8217;, effectively truncating the date to the start of the month.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">DATETRUNC() supports various interval options:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>year<\/li>\n\n\n\n<li>quarter<\/li>\n\n\n\n<li>month<\/li>\n\n\n\n<li>week<\/li>\n\n\n\n<li>day<\/li>\n\n\n\n<li>hour<\/li>\n\n\n\n<li>minute<\/li>\n\n\n\n<li>second<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">For versions prior to SQL Server 2022, you can achieve similar results using a combination of DATEADD() and DATEDIFF():<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT DATEADD(month, DATEDIFF(month, 0, '2023-03-15 14:30:00'), 0)<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">DATEADD<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">month<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #56B6C2\">DATEDIFF<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">month<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;2023-03-15 14:30:00&#39;<\/span><span style=\"color: #ABB2BF\">), <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">PostgreSQL: Using DATE_TRUNC()<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">PostgreSQL offers the versatile DATE_TRUNC() function:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT DATE_TRUNC('month', TIMESTAMP '2023-03-15 14:30:00')<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;month&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #C678DD\">TIMESTAMP<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2023-03-15 14:30:00&#39;<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This returns &#8216;2023-03-01 00:00:00&#8217;, similar to SQL Server&#8217;s DATETRUNC().<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">PostgreSQL&#8217;s DATE_TRUNC() supports an extensive list of interval options, including:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>millennium<\/li>\n\n\n\n<li>century<\/li>\n\n\n\n<li>decade<\/li>\n\n\n\n<li>year<\/li>\n\n\n\n<li>quarter<\/li>\n\n\n\n<li>month<\/li>\n\n\n\n<li>week<\/li>\n\n\n\n<li>day<\/li>\n\n\n\n<li>hour<\/li>\n\n\n\n<li>minute<\/li>\n\n\n\n<li>second<\/li>\n\n\n\n<li>millisecond<\/li>\n\n\n\n<li>microsecond<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">MySQL: Emulating Date Truncation<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">While MySQL doesn&#8217;t have a built-in DATE_TRUNC() function, you can achieve similar results using DATE() and the last_day() functions:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Truncate to month\nSELECT DATE(DATE_FORMAT('2023-03-15 14:30:00', '%Y-%m-01'))\n\n-- Truncate to quarter\nSELECT DATE(CONCAT(YEAR('2023-03-15 14:30:00'), '-', QUARTER('2023-03-15 14:30:00') * 3 - 2, '-01'))<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Truncate to month<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">DATE_FORMAT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;2023-03-15 14:30:00&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;%Y-%m-01&#39;<\/span><span style=\"color: #ABB2BF\">))<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Truncate to quarter<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #56B6C2\">CONCAT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #56B6C2\">YEAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;2023-03-15 14:30:00&#39;<\/span><span style=\"color: #ABB2BF\">), <\/span><span style=\"color: #98C379\">&#39;-&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #C678DD\">QUARTER<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;2023-03-15 14:30:00&#39;<\/span><span style=\"color: #ABB2BF\">) * <\/span><span style=\"color: #D19A66\">3<\/span><span style=\"color: #ABB2BF\"> - <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;-01&#39;<\/span><span style=\"color: #ABB2BF\">))<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">Oracle: DATE_TRUNC() and TRUNC()<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Oracle provides both DATE_TRUNC() and TRUNC() functions for date truncation:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Using DATE_TRUNC()\nSELECT DATE_TRUNC('MONTH', TIMESTAMP '2023-03-15 14:30:00') FROM DUAL;\n\n-- Using TRUNC()\nSELECT TRUNC(TO_DATE('2023-03-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'MM') FROM DUAL;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Using DATE_TRUNC()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;MONTH&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #C678DD\">TIMESTAMP<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2023-03-15 14:30:00&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> DUAL;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Using TRUNC()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> TRUNC(TO_DATE(<\/span><span style=\"color: #98C379\">&#39;2023-03-15 14:30:00&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;YYYY-MM-DD HH24:MI:SS&#39;<\/span><span style=\"color: #ABB2BF\">), <\/span><span style=\"color: #98C379\">&#39;MM&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> DUAL;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Both queries return &#8216;2023-03-01 00:00:00&#8217;, truncating to the start of the month.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Practical Applications of Date Truncation<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Time-Based Aggregations<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Date truncation shines when you need to aggregate data over specific time periods:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT DATE_TRUNC('month', order_date) AS month,\n       SUM(order_total) AS monthly_sales\nFROM orders\nGROUP BY DATE_TRUNC('month', order_date)\nORDER BY month<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;month&#39;<\/span><span style=\"color: #ABB2BF\">, order_date) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">month<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(order_total) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> monthly_sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> orders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;month&#39;<\/span><span style=\"color: #ABB2BF\">, order_date)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">month<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query aggregates sales data by month, providing a clear view of monthly performance.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Fiscal Year Analysis<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Many businesses operate on fiscal years that don&#8217;t align with calendar years. Date truncation can help:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT DATE_TRUNC('quarter', date_column) AS fiscal_quarter,\n       SUM(revenue) AS quarterly_revenue\nFROM financial_data\nWHERE date_column >= '2023-07-01' AND date_column &lt; '2024-07-01'\nGROUP BY DATE_TRUNC('quarter', date_column)\nORDER BY fiscal_quarter<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;quarter&#39;<\/span><span style=\"color: #ABB2BF\">, date_column) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> fiscal_quarter,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(revenue) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> quarterly_revenue<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> financial_data<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> date_column <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2023-07-01&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> date_column <\/span><span style=\"color: #56B6C2\">&lt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2024-07-01&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;quarter&#39;<\/span><span style=\"color: #ABB2BF\">, date_column)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> fiscal_quarter<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query groups financial data by fiscal quarters, assuming a fiscal year starting in July.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Cohort Analysis<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Date truncation is invaluable for cohort analysis, where you group users based on when they first interacted with your product:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT DATE_TRUNC('month', first_purchase_date) AS cohort_month,\n       COUNT(DISTINCT user_id) AS cohort_size,\n       AVG(lifetime_value) AS avg_ltv\nFROM user_data\nGROUP BY DATE_TRUNC('month', first_purchase_date)\nORDER BY cohort_month<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;month&#39;<\/span><span style=\"color: #ABB2BF\">, first_purchase_date) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> cohort_month,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">DISTINCT<\/span><span style=\"color: #ABB2BF\"> user_id) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> cohort_size,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #56B6C2\">AVG<\/span><span style=\"color: #ABB2BF\">(lifetime_value) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> avg_ltv<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> user_data<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;month&#39;<\/span><span style=\"color: #ABB2BF\">, first_purchase_date)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> cohort_month<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query groups users into monthly cohorts based on their first purchase date and calculates average lifetime value for each cohort.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-text-align-center wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-cyan-blue-color\"><strong style=\"background-color: rgb(69, 136, 216); color: rgb(241, 235, 218); font-size: calc(12px + 0.390625vw); font-weight: bold;\">SQL Date Truncation with a Rapid Database Builder<\/strong><\/mark><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When working with SQL, executing efficient queries is straightforward, but date truncation can sometimes require extra attention, especially when dealing with date-time data across large datasets or needing to group data by specific date parts.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>This is where rapid database builders like Five come into play. Five provides a MySQL database for your <a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/how-to-build-a-web-app\/\">application<\/a> and generates an automatic UI, making it easier to interact with and manipulate your date-time data.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">With Five, you can handle tasks like SQL date truncation intuitively through its generated forms and tables. This allows you to focus on writing accurate queries without worrying about the complexities of date formatting or truncating dates manually. For example, you can easily truncate dates to the nearest day, month, or year to aggregate or filter your data efficiently.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Five also enables you to create interactive forms, tables, dynamic charts, comprehensive PDF reports, and dashboards that are automatically generated based on your database schema. This makes it easier to visualize and analyze data by specific date ranges, helping you gain insights quickly.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In addition, Five lets you write custom JavaScript and TypeScript functions, giving you the flexibility to implement complex business logic.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Once your application is ready, Five makes deployment easy with just a few clicks, allowing you to deploy your MySQL-based application to a secure, scalable cloud infrastructure.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This lets you focus on developing, while Five handles the complexities of cloud deployment and infrastructure management.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you&#8217;re serious about using SQL efficiently, give Five a try.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Sign up for free access to Five\u2019s online development environment and start building your MySQL web application today.<\/a><\/p>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\" \/>\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(18px + 0.390625vw);\"><strong>Build Your Database In 3 Steps<\/strong><br \/><span style=\"font-size: 14pt;\">Start Developing Today<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\" data-wplink-edit=\"true\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Instant Access<\/strong><\/button><br \/><\/a><\/p>\n<hr style=\"height: 5px;\" \/><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Performance Considerations<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">While date truncation is a great tool, it&#8217;s important to consider its performance implications, especially on large datasets:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Indexing<\/strong>: If you frequently truncate a date column in WHERE clauses or JOIN conditions, consider creating an index on the truncated value.<\/li>\n\n\n\n<li><strong>Materialized Views<\/strong>: For complex queries that frequently truncate dates, consider creating materialized views with pre-truncated dates.<\/li>\n\n\n\n<li><strong>Partitioning<\/strong>: For very large tables, you might benefit from partitioning based on truncated date values.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Common Issues and How to Avoid Them<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Timezone Issues<\/strong>: Be aware of timezone differences when truncating timestamps. Always ensure you&#8217;re working in the correct timezone.<\/li>\n\n\n\n<li><strong>Leap Years<\/strong>: When truncating to years or months, be mindful of leap years which can affect your calculations.<\/li>\n\n\n\n<li><strong>Week Definitions<\/strong>: Different database systems may have different definitions of week starts (Sunday vs. Monday). Always check your system&#8217;s specific behavior.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Advanced Techniques<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Rolling Time Windows<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Combine date truncation with window functions for rolling time analysis:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT DATE_TRUNC('day', event_date) AS day,\n       COUNT(*) AS daily_events,\n       AVG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('day', event_date) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day_avg\nFROM events\nGROUP BY DATE_TRUNC('day', event_date)\nORDER BY day<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;day&#39;<\/span><span style=\"color: #ABB2BF\">, event_date) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">day<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> daily_events,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #56B6C2\">AVG<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*)) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;day&#39;<\/span><span style=\"color: #ABB2BF\">, event_date) <\/span><span style=\"color: #C678DD\">ROWS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BETWEEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">6<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PRECEDING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> CURRENT <\/span><span style=\"color: #C678DD\">ROW<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> rolling_7day_avg<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> events<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;day&#39;<\/span><span style=\"color: #ABB2BF\">, event_date)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">day<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query calculates a 7-day rolling average of daily events.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Custom Time Periods<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Sometimes you need to truncate to custom time periods. Here&#8217;s an example of truncating to 10-minute intervals:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT DATE_TRUNC('hour', event_timestamp) +\n       INTERVAL '10 minutes' * (EXTRACT(MINUTE FROM event_timestamp)::integer \/ 10) AS ten_minute_interval,\n       COUNT(*) AS event_count\nFROM events\nGROUP BY 1\nORDER BY 1<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;hour&#39;<\/span><span style=\"color: #ABB2BF\">, event_timestamp) +<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       INTERVAL <\/span><span style=\"color: #98C379\">&#39;10 minutes&#39;<\/span><span style=\"color: #ABB2BF\"> * (EXTRACT(<\/span><span style=\"color: #C678DD\">MINUTE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> event_timestamp)::<\/span><span style=\"color: #C678DD\">integer<\/span><span style=\"color: #ABB2BF\"> \/ <\/span><span style=\"color: #D19A66\">10<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> ten_minute_interval,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> event_count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> events<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query groups events into 10-minute intervals within each hour.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Using date truncation in SQL opens up a world of possibilities for time-based analysis. Whether you&#8217;re working with SQL Server, PostgreSQL, MySQL, or Oracle, understanding how to effectively truncate dates allows you to create more meaningful aggregations, perform sophisticated cohort analysis, and gain deeper insights from your time-series data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">As you apply these techniques in your own projects, remember to consider the specific requirements of your database system, the performance implications of your queries, and the potential pitfalls of working with dates and times.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Sign up for free access to Five\u2019s online development environment and start building your MySQL web application today.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Breakdown of SQL Date Truncation Date manipulation is a crucial skill for any database developer or data analyst. Among the various date operations, truncation stands out as a particularly useful technique for time-based analysis and reporting. This comprehensive guide delves into the intricacies of date truncation in SQL, exploring its applications across different database systems [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":4043,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-4042","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Date-Truncation-How-to-Guide.png",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Date-Truncation-How-to-Guide-150x150.png",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Date-Truncation-How-to-Guide-300x169.png",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Date-Truncation-How-to-Guide-768x432.png",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Date-Truncation-How-to-Guide-1024x576.png",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Date-Truncation-How-to-Guide.png",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Date-Truncation-How-to-Guide.png",1280,720,false]},"uagb_author_info":{"display_name":"Ryan Forrester","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed9_au\/"},"uagb_comment_info":0,"uagb_excerpt":"Breakdown of SQL Date Truncation Date manipulation is a crucial skill for any database developer or data analyst. Among the various date operations, truncation stands out as a particularly useful technique for time-based analysis and reporting. This comprehensive guide delves into the intricacies of date truncation in SQL, exploring its applications across different database systems&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4042","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=4042"}],"version-history":[{"count":1,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4042\/revisions"}],"predecessor-version":[{"id":17824,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4042\/revisions\/17824"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4043"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4042"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4042"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4042"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}