0

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.

  • what RDBMS are you using ? – Kin Shah Jun 4 '15 at 15:24
  • A Unix Timestamp is the number of seconds since '1970-01-01 00:00:00', so simply add it to that starting timestamp. How you do this depends on your DBSM. – dnoeth Jun 4 '15 at 15:33
  • MySQL and a newbie - Soz – james Jun 4 '15 at 15:33
  • I got this now but it doesnt work – james Jun 4 '15 at 15:36
  • MySQL support a FROM_UNIXTIME(...) function :-) – dnoeth Jun 4 '15 at 15:38
0

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
  • Sorry but I was a bit quick to say yes it worked. Actually I have no clue how to integrate this with my SQL :(, it ran with no errors but the two timestamp fields I applied this too are now missing in the result set – james Jun 4 '15 at 15:58
  • Error FROM_UNIXTIME and UNIX_TIMESTAMP database.UNIX_TIMESTAMP does not exist – james Jun 4 '15 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.