Unix時間戳轉換

如何將我的 Unix 時間戳輸出到人類可讀的 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

注意：出於安全原因，查詢已更改。

MySQL 支持FROM_UNIXTIME (…) 函式:-)

select FROM_UNIXTIME(1433433155)

反過來，UNIX_TIMESTAMP

將其應用於您的範例，假設timecreated並且timemodified是那些 Unix 時間戳：

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

引用自：https://dba.stackexchange.com/questions/103256