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.

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?