CODEWITHSUNDEEP
mysqlsql
mrAfzaL
mrAfzaL

Reputation: 141

select certain date till now mysql

I need my query to select unix timestamps which range from a particular past date till now?For example I need to select '2013-01-01' till current date. I was able to do it for a particular year. Any help would be appreciated.

SELECT mdl_user_info_data.data, mdl_user.firstname, mdl_user.lastname, mdl_user.id   AS  userid, SUM( mdl_quiz.fcpd ) AS cpdtotal
FROM mdl_grade_grades
INNER JOIN mdl_user ON mdl_grade_grades.userid = mdl_user.id
INNER JOIN mdl_grade_items ON mdl_grade_grades.itemid = mdl_grade_items.id
INNER JOIN mdl_quiz ON mdl_grade_items.itemname = mdl_quiz.name
INNER JOIN mdl_course ON mdl_grade_items.courseid = mdl_course.id
INNER JOIN mdl_user_info_data ON mdl_user.id = mdl_user_info_data.userid
WHERE mdl_user_info_data.fieldid =1
AND mdl_grade_items.itemname IS NOT NULL
AND YEAR( FROM_UNIXTIME( mdl_grade_grades.timemodified ) ) =2013
GROUP BY mdl_user.id

Upvotes: 2

Views: 3880

Answers (5)

mvp
mvp

Reputation: 116177

Simply replace this line:

AND YEAR(FROM_UNIXTIME(mdl_grade_grades.timemodified)) = 2013

to:

AND FROM_UNIXTIME(mdl_grade_grades.timemodified)
   BETWEEN '2012-01-01 12:00'
   AND now()

It seems that you are storing unix timestamp in your timemodified column. You should consider changing it to timestamp type - this will allow to take advantage of index on that field. Now, your query (and this query as well) is slow because it has to compute FROM_UNIXTIME() on every row in that table and only then compare year or date.

UPDATE:

If you don't want to change types, using this should make your query work much faster (provided that there is an index on timemodified column):

AND mdl_grade_grades.timemodified
   BETWEEN unix_timestamp('2012-01-01 12:00')
   AND unix_timestamp(now())

Upvotes: 0

user1502952
user1502952

Reputation: 1420

AND DATE(FROM_UNIXTIME(mdl_grade_grades.timemodified)) between '2013-01-01' and  
DATE(NOW());

Upvotes: 0

user2763765
user2763765

Reputation:

You can create a Stored Procedure and pass the date as parameter to it. Here I made it more flexible by adding end date too.

CREATE PROCEDURE `NewProc`(IN StartDate date,IN EndDate date)
BEGIN

SELECT mdl_user_info_data.data, mdl_user.firstname, mdl_user.lastname, mdl_user.id   AS  userid, SUM( mdl_quiz.fcpd ) AS cpdtotal
FROM mdl_grade_grades
INNER JOIN mdl_user ON mdl_grade_grades.userid = mdl_user.id
INNER JOIN mdl_grade_items ON mdl_grade_grades.itemid = mdl_grade_items.id
INNER JOIN mdl_quiz ON mdl_grade_items.itemname = mdl_quiz.name
INNER JOIN mdl_course ON mdl_grade_items.courseid = mdl_course.id
INNER JOIN mdl_user_info_data ON mdl_user.id = mdl_user_info_data.userid
WHERE mdl_user_info_data.fieldid =1
AND mdl_grade_items.itemname IS NOT NULL
AND mdl_grade_grades.timemodified between StartDate and EndDate 
GROUP BY mdl_user.id

END;

Upvotes: 0

spencer7593
spencer7593

Reputation: 108400

To check for a timestamp value between a given datetime in the past, and the current datetime, something like this:

AND mdl_grade_grades.timemodified >= '2013-07-25 15:30'
AND mdl_grade_grades.timemodified <  NOW()

Upvotes: 1

Gopal Joshi
Gopal Joshi

Reputation: 2358

try below query

SELECT mdl_user_info_data.data, mdl_user.firstname, mdl_user.lastname, mdl_user.id   AS  userid, SUM( mdl_quiz.fcpd ) AS cpdtotal
FROM mdl_grade_grades
INNER JOIN mdl_user ON mdl_grade_grades.userid = mdl_user.id
INNER JOIN mdl_grade_items ON mdl_grade_grades.itemid = mdl_grade_items.id
INNER JOIN mdl_quiz ON mdl_grade_items.itemname = mdl_quiz.name
INNER JOIN mdl_course ON mdl_grade_items.courseid = mdl_course.id
INNER JOIN mdl_user_info_data ON mdl_user.id = mdl_user_info_data.userid
WHERE mdl_user_info_data.fieldid =1
AND mdl_grade_items.itemname IS NOT NULL
AND mdl_grade_grades.timemodified <  date('d-m-Y')
GROUP BY mdl_user.id

Upvotes: 0

Related Questions