The clock king

I wrote about timezones telling how avoid problem using them and, in particular, using timestamp fields. As I said in the end of the article the solution has a big flow: it depends on MySQL configuration.

This is not good because best practice is to make your code as system-indepentent as possible. It's good if you can install your sotware without checking MySQL timezone. Because MySQL timezone could be configured by someone who is not you, because you could never know the real MySQL timezone and because YOU'LL NOT CHECK MySQL timezone at installation time. You'll forget it. Don't try to fool me, we both know it will go this way.

So? Is there a better solution? Obviously yes.

Problem is that timestamp field is a field that carry in it timezone information calculating it using its envionment. This property bind it to MySQL timezone and gives us headaches. So, solution is throw timestamp field away and use datetime instead.

Datetime field has no timezone information (basically, it's not converted to UNIX Timestamp when saved on the DB table) so it's just a magic string with a day and an hour. It's less powerful so you can have better control on it. The only thing you have to keep in mind is to ensure that, every time your app write a datetime on the database the timezone it uses is always the same. UTC is usually a good choose.

So, when you insert a new line and you want to trace the moment of insert you no more relay on the MySQL auto-update, but you assign:

timestamp => DateTime->now()

DateTime->now() is already with UTC timezone.

Now you can display your timestamp using any timezone you like. The value you'll receive from database will be UTC so conversion is easy.
Just:

my $ts = $dbix_row->timestamp;
$ts->set_time_zone('UTC') #Remember? Value arrive from  database with floating timezone
$ts->set_time_zone('TIMEZONE_YOU_LIKE') #Starting from UTC the datetime will be converted