Functions that expect time values usually accept datetime values and ignore the date part.
Number of days between date1 and date2: TO_DAYS(date2) - TO_DAYS(date1)Number of months between date1 and date2: IF((((YEAR(date2) - 1) * 12 + MONTH(date2)) - ((YEAR(date1) - 1) * 12 + MONTH(date1))) Spent some time trying to work out how to calculate the month start x months ago ( so that I can create historical stats on the fly)here is what I came up with..((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-6)*100)+1)this gives you the first day of the month six months before the start of the current month in datetime format Several times i have come to a followng date/time problem: In the table i am storing both date and time information in the datetime column.
Querying, I want to receive COUNTed results grouped by date, and not date and time.
I came to the easy solution: SELECT DATE_FORMAT(postdate, '%Y-%m-%d') AS dd, COUNT(id) FROM My Table GROUP BY dd; I suppose this solution to be quite slow (date formatting).
If you prefer a result evaluated with respect to the year that contains the first day of the week for the given date, use PDF (US Ltr) - 26.7Mb PDF (A4) - 26.7Mb PDF (RPM) - 25.9Mb EPUB - 6.7Mb HTML Download (TGZ) - 6.5Mb HTML Download (Zip) - 6.5Mb HTML Download (RPM) - 5.6Mb Eclipse Doc Plugin (TGZ) - 7.1Mb Eclipse Doc Plugin (Zip) - 8.8Mb Man Pages (TGZ) - 165.3Kb Man Pages (Zip) - 271.0Kb Info (Gzip) - 2.5Mb Info (Zip) - 2.5Mb My SQL Backup and Recovery My SQL Cluster NDB 7.2 My SQL Globalization My SQL Information Schema My SQL Installation Guide My SQL and Linux/Unix My SQL and OS X My SQL Partitioning My SQL Performance Schema My SQL Replication My SQL Restrictions and Limitations Security in My SQL My SQL and Solaris Building My SQL from Source Starting and Stopping My SQL My SQL Tutorial My SQL and Windows If you're looking for generic SQL queries that will allow you to get the days, months, and years between any two given dates, you might consider using these.
You just need to substitute date1 and date2 with your date expressions.
NOTE: Some of these formulas are complex because they account for all cases where date1 date2.
Additionally, these formulas can be used in very generic queries where aliases and temporary variables are not allowed.
This section describes the functions that can be used to manipulate temporal values.
See Section 11.3, “Date and Time Types”, for a description of the range of values each date and time type has and the valid formats in which values may be specified.