{"id":4210,"date":"2024-10-02T11:30:35","date_gmt":"2024-10-02T03:30:35","guid":{"rendered":"https:\/\/five.co\/?p=4210"},"modified":"2026-05-07T09:05:17","modified_gmt":"2026-05-07T01:05:17","slug":"sql-timestamp-to-date-conversion","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/sql-timestamp-to-date-conversion\/","title":{"rendered":"SQL Timestamp to Date Conversion: Practical Guide"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Breakdown of SQL Timestamp to Date Conversion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Converting timestamps to dates in SQL is a common task that can significantly simplify data analysis and reporting. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Whether you&#8217;re working with log files, transaction records, or user activity data, understanding how to manipulate timestamp data efficiently is crucial. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This guide will walk you through various methods to convert timestamps to dates in SQL, complete with practical examples and real-world applications.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-t13dw wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-t13dw \"><div class=\"eb-toc-container eb-toc-t13dw  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 Timestamp to Date Conversion&quot;,&quot;text&quot;:&quot;Breakdown of SQL Timestamp to Date Conversion&quot;,&quot;link&quot;:&quot;breakdown-of-sql-timestamp-to-date-conversion&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Understanding Timestamps and Dates&quot;,&quot;text&quot;:&quot;Understanding Timestamps and Dates&quot;,&quot;link&quot;:&quot;understanding-timestamps-and-dates&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Basic Timestamp to Date Conversion&quot;,&quot;text&quot;:&quot;Basic Timestamp to Date Conversion&quot;,&quot;link&quot;:&quot;basic-timestamp-to-date-conversion&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Real-world example: Analyzing Daily Sales&quot;,&quot;text&quot;:&quot;Real-world example: Analyzing Daily Sales&quot;,&quot;link&quot;:&quot;real-world-example-analyzing-daily-sales&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Database-Specific Conversion Methods&quot;,&quot;text&quot;:&quot;Database-Specific Conversion Methods&quot;,&quot;link&quot;:&quot;database-specific-conversion-methods&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;MySQL and MariaDB&quot;,&quot;text&quot;:&quot;MySQL and MariaDB&quot;,&quot;link&quot;:&quot;mysql-and-mariadb&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;PostgreSQL&quot;,&quot;text&quot;:&quot;PostgreSQL&quot;,&quot;link&quot;:&quot;postgresql&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;SQL Server&quot;,&quot;text&quot;:&quot;SQL Server&quot;,&quot;link&quot;:&quot;sql-server&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Oracle&quot;,&quot;text&quot;:&quot;Oracle&quot;,&quot;link&quot;:&quot;oracle&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Working with Time Zones&quot;,&quot;text&quot;:&quot;Working with Time Zones&quot;,&quot;link&quot;:&quot;working-with-time-zones&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Converting to UTC&quot;,&quot;text&quot;:&quot;Converting to UTC&quot;,&quot;link&quot;:&quot;converting-to-utc&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Handling Different Time Zones&quot;,&quot;text&quot;:&quot;Handling Different Time Zones&quot;,&quot;link&quot;:&quot;handling-different-time-zones&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Advanced Date Manipulations&quot;,&quot;text&quot;:&quot;Advanced Date Manipulations&quot;,&quot;link&quot;:&quot;advanced-date-manipulations&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Grouping by Week or Month&quot;,&quot;text&quot;:&quot;Grouping by Week or Month&quot;,&quot;link&quot;:&quot;grouping-by-week-or-month&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Calculating Date Differences&quot;,&quot;text&quot;:&quot;Calculating Date Differences&quot;,&quot;link&quot;:&quot;calculating-date-differences&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Real-World Application: User Activity Analysis&quot;,&quot;text&quot;:&quot;Real-World Application: User Activity Analysis&quot;,&quot;link&quot;:&quot;real-world-application-user-activity-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 Pitfalls and Solutions&quot;,&quot;text&quot;:&quot;Common Pitfalls and Solutions&quot;,&quot;link&quot;:&quot;common-pitfalls-and-solutions&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;FAQ&quot;,&quot;text&quot;:&quot;FAQ&quot;,&quot;link&quot;:&quot;faq&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;What does converting a timestamp to a date mean in SQL?&quot;,&quot;text&quot;:&quot;What does converting a timestamp to a date mean in SQL?&quot;,&quot;link&quot;:&quot;what-does-converting-a-timestamp-to-a-date-mean-in-sql&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;What is the easiest way to convert a timestamp to a date?&quot;,&quot;text&quot;:&quot;What is the easiest way to convert a timestamp to a date?&quot;,&quot;link&quot;:&quot;what-is-the-easiest-way-to-convert-a-timestamp-to-a-date&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Do different databases use different conversion methods?&quot;,&quot;text&quot;:&quot;Do different databases use different conversion methods?&quot;,&quot;link&quot;:&quot;do-different-databases-use-different-conversion-methods&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Why is timestamp-to-date conversion useful in real projects?&quot;,&quot;text&quot;:&quot;Why is timestamp-to-date conversion useful in real projects?&quot;,&quot;link&quot;:&quot;why-is-timestamp-to-date-conversion-useful-in-real-projects&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;What common issues should I watch out for?&quot;,&quot;text&quot;:&quot;What common issues should I watch out for?&quot;,&quot;link&quot;:&quot;what-common-issues-should-i-watch-out-for&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Wrapping Up&quot;,&quot;text&quot;:&quot;Wrapping Up&quot;,&quot;link&quot;:&quot;wrapping-up&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;Breakdown of SQL Timestamp to Date Conversion&quot;,&quot;value&quot;:&quot;breakdown-of-sql-timestamp-to-date-conversion&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Understanding Timestamps and Dates&quot;,&quot;value&quot;:&quot;understanding-timestamps-and-dates&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Basic Timestamp to Date Conversion&quot;,&quot;value&quot;:&quot;basic-timestamp-to-date-conversion&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Real-world example: Analyzing Daily Sales&quot;,&quot;value&quot;:&quot;real-world-example-analyzing-daily-sales&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Database-Specific Conversion Methods&quot;,&quot;value&quot;:&quot;database-specific-conversion-methods&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;MySQL and MariaDB&quot;,&quot;value&quot;:&quot;mysql-and-mariadb&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;PostgreSQL&quot;,&quot;value&quot;:&quot;postgresql&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;SQL Server&quot;,&quot;value&quot;:&quot;sql-server&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Oracle&quot;,&quot;value&quot;:&quot;oracle&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Working with Time Zones&quot;,&quot;value&quot;:&quot;working-with-time-zones&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Converting to UTC&quot;,&quot;value&quot;:&quot;converting-to-utc&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Handling Different Time Zones&quot;,&quot;value&quot;:&quot;handling-different-time-zones&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Advanced Date Manipulations&quot;,&quot;value&quot;:&quot;advanced-date-manipulations&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Grouping by Week or Month&quot;,&quot;value&quot;:&quot;grouping-by-week-or-month&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Calculating Date Differences&quot;,&quot;value&quot;:&quot;calculating-date-differences&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Real-World Application: User Activity Analysis&quot;,&quot;value&quot;:&quot;real-world-application-user-activity-analysis&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Performance Considerations&quot;,&quot;value&quot;:&quot;performance-considerations&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Common Pitfalls and Solutions&quot;,&quot;value&quot;:&quot;common-pitfalls-and-solutions&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;FAQ&quot;,&quot;value&quot;:&quot;faq&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;What does converting a timestamp to a date mean in SQL?&quot;,&quot;value&quot;:&quot;what-does-converting-a-timestamp-to-a-date-mean-in-sql&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;What is the easiest way to convert a timestamp to a date?&quot;,&quot;value&quot;:&quot;what-is-the-easiest-way-to-convert-a-timestamp-to-a-date&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Do different databases use different conversion methods?&quot;,&quot;value&quot;:&quot;do-different-databases-use-different-conversion-methods&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Why is timestamp-to-date conversion useful in real projects?&quot;,&quot;value&quot;:&quot;why-is-timestamp-to-date-conversion-useful-in-real-projects&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;What common issues should I watch out for?&quot;,&quot;value&quot;:&quot;what-common-issues-should-i-watch-out-for&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Wrapping Up&quot;,&quot;value&quot;:&quot;wrapping-up&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=\"#understanding-timestamps-and-dates\">Understanding Timestamps and Dates<\/a><li><a href=\"#basic-timestamp-to-date-conversion\">Basic Timestamp to Date Conversion<\/a><li><a href=\"#database-specific-conversion-methods\">Database-Specific Conversion Methods<\/a><li><a href=\"#working-with-time-zones\">Working with Time Zones<\/a><li><a href=\"#advanced-date-manipulations\">Advanced Date Manipulations<\/a><li><a href=\"#real-world-application-user-activity-analysis\">Real-World Application: User Activity Analysis<\/a><li><a href=\"#performance-considerations\">Performance Considerations<\/a><li><a href=\"#common-pitfalls-and-solutions\">Common Pitfalls and Solutions<\/a><li><a href=\"#faq\">FAQ<\/a><ul class=\"eb-toc__list\"><li><a href=\"#what-does-converting-a-timestamp-to-a-date-mean-in-sql\">What does converting a timestamp to a date mean in SQL?<\/a><li><a href=\"#what-is-the-easiest-way-to-convert-a-timestamp-to-a-date\">What is the easiest way to convert a timestamp to a date?<\/a><li><a href=\"#do-different-databases-use-different-conversion-methods\">Do different databases use different conversion methods?<\/a><li><a href=\"#why-is-timestamp-to-date-conversion-useful-in-real-projects\">Why is timestamp-to-date conversion useful in real projects?<\/a><li><a href=\"#what-common-issues-should-i-watch-out-for\">What common issues should I watch out for?<\/a><\/li><\/ul><li><a href=\"#wrapping-up\">Wrapping Up<\/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 Timestamps and Dates<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Before we dive into conversion techniques, let&#8217;s clarify the difference between timestamps and dates:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Timestamp<\/strong>: Typically includes both date and time information, often down to microseconds.<\/li>\n\n\n\n<li><strong>Date<\/strong>: Represents only the date portion without any time information.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Converting a timestamp to a date essentially means extracting just the date part, discarding the time information.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Basic Timestamp to Date Conversion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s start with the most straightforward method using the <code>DATE()<\/code> function, which is widely supported across different SQL databases.<\/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(timestamp_column) AS converted_date\nFROM your_table;<\/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: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\">(timestamp_column) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> converted_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> your_table;<\/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 extracts the date part from <code>timestamp_column<\/code>, giving you a clean date without any time information.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Real-world example: Analyzing Daily Sales<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Imagine you&#8217;re working with an e-commerce database that records each sale with a timestamp. To analyze daily sales totals, you&#8217;d need to group by date:<\/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 \n    DATE(sale_timestamp) AS sale_date,\n    SUM(sale_amount) AS daily_total\nFROM \n    sales\nGROUP BY \n    DATE(sale_timestamp)\nORDER BY \n    sale_date;<\/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>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\">(sale_timestamp) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> sale_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(sale_amount) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> daily_total<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\">(sale_timestamp)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    sale_date;<\/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 gives you a clear view of daily sales performance, making it easier to spot trends or anomalies.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Database-Specific Conversion Methods<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">While the <code>DATE()<\/code> function works in many SQL databases, some systems offer alternative or additional methods for timestamp-to-date conversion.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">MySQL and MariaDB<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">In MySQL and MariaDB, you can use the <code>DATE()<\/code> function as shown above, or try these alternatives:<\/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 \n    CAST(timestamp_column AS DATE) AS converted_date,\n    DATE_FORMAT(timestamp_column, '%Y-%m-%d') AS formatted_date\nFROM \n    your_table;<\/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>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">CAST<\/span><span style=\"color: #ABB2BF\">(timestamp_column <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> converted_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">DATE_FORMAT<\/span><span style=\"color: #ABB2BF\">(timestamp_column, <\/span><span style=\"color: #98C379\">&#39;%Y-%m-%d&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> formatted_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    your_table;<\/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\">The <code>CAST()<\/code> function explicitly converts the timestamp to a date, while <code>DATE_FORMAT()<\/code> allows you to specify the output format.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">PostgreSQL<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">PostgreSQL offers similar functionality with its <code>::<\/code> casting operator:<\/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 \n    timestamp_column::DATE AS converted_date,\n    TO_CHAR(timestamp_column, 'YYYY-MM-DD') AS formatted_date\nFROM \n    your_table;<\/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>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    timestamp_column::<\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> converted_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    TO_CHAR(timestamp_column, <\/span><span style=\"color: #98C379\">&#39;YYYY-MM-DD&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> formatted_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    your_table;<\/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\">The <code>::DATE<\/code> cast extracts the date, and <code>TO_CHAR()<\/code> lets you format the output as needed.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">SQL Server<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">SQL Server provides the <code>CAST()<\/code> and <code>CONVERT()<\/code> functions for this purpose:<\/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 \n    CAST(timestamp_column AS DATE) AS converted_date,\n    CONVERT(DATE, timestamp_column) AS converted_date_alt\nFROM \n    your_table;<\/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>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">CAST<\/span><span style=\"color: #ABB2BF\">(timestamp_column <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> converted_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">CONVERT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\">, timestamp_column) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> converted_date_alt<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    your_table;<\/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 methods achieve the same result, so choose the one that fits your coding style.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Oracle<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Oracle Database uses the <code>TRUNC()<\/code> function to remove the time portion from a timestamp:<\/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 \n    TRUNC(timestamp_column) AS converted_date,\n    TO_CHAR(timestamp_column, 'YYYY-MM-DD') AS formatted_date\nFROM \n    your_table;<\/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>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    TRUNC(timestamp_column) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> converted_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    TO_CHAR(timestamp_column, <\/span><span style=\"color: #98C379\">&#39;YYYY-MM-DD&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> formatted_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    your_table;<\/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\"><code>TRUNC()<\/code> without arguments defaults to truncating to the day, effectively converting the timestamp to a date.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Working with Time Zones<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">When dealing with timestamps, especially in applications spanning multiple time zones, it&#8217;s crucial to consider time zone conversions.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Converting to UTC<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">If your timestamps are stored in local time, converting to UTC can standardize your data:<\/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>-- PostgreSQL example\nSELECT \n    timestamp_column AT TIME ZONE 'UTC' AS utc_timestamp,\n    (timestamp_column AT TIME ZONE 'UTC')::DATE AS utc_date\nFROM \n    your_table;<\/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\">-- PostgreSQL example<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    timestamp_column <\/span><span style=\"color: #C678DD\">AT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TIME<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ZONE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;UTC&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> utc_timestamp,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    (timestamp_column <\/span><span style=\"color: #C678DD\">AT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TIME<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ZONE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;UTC&#39;<\/span><span style=\"color: #ABB2BF\">)::<\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> utc_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    your_table;<\/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 first converts the timestamp to UTC, then extracts the date part.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Handling Different Time Zones<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">For applications that need to display dates in various time zones:<\/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>-- PostgreSQL example\nSELECT \n    timestamp_column AT TIME ZONE 'America\/New_York' AS ny_timestamp,\n    (timestamp_column AT TIME ZONE 'America\/New_York')::DATE AS ny_date,\n    timestamp_column AT TIME ZONE 'Europe\/London' AS london_timestamp,\n    (timestamp_column AT TIME ZONE 'Europe\/London')::DATE AS london_date\nFROM \n    your_table;<\/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\">-- PostgreSQL example<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    timestamp_column <\/span><span style=\"color: #C678DD\">AT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TIME<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ZONE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;America\/New_York&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> ny_timestamp,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    (timestamp_column <\/span><span style=\"color: #C678DD\">AT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TIME<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ZONE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;America\/New_York&#39;<\/span><span style=\"color: #ABB2BF\">)::<\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> ny_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    timestamp_column <\/span><span style=\"color: #C678DD\">AT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TIME<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ZONE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Europe\/London&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> london_timestamp,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    (timestamp_column <\/span><span style=\"color: #C678DD\">AT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TIME<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ZONE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Europe\/London&#39;<\/span><span style=\"color: #ABB2BF\">)::<\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> london_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    your_table;<\/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 approach allows you to see how the same timestamp translates to dates in different time zones, which can be crucial for global business operations.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Advanced Date Manipulations<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Once you&#8217;ve converted timestamps to dates, you might need to perform additional date manipulations for analysis or reporting purposes.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Grouping by Week or Month<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To aggregate data by week or month:<\/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(2 * 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>-- MySQL example\nSELECT \n    DATE_FORMAT(DATE(timestamp_column), '%Y-%m') AS month,\n    COUNT(*) AS monthly_count\nFROM \n    your_table\nGROUP BY \n    DATE_FORMAT(DATE(timestamp_column), '%Y-%m')\nORDER BY \n    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: #7F848E; font-style: italic\">-- MySQL example<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">DATE_FORMAT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\">(timestamp_column), <\/span><span style=\"color: #98C379\">&#39;%Y-%m&#39;<\/span><span style=\"color: #ABB2BF\">) <\/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\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> monthly_count<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    your_table<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">DATE_FORMAT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\">(timestamp_column), <\/span><span style=\"color: #98C379\">&#39;%Y-%m&#39;<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">month<\/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 query groups records by month, allowing you to see monthly trends easily.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Calculating Date Differences<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To find the number of days between two dates:<\/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>-- PostgreSQL example\nSELECT \n    timestamp_column::DATE AS event_date,\n    CURRENT_DATE - timestamp_column::DATE AS days_since_event\nFROM \n    your_table;<\/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\">-- PostgreSQL example<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    timestamp_column::<\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> event_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    CURRENT_DATE - timestamp_column::<\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> days_since_event<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    your_table;<\/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 calculation can be useful for analyzing user engagement, product lifespan, or any time-based metrics.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Real-World Application: User Activity Analysis<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s put these concepts together in a real-world scenario. Imagine you&#8217;re analyzing user logins for a web application:<\/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(2 * 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>-- PostgreSQL example\nWITH user_activity AS (\n    SELECT \n        user_id,\n        login_timestamp::DATE AS login_date,\n        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_timestamp::DATE) AS login_number\n    FROM \n        user_logins\n)\nSELECT \n    user_id,\n    MIN(login_date) AS first_login,\n    MAX(login_date) AS last_login,\n    COUNT(DISTINCT login_date) AS total_login_days,\n    CURRENT_DATE - MAX(login_date) AS days_since_last_login\nFROM \n    user_activity\nGROUP BY \n    user_id\nHAVING \n    COUNT(DISTINCT login_date) > 1\nORDER BY \n    total_login_days DESC, days_since_last_login;<\/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\">-- PostgreSQL example<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> user_activity <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        user_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        login_timestamp::<\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> login_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">ROW_NUMBER<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> user_id <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> login_timestamp::<\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> login_number<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        user_logins<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    user_id,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">MIN<\/span><span style=\"color: #ABB2BF\">(login_date) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> first_login,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">MAX<\/span><span style=\"color: #ABB2BF\">(login_date) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> last_login,<\/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\"> login_date) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> total_login_days,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    CURRENT_DATE - <\/span><span style=\"color: #56B6C2\">MAX<\/span><span style=\"color: #ABB2BF\">(login_date) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> days_since_last_login<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    user_activity<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    user_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">HAVING<\/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\">DISTINCT<\/span><span style=\"color: #ABB2BF\"> login_date) <\/span><span style=\"color: #56B6C2\">&gt;<\/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>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    total_login_days <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">, days_since_last_login;<\/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 provides a comprehensive view of user engagement:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Identifies each user&#8217;s first and last login dates<\/li>\n\n\n\n<li>Counts the total number of days they&#8217;ve logged in<\/li>\n\n\n\n<li>Calculates how long it&#8217;s been since their last login<\/li>\n\n\n\n<li>Filters for users who&#8217;ve logged in more than once<\/li>\n\n\n\n<li>Sorts results to highlight the most active users who might be at risk of churning<\/li>\n<\/ul>\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\">When working with large datasets, consider these performance tips:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Indexing<\/strong>: Create indexes on timestamp columns you frequently query.<\/li>\n\n\n\n<li><strong>Partitioning<\/strong>: For very large tables, consider partitioning by date ranges.<\/li>\n\n\n\n<li><strong>Materialized Views<\/strong>: If you&#8217;re repeatedly running complex date-based queries, materialized views can speed up access.<\/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 Pitfalls and Solutions<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Daylight Saving Time (DST) Issues<\/strong>: Be aware that DST can cause anomalies in timestamp-to-date conversions. Always use UTC or specify time zones explicitly to avoid confusion.<\/li>\n\n\n\n<li><strong>Midnight Boundary Problems<\/strong>: Timestamps at exactly midnight might be ambiguous. Some systems might round down to the previous day. Always test edge cases in your specific database system.<\/li>\n\n\n\n<li><strong>Performance with Large Datasets<\/strong>: Converting timestamps in queries can be CPU-intensive. For large-scale reporting, consider storing a separate date column and updating it with triggers or batch processes.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\">FAQ<\/h2>\n\n\n<div id=\"rank-math-faq\" class=\"rank-math-block\">\n<ol class=\"rank-math-list \">\n<li id=\"faq-question-1771985858796\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong>What does converting a timestamp to a date mean in SQL?<\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>Converting a timestamp to a date means extracting only the date portion (year-month-day) and removing the time component. This is commonly done to simplify reporting, grouping, or filtering records by day.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771985909936\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong>What is the easiest way to convert a timestamp to a date?<\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>In many SQL databases, the simplest method is using the <code>DATE()<\/code> function or casting the value to a <code>DATE<\/code> type. These approaches return the same value without the time portion, making them ideal for quick transformations.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771985985770\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong>Do different databases use different conversion methods?<\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>Yes. While some support <code>DATE()<\/code>, others use alternatives such as <code>CAST()<\/code>, <code>CONVERT()<\/code>, <code>::DATE<\/code> (PostgreSQL), or <code>TRUNC()<\/code> (Oracle). The exact syntax depends on the database engine you\u2019re using.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771985994514\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong>Why is timestamp-to-date conversion useful in real projects?<\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>It\u2019s often used for tasks like grouping daily sales, analyzing user activity, or generating reports. By converting timestamps to dates, you can aggregate data more easily and identify trends over time.<\/p>\n\n<\/div>\n<\/li>\n<li id=\"faq-question-1771986003404\" class=\"rank-math-list-item\">\n<h3 class=\"rank-math-question \"><strong><strong>What common issues should I watch out for?<\/strong><\/strong><\/h3>\n<div class=\"rank-math-answer \">\n\n<p>A key pitfall is ignoring time zones. If timestamps come from multiple regions, converting them without standardizing the timezone can produce incorrect dates. Always confirm timezone handling before conversion.<\/p>\n\n<\/div>\n<\/li>\n<\/ol>\n<\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n<\/div><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">Wrapping Up<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Converting SQL timestamps to dates is a fundamental skill for data analysis and reporting. By mastering these techniques, you&#8217;ll be able to extract meaningful insights from your time-based data more efficiently. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Remember to consider time zones, performance implications, and the specific needs of your analysis when working with timestamp conversions.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">As you apply these methods in your projects, you&#8217;ll likely discover more nuanced ways to manipulate and analyze date-based data.<\/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 Timestamp to Date Conversion Converting timestamps to dates in SQL is a common task that can significantly simplify data analysis and reporting. Whether you&#8217;re working with log files, transaction records, or user activity data, understanding how to manipulate timestamp data efficiently is crucial. This guide will walk you through various methods to [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":4211,"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-4210","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\/10\/Five.Co-SQL-Timestamp-to-Date-Conversion.jpg",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Timestamp-to-Date-Conversion-150x150.jpg",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Timestamp-to-Date-Conversion-300x169.jpg",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Timestamp-to-Date-Conversion-768x432.jpg",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Timestamp-to-Date-Conversion-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Timestamp-to-Date-Conversion.jpg",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Timestamp-to-Date-Conversion.jpg",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 Timestamp to Date Conversion Converting timestamps to dates in SQL is a common task that can significantly simplify data analysis and reporting. Whether you&#8217;re working with log files, transaction records, or user activity data, understanding how to manipulate timestamp data efficiently is crucial. This guide will walk you through various methods to&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4210","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=4210"}],"version-history":[{"count":1,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4210\/revisions"}],"predecessor-version":[{"id":17837,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4210\/revisions\/17837"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4211"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4210"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4210"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4210"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}