mysql get month from timestamp not working

问题:

I have a query that pulls the correct data form a db, but it does not return me the month from the timestamp. In the timestamp column I get a null value, even though the timestamp exsists. It is stores in the DB as a bigInt (this wasnt my idea).

What I need is a date returned like this:

Course |  fcpd   |   Month
216    0.5         04

but I get:

Course |  fcpd   |   Month
216    0.5        null

SELECT mdl_quiz.course, mdl_quiz.fcpd, MONTH(mdl_quiz_grades.timemodified) as Month FROM mdl_quiz INNER JOIN mdl_quiz_grades ON mdl_quiz.course = mdl_quiz_grades.quiz WHERE mdl_quiz_grades.userid = 9428 AND mdl_quiz.course = 215

Could anyone point out where I am going wrong?

回答1:

You need to convert the timestamp back to a date first, before you can apply the MONTH() function.

MONTH(mdl_quiz_grades.timemodified)

becomes

MONTH(FROM_UNIXTIME(mdl_quiz_grades.timemodified))

Read more about it here.

And as a sidenote, int is enough for a timestamp, bigint is not necessary. A timestamp is a 32bit number, that's why it can hold the maximum date of January 19, 2038.

回答2:

"SELECT count(*) as records FROM sms WHERE MONTH(tarih)=MONTH(NOW()) and ceptel='"&ceptel&"'"