1
SELECT 
 prefix_grade_items.itemname AS Course,
 prefix_grade_items.grademax,
 ROUND(prefix_grade_grades_history.finalgrade, 0) 
 AS finalgrade,
 prefix_user.firstname,
 prefix_user.lastname,
 prefix_user.username,
 prefix_grade_grades_history.timemodified

 FROM
 prefix_grade_grades_history
 INNER JOIN prefix_user ON prefix_grade_grades_history.userid = prefix_user.id
 INNER JOIN prefix_grade_items ON prefix_grade_grades_history.itemid = 
 prefix_grade_items.id

 WHERE (prefix_grade_items.itemname IS NOT NULL)
 AND (prefix_grade_items.itemtype = 'mod' OR prefix_grade_items.itemtype = 'manual')
 AND (prefix_grade_items.itemmodule = 'quiz' OR prefix_grade_items.itemmodule IS NULL)
 AND (prefix_grade_grades_history.timemodified IS NOT NULL)
 AND (prefix_grade_grades_history.finalgrade > 0)
 AND (prefix_user.deleted = 0)
ORDER BY course

Currently I am trying to polish this query. The problem I am having is using a UNIX Command to convert the time queried from timemodified into Human time. It comes out in epoch time. I have been attempting to use commands such as FROM_UNIXTIME(timestamp,'%a - %D %M %y %H:%i:%s') as timestamp. For reference this is a adhoc query to a moodle server contained in MariaDB. My desired result from the query is that nothing would change as far as the results we are getting, except that the time would be in a month/day/year format instead of the current format.

Voximus14
  • 11
  • 1
  • Can you clarify what is or isn't working? You mention using the `FROM_UNIXTIME` function, but is that not working? – Eliot K Feb 28 '20 at 21:14

2 Answers2

1

I have converted the timestamp into a custom date format using the below command in my select query.

DATE_FORMAT(FROM_UNIXTIME(`timestamp`), "%b-%d-%y")
Prasanna T
  • 169
  • 6
0

As included in your question where you mention FROM_UNIXTIME(timestamp,'%a - %D %M %y %H:%i:%s'), it is indeed possible to include a second argument in order to specify the specific time/date format you wish to output converted from the UNIX timestamp.

That's the bit that looks like: '%a - %D %M %y %H:%i:%s' - this particular format string will give you an output that looks something like this: Fri - 24th January 20 14:17:09, which as you stated isn't quite what you were looking for, but we can fix that!

For example, the statement below will return the human-readable date (according to the value returned in the timestamp) in the form of month/day/year as you specified as the goal in your question, and would look similar to this: Jan/01/20 

FROM_UNIXTIME(timestamp), '%b/%d/%y')

If you instead wish to use a 4 digit year you can substitute the lowercase %y for a capital %Y.

Additionally if a numeric month is instead preferred you can use %m in place of %b.

For a more comprehensive reference on the available specifiers that can be used to build up the format string, this page has a handy table

So putting it all together in the specific context of your original SQL query, using FROM_UNIXTIME to gain the human readable date (along with a suitable format string to specify the format of the output) may look something like this perhaps:

SELECT 
 prefix_grade_items.itemname AS Course,
 prefix_grade_items.grademax, 
 ROUND(prefix_grade_grades_history.finalgrade, 0) AS finalgrade,
 prefix_user.firstname,
 prefix_user.lastname,
 prefix_user.username, 
  FROM_UNIXTIME(prefix_grade_grades_history.timemodified, '%b/%d/%Y') AS grademodified

FROM
 prefix_grade_grades_history
INNER JOIN prefix_user ON prefix_grade_grades_history.userid = prefix_user.id
INNER JOIN prefix_grade_items ON prefix_grade_grades_history.itemid = prefix_grade_items.id

WHERE (prefix_grade_items.itemname IS NOT NULL)
 AND (prefix_grade_items.itemtype = 'mod' OR prefix_grade_items.itemtype = 'manual') 
 AND (prefix_grade_items.itemmodule = 'quiz' OR prefix_grade_items.itemmodule IS NULL) 
 AND (prefix_grade_grades_history.timemodified IS NOT NULL) 
 AND (prefix_grade_grades_history.finalgrade > 0) 
 AND (prefix_user.deleted = 0)
ORDER BY course

NOTE: I ended up specifying an alias for the timemodified column, calling it instead grademodified. This was done as without an alias the column name ends up getting a little busy :)

Hope that is helpful to you! :)

MattG
  • 56
  • 3