Date and time management

Date and time management

As of Feb 17, 2010, Chamilo added the support of timezones. Here is how it works, in order to manage timezones properly.

Why DATETIME and not TIMESTAMP ?

Any date stored in the database MUST be a DATETIME, and not a TIMESTAMP. Why ? Because TIMESTAMP suffers from the year 2037 bug (see http://en.wikipedia.org/wiki/Year_2038_problem), while DATETIME doesn't.

Why do I need to call '''api_get_utc_datetime''' before saving any datetime ?

In order to manage timezones properly, new code using datetimes should call the api_get_utc_datetime($time) function before saving it in the database. This function simply takes your datetime or timestamp and converts it into the UTC (Coordinated Universal Time) timezone. Basically, it ensures that all the dates and times that are stored in the database use the UTC timezone.

For example, a normal query like:

$sql = "INSERT INTO mytable (name, created_at) VALUES ('hop', NOW())";

now has to be written as

$now = api_get_utc_datetime(time());
$sql = "INSERT INTO mytable (name, created_at) VALUES ('hop', '$now')";

I have my datetime stored as UTC in the database. How do I display it ?

In order to display a datetime stored as UTC in the database, there are 3 functions you can use:

• api_get_local_time($time = null, $to_timezone = null, $from_timezone = null): this function takes a time as an argument (which can be either a string or a timestamp, or null if you want the current time), and converts it into a string with the format 'Y-m-d H:i:s', with the right timezone. If $to_timezone is null, it will convert it to the timezone chosen by the user if it exists, or the timezone chosen by the administrator otherwise. If from_timezone is null, it will default to UTC. So, for example, if you give it a datetime string such as '2009-03-13 09:41:13' with default parameters (therefore assuming that the timezone to be converted from is UTC), and assuming that the timezone chosen by the user is America/Lima (which is GMT-5), the function will return '2009-03-13 04:41:13'.
• api_format_date($time, $format = null, $language = null): this function will format a date or a datetime in the language of the user, according to the format $format. $time is the time to be formatted (which can be either a string or a timestamp), $format can be either a predefined variable (a list of predefined variables follows) or a string under the format given in http://www.php.net/manual/en/function.strftime.php, and $language, if null, will default to the language of the user. The format will default to DATE_TIME_FORMAT_LONG.
• api_convert_and_format_date($time = null, $format = null, $from_timezone = null): this function is just a convenience function provided in order to, first, convert a datetime in the right timezone and, second, format it in the given format. So, if you simply need to display a datetime that comes from the database and is in UTC in the database, you simply need to call api_convert_and_format_date($datetime).

List of predefined date format variables

The following list of variables can be given as the $format argument to the api_format_date or the api_convert_and_format_date functions. It is HIGHLY RECOMMENDED to use these variables instead of a custom string under the format http://www.php.net/manual/en/function.strftime.php.

• TIME_NO_SEC_FORMAT: returns a string with the time without seconds
• DATE_FORMAT_SHORT: returns a string of the type "January 12, 2010"
• DATE_FORMAT_LONG: will return a string of the type "Saturday, March 13, 2010"
• DATE_TIME_FORMAT_LONG: will return a string of the type "Saturday, March 13, 2010 3:30pm"

Note: it is possible that environments using PHP 5.3 return slightly different results than environments using PHP 5.2 or lower. This is because environments using PHP 5.2 or lower use custom code, while environments using PHP 5.3 use the new PHP 5.3 class IntlDateFormatter: http://www.php.net/manual/en/class.intldateformatter.php

Some examples

• I want to store a datetime provided by the user in the database: assuming $user_date_time is the datetime provided by the user:

$date_time = api_get_utc_datetime($user_date_time);

and then store $date_time in the database

• I want to display a datetime, stored in the database as UTC, to the user: assuming $database_date_time is the datetime that comes from the database:

$display_date_time = api_convert_and_format_date($database_date_time);

• I want to store the current time in the database:

$current_time_in_utc = api_get_utc_datetime();

IMPORTANT: Beware of time(), date() and so on

The functions date(), time(), strftime(), strtotime(), gmdate() all use the default timezone of the server to do their conversions. So you need to be very careful when using them, and avoid using them as much as possible. What you need to know:

• time() can be used safely, because time() will return you a timestamp which will ALWAYS BE IN UTC, no matter what the timezone of the server is
• all of the other functions should not be used, or used very carefully

Here are examples of mistakes you can make:

• Let's say that $date_time_utc is a datetime in utc coming from the database, and that you write the following code:

$timestamp = strtotime($date_time_utc);

The generated timestamp will be wrong, because the datetime given to strtotime is in UTC, and strtotime will assume that the date_time given as an argument is into whatever the timezone of the server is...

• Let's say that $date_time_user is a datetime given by the user, and that, again, you write the following code to convert it to a timestamp:

$timestamp = strtotime($date_time_user);

Again, the timestamp will be wrong, because $date_time_user is in whatever timezone the user has chosen, and strtotime assumes that the timezone of the datetime given as an argument is the default timezone of the server.

Ok, so... how do I convert a string to a timestamp safely ?

I provided a function in main_api.lib.php, called api_strtotime, with the following prototype:

function api_strtotime($time, $timezone = null)

By default, it will be assumed that the string given is in the timezone chosen by the user. If you have a string in UTC, simply use UTC as the $timezone argument.

h2. And what about mysql functions such as NOW(), UNIX_TIMESTAMP etc...

The answer is simple: DO NOT USE THEM ! A date should enter in mysql as a DATETIME, and should come out as a DATETIME as well. Use the functions I provided to do your conversions...

Naming conventions

To name your database fields, follow Akelos naming conventions (http://www.akelos.org/wiki/naming-conventions). For example, if you need to store the date and time at which an object was created, name the field created_at. To store the date and time at which an object was updated, use updated_at etc...

h2. Conclusion

• Before storing a datetime in the DB, use api_get_utc_datetime
• To display a datetime that comes from the DB, use api_convert_and_format_date
• To display a datetime that comes from the DB in a specific format, first use api_get_local_time and then api_format_date
• Do not use strtotime, use api_strtotime instead
• I can't think of any use case where you would need to use one of the unsafe functions (date, gmdate, strtotime, strftime), so DON'T USE THEM ! And if you feel like you need to use one of them, talk to us first (dev at lists.chamilo.org)

Notes concerning the transition from non-UTC to UTC

Modules currently in UTC:

All

Converting from datetimes in non-UTC to datetimes in UTC

See the file main/install/update-db-1.8.6.2-1.8.7.inc.php. It was decided that the conversions should be done using the default timezone of the PHP server, to avoid problems of a mysql server not using the same timezone as the PHP server... So the conversion takes place in the php script...