How to extract database info from current month and year
I am storing records with a date value in the format: yyyy-mm-dd (2017-03-25).
I want to now SELECT all records in the month that it is at the time of viewing the page. So, if I view today, it should show me all records for March 2017. If I view in April, then it should show me all records for April 2017and not April for every year i.e. 2016, 2015, 2014 etc.
In my SELECT statement I don't know if that is where I should try to strip out the day and just extract the month and year or if that would have to be done elsewhere.
I can get the current month and year like:
But then I am not sure how to handle the SQL. I tried something like this but no joy.
I want to now SELECT all records in the month that it is at the time of viewing the page. So, if I view today, it should show me all records for March 2017. If I view in April, then it should show me all records for April 2017and not April for every year i.e. 2016, 2015, 2014 etc.
In my SELECT statement I don't know if that is where I should try to strip out the day and just extract the month and year or if that would have to be done elsewhere.
I can get the current month and year like:
$current_month = date("m");
$current_year = date("Y");But then I am not sure how to handle the SQL. I tried something like this but no joy.
SELECT `lost`, EXTRACT (YEAR FROM `summary`) AS currentyear WHERE currentyear = ?ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
This is why you have 104 points for PHP :)
Yes, years of drudgery :-)
Somewhere on this site there is an article with a title like "Beware of Between" that might be worth a look. Without your test data we can't be sure anything will work as we have assumed. Just a thought for going forward in the Q&A process: SSCCE.
Somewhere on this site there is an article with a title like "Beware of Between" that might be worth a look. Without your test data we can't be sure anything will work as we have assumed. Just a thought for going forward in the Q&A process: SSCCE.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
If performance is an issue, you can verify the way SQL is handling your query with EXPLAIN SELECT.
I think the real problem with BETWEEN goes a bit beyond indexes and performance to the issue of exact output. Here's my concern. It's subtle but I've seen this issue in real life.
Let's say you have columns of type DATE and your BETWEEN clause identifies ISO-8601-standard start and end dates. Things work perfectly.
Now let's say you ALTER TABLE and change the column definition from DATE to DATETIME. Instead of something like 2017-03-31, newly inserted rows will potentially have additional time information, something like 2017-03-31 14:56:00. If your query says BETWEEN ... AND '2017-03-31' you will not get the rows from 2017-03-31 that have the additional TIME information. You can get this to work correctly if you use the DATE() function on the DATETIME columns. Or you can get this to work correctly if you add 23:59:59 to the end of the last date in the BETWEEN clause.
This is not a problem for the first date, only the last date in the BETWEEN clause.
I think the real problem with BETWEEN goes a bit beyond indexes and performance to the issue of exact output. Here's my concern. It's subtle but I've seen this issue in real life.
Let's say you have columns of type DATE and your BETWEEN clause identifies ISO-8601-standard start and end dates. Things work perfectly.
Now let's say you ALTER TABLE and change the column definition from DATE to DATETIME. Instead of something like 2017-03-31, newly inserted rows will potentially have additional time information, something like 2017-03-31 14:56:00. If your query says BETWEEN ... AND '2017-03-31' you will not get the rows from 2017-03-31 that have the additional TIME information. You can get this to work correctly if you use the DATE() function on the DATETIME columns. Or you can get this to work correctly if you add 23:59:59 to the end of the last date in the BETWEEN clause.
This is not a problem for the first date, only the last date in the BETWEEN clause.
Looks like the "can't use indexes" was a regression bug that is now fixed.
https://bugs.mysql.com/bug.php?id=58190
https://bugs.mysql.com/bug.php?id=58190
ASKER
Open in new window