mysql query for timezone conversion

Question

hi,

is there a way to convert from unix timestamp to GMT in mysql while running the query itself?? My query is as follows:

SELECT
    r.name
  , r.network
  , r.namestring
  , i.name
, i.description
  , r.rid
  , i.id
  , d.unixtime                  // this is the unix time i am asking to print
  , d.ifInOctets
FROM range AS r
INNER JOIN intranet AS i
ON r.rid = i.rid
INNER JOIN 1279080000_1_60 AS d  
ON i.id = d.id
AND unixtime BETWEEN 1279113600 AND 1279115400   // range of unix time
WHERE r.network = "ITPN"
AND i.status = "active"
GROUP BY i.id AND d.unixtime   

I was working with it in jdbc and tried numerous ways suggested in this forum to do the conversion but it does not seem to help.

Can I convert it directly and print out while running the query itself? I want to display the date in hour:minutes. eg: 9:20.

Please help or if there is any link I can follow that would be great too. Thank you,

Answer 1

Using FROM_UNIXTIME converts a UNIX timestamp to a MySQL DATETIME:

FROM_UNIXTIME(r.unixtime)

Then you can use CONVERT_TZ to get the DATETIME in a different timezone:

CONVERT_TZ(FROM_UNIXTIME(r.unixtime), ?, 'GMT')

Problem is, you need to know the original timezone is - you have to update the ? with the correct timezone...