Menu
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?
321
Use the MONTH() function to retrieve a month from a date/datetime/timestamp column in MySQL. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a date/datetime/timestamp column.
We can retrieve the current month value in SQL using the MONTH() and DATEPART() functions along with the GETDATE() function. To retrieve the name of the month functions in SQL such as DATENAME() and FORMAT() are used.
The following is the output. The following is the query to format the date to YYYY-MM-DD. mysql> select str_to_date(LoginDate,'%d. %m.
The DATE_ADD() function adds a time/date interval to a date and then returns the date.
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.
55
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
