Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 321
user1882752 Avatar asked Apr 18 '13 14:04

user1882752


People also ask

How do I query a month from a timestamp in SQL?

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.

How do I get current month data in SQL?

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.

How can get date in dd mm yyyy format in MySQL?

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.

What is Date_add in MySQL?

The DATE_ADD() function adds a time/date interval to a date and then returns the date.


1 Answers

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.

like image 55
fancyPants Avatar answered Oct 12 '22 04:10

fancyPants