Unix timestamp conversion

Question

How can I output my Unix timestamps to human readable GMT?

SELECT
      category
      ,fullname
      ,shortname
      ,idnumber
      ,startdate
      ,visible
      ,timecreated
      ,timemodified
  ,count( my.id ) AS mydata
FROM mdl_mytable
JOIN mycontext ct ON ( ct.my = cat.id )
LEFT JOIN myjoin ra ON ( mycontext = cat.id )
WHERE ct.mynumber =10000000
GROUP BY shortname, fullname, .category
      ,.idnumber
      ,.startdate
      ,.visible
      ,.timecreated
      ,.timemodified
ORDER BY myorder ASC

Note: The query has been changed for security reasons.

Answer 1

MySQL support a FROM_UNIXTIME(...) function :-)

select FROM_UNIXTIME(1433433155)

And the other way around, UNIX_TIMESTAMP

Applying it to your example, assuming timecreated and timemodified are those Unix timestamps:

SELECT
      category
      ,fullname
      ,shortname
      ,idnumber
      ,startdate
      ,visible
      , FROM_UNIXTIME (timecreated)
      , FROM_UNIXTIME (timemodified)
  ,count( my.id ) AS mydata
FROM mdl_mytable
JOIN mycontext ct ON ( ct.my = cat.id )
LEFT JOIN myjoin ra ON ( mycontext = cat.id )
WHERE ct.mynumber =10000000
GROUP BY shortname, fullname, .category
      ,.idnumber
      ,.startdate
      ,.visible
      , FROM_UNIXTIME (timecreated)
      , FROM_UNIXTIME (timemodified)
ORDER BY myorder ASC