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

Entering the time zone

It' a lot of time I don't write here, i'm working on something that will make me write a lot (I hope) but i'm keeping silence until it is finished. However, today I had to understand timezones more that how much I did before so I want to throw all the knowledge I acquired in this blog, to make it remeber it instead of me.

Problem of the day is common, but not simple. You have a database with a timestamp field, you want to print it on the screen. It seems easy, a timestamp is just a date and an hour, what could go wrong? Easy, the hour you print IS NOT the hour you expected.

We are a lot of people who lives in a lot of places. Sun can't shine on all these places at the same time. But everyone wants to wake up in the morning and go to bed in the evening so: timezones are born.

We assume that you have a MySQL database, that you will read the timestamp column using DBIx::Class and then that a Dancer app will make it visibile on the screen. Every step has its timezone issue.

Timezone Steps

MySQL

MySQL knows its timezone and every time it gives you a timestamp the timestamp is considered in that timezone.

MySQL timezone is the default_time_zone option in my.cnf. If no timezone is provided, the server will use the one of the machine where it it hosted.

DBIx::Class

DBIx::Class has a lot of tools to manage timestamps. Using InflateColumn::DateTime is nearly a standard. Problem is DBIx::Class can't ask MySQL what is its timezone so you have to configure it when you write the model. Your timestamp field will be:

  "timestamp",

  {

      data_type => "timestamp",

      datetime_undef_if_invalid => 1,

      default_value => \"current_timestamp",

      timezone => "Europe/Rome",

      is_nullable => 0,

  }

Dancer

Dancer is not really involved in this question. Every time you want to print a timestamp you'll go through this code. But this could be used when Dancer has to give you an output on the screen. Here you can decide once more what is the timezone to use. 

Few lines:

$ts->set_time_zone("Europe/Rome");

It's a trap!

What could go wrong? 

If timezone is not configured in the DBIx::Class you will have, as output, the hour in the timezone of MySQL, but with a "floating" timezone. This means that conversion will be... well... erratic.

If timezone configured in DBIx::Class is different from the timezone in the MySQL, MySQL timezone will be ignored although the time you receive is made from it. DBIx::Class timezone will be attached to timestamp instead. No automatic conversion will be triggered and you'll have to work just with wrong data.

How to deal with this

Problem I see with this situation is that MySQL is part of the environment while the rest is part of your code. You write the code, but the environment can change. I think that a good idea is always configure the timezone when you output the data ("Dancer" level) so you have a good point to change it if it's needed, without touching the structure below. Than be always sure of your MySQL configuration when you write DBIx::Classes.

The accent

My name is Simone Faré and I'm italian. I think you can understand how important is for me UTF-8 and character set management.

Using a framework should allow you to avoid all the boring parts of stuff and UTF8 in Dancer2 is actually like that, but you need to remember the three points where the magic spell lies and ensure to activate all of them.

The first point is the easiest, the most powerful and it comes for free.
It's just the  charset: "UTF-8" configuration key in the config.yml. Spend a moment reading the documentation page about it and you'll find how many things this little word do. You could try it also with girls in pubs, probably It will do something good also  with it as well.

The second point is about your template engine, if you're using template toolkit add:

template: "template_toolkit"

engines:
    template_toolkit:
        ENCODING: utf8

This is a useful configuration, but not as powerful as the other one. If you're in an environement where all the charset are managed for good your template engine and the browser will not need to do lucky (wrong) guess about encoding. I think that this configuration can help just when guessing is needed.

Obviously the third point is very important, very insidious and very well hidden in the documentation, because there's no gain without pain.

The first configuration is about the framework, its internal logic, the second is about HTML and templating, so who are we forgetting? Never heard of something named MySQL?

DBA are strange people, very different from developers. A database has encodings, collations, things about tables, magic and curses everywhere. What I know is that if you don't nail down your DB connection to what you want you will never save good data in it. How to do this?
In the database configuration:


options:
        RaiseError: 1
        PrintError: 1
        mysql_enable_utf8: 1


Just that. But remember it or you'll suffer. Suffering is good, but suffering forever is unfair.

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?