38262

MySQL: Select previous month and month before

Question:

I am trying to write a reporting page that will

<strong>(PART A)</strong> Select records from the current calendar month (so if accessed on June 10, it will show records from June 1 to June 10)

<strong>(PART B)</strong> Then another section for the previous calendar month (so May 1 to May 31).

I've played around with the example in this discussion <a href="https://stackoverflow.com/questions/1138190/mysql-query-to-calculate-the-previous-month" rel="nofollow">MySQL Query to calculate the Previous Month</a> But am a little bit confused.

Will

SELECT Count(*) FROM `table` WHERE table.timestamp BETWEEN date_format(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') AND last_day(NOW() - INTERVAL 1 MONTH)

satisfy <strong>(PART A)</strong> or <strong>(PART B)</strong>? It seems like it is <strong>(PART A)</strong> but I am pretty confused.

Can you help me build both queries? This SQL datetime stuff really messes with my head.

Answer1:

If you subtract the current day of the month from the date and add 1, you have the beginning of the current month. You can use this logic:

where time between date_sub(curdate(), interval day(curdate()) - 1 day) and date_sub(date_add(date_sub(curdate(), interval day(curdate()) - 1 day), interval 1 month), interval -1 day)

Recommend

  • Confusion with the order of execution when `next` with `unless` in ruby
  • Javascript fade in fade out div with youtube video
  • What is the difference between Google Developers Console project and a Google **Play** Developers Co
  • Best style for iterating over a small number of items in Python?
  • Route Audio song to blue-tooth programmatically?
  • c++ create an array with string::size
  • Change behaviour of Print button in ReportViewer C#
  • How to get or calculate size of Azure File/Share or Service
  • How to model a mixture of finite components from different parametric families with JAGS?
  • How do I include screenshots of the full page in my serenity report (and not only of the viewport) u
  • SQL Server re-calculate or not?
  • Receive mouse move even cursor is outside control
  • Retaining data after updating application
  • Is there a way to dynamically embed PDF Files in a JSP pulled from the file system?
  • cordova is not defined - cordova.js has already been loaded :: Ionic
  • MYSQ & MVC3 SQL connection error \\ ProviderManifestToken but I am using MySQL
  • Using Sax parsing to edit and write XML in VB6
  • Java color detection
  • How does this usort cmp function actually work?
  • How to define custom class, title, and target in Link Browser for content elements and the new rte_c
  • Ionic 2 storage is not cleaning up on uninstall - Only for signed APK
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Admob requires api-13 or later can I not deploy on old API-8 phones?
  • Moving mysql files across servers
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Django: Count of Group Elements
  • Java Scanner input dilemma. Automatically inputs without allowing user to type
  • How to check if every primary key value is being referenced as foreign key in another table
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • Updated Ionic CLI but shows previous version (Windows)
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • Comma separated Values
  • SQL merge duplicate rows and join values that are different
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?
  • Conditional In-Line CSS for IE and Others?