MySQL从时间戳获取月份不起作用 [英] mysql get month from timestamp not working

问题描述

我有一个查询，它从db中提取正确的数据，但它没有从时间戳返回月份.在时间戳列中，即使存在时间戳，我也会得到一个空值. 它作为bigInt存储在数据库中(这不是我的想法).

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

但是我得到了

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?

推荐答案

在应用MONTH()函数之前，您需要先将时间戳转换回日期.

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

MONTH(mdl_quiz_grades.timemodified)

成为

MONTH(FROM_UNIXTIME(mdl_quiz_grades.timemodified))

在此处详细了解

此外，int对于一个时间戳就足够了，而bigint则不是必需的.时间戳是一个32位数字，这就是为什么它可以保留最大日期为2038年1月19日的原因.

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.

这篇关于MySQL从时间戳获取月份不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！