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

Database around the clock

Probably this article is about something that everyone in the world knows. Problem is I did mistakes about this TWO times, without learning from them, so writing down it could be useful for this head of mine to remember how to do things right.

Writing a timestamp whenever a database row is created or updated is a standard task and everyone need it.  Many database systems can do it by themselves (MySQL), dumber ones cannot (SQLite). Are we interested in this? Obviously not, DBIX::Class (the one and only) give us a good abstraction about that and it's easy to use.

When you know the trap to avoid.

Syntax to have a timestamp that set itself on create is really simple. In your model class:


__PACKAGE__->add_columns(
  "timestamp",
  { data_type => "datetime", is_nullable => 0, set_on_create => 1 },
);


It's something so used that there's a flag to manage it. You set it to true, the timestamp will be written.

IF and only IF you remember this:

__PACKAGE__->load_components("TimeStamp");

and you installed DBIx::Class::Timestamp!

Are you hearing the Wotan worshipper roaring? Why should he? It's a procedure so clean!

Well, perl is a language that worship optimization (more than Wotan! Grrrrr). Often optimization means a fine grane library control. So in many important modules you see methods different from use... to load libraries. Most of these methods have a little side-effect: they don't give errors.
In this particular case you can write the first piece of code, forget the second or write the second and forget to install the library. The code will run. What will happen about the timestamp? Absolutly nothing.

But now I wrote this. Now this thing is encarved in my blog and in my mind. I will never be tricked again.

What were we talking about?