Local time in report

Hi,

I have below query but need it converted to localtime since we use the system in several timezones and have therefor set the timezone for each site.

Or version is 9.2 with build 9225 in a PGSQL database.

Anyone that can help?

select to_char(from_unixtime(wo.CREATEDTIME/1000),'YYYY') "Year",to_char(from_unixtime(wo.CREATEDTIME/1000),'Month') "Month",to_char(from_unixtime(wo.CREATEDTIME/1000),'dd') "Date",

sum(CASE WHEN HOUR(from_unixtime(wo.CREATEDTIME/1000))>6 AND HOUR(from_unixtime(wo.CREATEDTIME/1000)) <= 7 THEN 1 ELSE 0 END) "6:00 to 7:00",

sum(CASE WHEN HOUR(from_unixtime(wo.CREATEDTIME/1000))>7 AND HOUR(from_unixtime(wo.CREATEDTIME/1000)) <= 8 THEN 1 ELSE 0 END) "7:00 to 8:00",

sum(CASE WHEN HOUR(from_unixtime(wo.CREATEDTIME/1000))>8 AND HOUR(from_unixtime(wo.CREATEDTIME/1000)) <= 9 THEN 1 ELSE 0 END) "8:00 to 9:00",

sum(CASE WHEN HOUR(from_unixtime(wo.CREATEDTIME/1000))>9 AND HOUR(from_unixtime(wo.CREATEDTIME/1000)) <= 10 THEN 1 ELSE 0 END) "9:00 to 10:00",

sum(CASE WHEN HOUR(from_unixtime(wo.CREATEDTIME/1000))>10 AND HOUR(from_unixtime(wo.CREATEDTIME/1000)) <= 11 THEN 1 ELSE 0 END) "10:00 to 11:00",

sum(CASE WHEN HOUR(from_unixtime(wo.CREATEDTIME/1000))>11 AND HOUR(from_unixtime(wo.CREATEDTIME/1000)) <= 12 THEN 1 ELSE 0 END) "11:00 to 12:00",

sum(CASE WHEN HOUR(from_unixtime(wo.CREATEDTIME/1000))>12 AND HOUR(from_unixtime(wo.CREATEDTIME/1000)) <= 13 THEN 1 ELSE 0 END) "12:00 to 13:00",

sum(CASE WHEN HOUR(from_unixtime(wo.CREATEDTIME/1000))>13 AND HOUR(from_unixtime(wo.CREATEDTIME/1000)) <= 14 THEN 1 ELSE 0 END) "13:00 to 14:00",

sum(CASE WHEN HOUR(from_unixtime(wo.CREATEDTIME/1000))>14 AND HOUR(from_unixtime(wo.CREATEDTIME/1000)) <= 15 THEN 1 ELSE 0 END) "14:00 to 15:00",

sum(CASE WHEN HOUR(from_unixtime(wo.CREATEDTIME/1000))>15 AND HOUR(from_unixtime(wo.CREATEDTIME/1000)) <= 16 THEN 1 ELSE 0 END) "15:00 to 16:00",

sum(CASE WHEN HOUR(from_unixtime(wo.CREATEDTIME/1000))>16 AND HOUR(from_unixtime(wo.CREATEDTIME/1000)) <= 17 THEN 1 ELSE 0 END) "16:00 and 17:00",

sum(CASE WHEN HOUR(from_unixtime(wo.CREATEDTIME/1000))>17 AND HOUR(from_unixtime(wo.CREATEDTIME/1000)) <= 18 THEN 1 ELSE 0 END) "17:00 to 18:00",

sum(CASE WHEN HOUR(from_unixtime(wo.CREATEDTIME/1000))>18 AND HOUR(from_unixtime(wo.CREATEDTIME/1000)) <= 24 THEN 1 ELSE 0 END) "18:00 to 24:00" from workorder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID where from_unixtime(wo.CREATEDTIME/1000) > CURRENT_DATE-INTERVAL '2 MONTH'

group by to_char(from_unixtime(wo.CREATEDTIME/1000),'YYYY'), to_char(from_unixtime(wo.CREATEDTIME/1000),'Month'), to_char(from_unixtime(wo.CREATEDTIME/1000),'dd')

order by to_char(from_unixtime(wo.CREATEDTIME/1000),'YYYY'),to_char(from_unixtime(wo.CREATEDTIME/1000),'Month'),to_char(from_unixtime(wo.CREATEDTIME/1000),'dd')