Timestamps in RT2 in mysql


#1

I was trying to do a cronjob based on the message from Lorens Kockum
rt-dev-id-48@lists.lorens.org about a similar thing for RT1.

I came up with:

#!/bin/sh
mysql rt2 <<EOF
insert
into Transactions
(Creator, OldValue, TimeTaken, Ticket, NewValue, Field, Type, Created)
select
’1’, ‘stalled’, 0, id, ‘open’, ‘Status’, ‘Status’, NOW()
from Tickets
where Due < NOW() and due > ‘1970-01-01 00:00:00’ and Status=‘stalled’;
update Tickets
set Status=‘open’, LastUpdated=NOW()
where Due < NOW() and due > ‘1970-01-01 00:00:00’ and Status=‘stalled’;
EOF

I think this will ‘almost’ work. But there is a problem with the
timestamps. The times stored in the DB by RT2 seems to be in GMT, but
the time returned by NOW() is localtime. (For me, that’s a 2 hour
diffrence right now.)

How do I get GMT in mysql?

(Maby I should just switch to a real database, like PostgreSQL? :wink:

/ Jonas - http://jonas.liljegren.org/myself/en/index.html