In this article we explain how best to handle date and time values in Joomla! extensions. We investigate the following:

• How to store values in the Joomla! MySQL database
• Ways to format dates
• How to deal with time zones

Joomla! MySQL database date and time data types

The best policy when dealing with date and times is to store all values in the same time zone. Time zones are often defined in terms of their positive or negative offset from UTC (Coordinated Universal Time), for example UTC+1. It therefore makes good sense to use UTC+0 as the base time zone in which to store dates.

In MySQL there are five data types used for storing date information. Only two of these data types store the date and the time simultaneously, these are DATETIME and TIMESTAMP. There are important and very significant differences between the two; we won’t spend too long looking into these differences.

To Summarise, TIMESTAMP, stores data as a single 32bit positive integer value in which each unit represents the number of seconds since January 1st 1970, the Unix Epoch. Ultimately this means that the TIMESTAMP data type is restricted to storing dates between 2⁰ and 2³² - or to make that more understandable, January 1st 1970 00:00:00 and January 19th 2038 03:14:07.

The DATETIME data type on the other hand has a far greater range. Dates can range from January 1st 1000 00:00:00 to December 31st 9999 23:59:59. Actual range will depend on your setup, the range stated here is the expected minimum range, for more information refer to http://dev.mysql.com/doc/refman/5.4/en/datetime.html.

The range alone makes DATETIME a more useful date and time data type. There are additional reasons why DATETIME is preferable. For example, the TIMESTAMP data type automatically converts dates and times from the server time zone to UTC+0 when storing values and vice versa when retrieving values. Working with the TIMESTAMP data type can be very frustrating because of the amount of effort required to accurately deal with time zones, leap seconds and leap years. For these reasons, amongst others, it is general practice to use the DATETIME data type in preference to the TIMESTAMP data type.

The following SQL code snippet shows how we can add a DATETIME field named some_date to the table #__my_table.

ALTER TABLE `#__my_table`
ADD COLUMN `some_date` DATETIME NOT NULL;

The field is specified as NOT NULL. DATETIME fields can store NULL values and zero values. A zero DATETIME value is represented as 0000-00-00 00:00:00. If we insert an invalid value into a DATETIME field the value will be converted to a zero value.

The JDate class

To help us work with dates Joomla! has provided, as part of the framework, the JDate class. We can instantiate instances of this class using JFactory. To get a JDate object that represents the current date and time we do the following:

$now = JFactory::getDate();

The first thing we should note is that we do NOT use the =& assignment operator. The static JFactory::getDate() method does not return references to globally accessible instances of JDate. This means each time we use JFactory::getDate() we are retrieving a new object.

It is also possible to specify the date and time we want the JDate object to represent. A likely source for this would be a DATETIME field extracted from the Joomla! database.

$created = JFactory::getDate($row->created);

The method used by JDate to parse date and time values is relatively robust. We can use formats other than the MySQL DATETIME representation YYYY-MM-DD HH:MM:SS. The table below describes the acceptable formats.

Format	Example	Notes
Timestamp	1254497100	Seconds since the Unix Epoch
RFC 2822	Fri, 2 Oct 2009 15:25:00 +0000	Name of day and UTC offset is optional. JDate does not support all of the obsolete RFC 822 time zone identifiers. JDate support numeric time zone identifiers, UT, GMT, and military time zones.
RFC 3339	2009-10-02 T15:25:00+00:00	RFC 3339 time zone offset can be expressed numerically or as the time zone alpha identifier Z (Zulu, UTC+0). RFC 3339 is also known as ISO 8601.
US English date format	2 October 2009	For more information about US English date formats refer to http://php.net/strtotime.

In the table above both the RFC 2822 and RFC 3339 examples include a UTC offset in the value. In the examples the offset is 0. This brings us neatly on to the next subject. JDate always internally represents the date and time in the UTC+0 time zone. Had the offsets in the examples been non zero values, and had we used these to create new JDate objects, we would have found that the date and time within the JDate objects would have been adjusted to represent an offset of 0.

Timestamps on the other hand cannot include UTC offset data because they are simple integers. We can still allow for this by recording the offset separately and passing this information when we instantiate a new JDate object.

$dateAndTime = 3600; // 1 hour after the Unix Epoch
$offset = 1;
$myDate = JFactory::getDate($dateAndTime, $offset);

In this example we see the timestamp 3600; this is January 1st 1970 01:00:00. The offset indicates that we are one hour ahead of UTC. This means that the $myDate object will have adjusted the value by one hour, giving us a value of 0 or January 1st 1970 00:00:00.

The $offset parameter used in the example is only used by JDate in instances where the date and time value passed in the first parameter is in the timestamp or US English date format. It is generally best to avoid using US English date formats because of the way in which time zones are handled by PHP and JDate.

Outputting dates using JDate

The JDate class includes five handy methods for retrieving formatted date and time strings. The most versatile of these methods is JDate::toFormat(). This method allows us to explicitly define the format in which we want the date and time to be described. We can define this format in the same way as we would when using the PHP function strftime(). For more information refer to http://php.net/strftime.

The example below creates a string representation of $myDate in the format YYYY-MM-DD, for example 2009-10-06.

$string = $myDate->toFormat('%Y-%m-%d');

The remaining four methods that we can use to retrieve formatted date and time strings are used to extract specific representations of the date and time. These representations are RFC 2822 (successor to RFC 822), ISO 8601 (also known as RFC 3339), Unix timestamp, and MySQL.

// D, d M Y H:i:s
// Tuesday, 06 October 2009 12:54:37+0000
$rfc2822 = $myDate->toRFC822();

// Y-m-dTH:i:s
// 2009-10-06T12:54:37Z
$iso8601 = $myDate->toISO8601();

// Unix timestamp
// 1254833677
$unix = $myDate->toUnix();

// Y-m-d H:i:s
// 2009-10-06 12:54:37
$mysql = $myDate->toMySQL();

Probably the most useful of these methods is JDate::toMySQL(). This method creates a string that is suitable for use in a MySQL database query. Whenever we use dates and times (not expressed as a timestamp integer) in MySQL it is important to remember to encapsulate and escape the string.

$database =& JFactory::getDBO(); $mysqlSafe = $database->Quote($mysql);

Outputting dates in different time zones using JDate

We already know that JDate internally stores dates and times in the UTC+0 time zone. We have also explained that it is good practice to store dates and times in the database in the UTC+0 time zone. For end users however, this is not necessarily especially easy to read. In this section we show how we can output dates and times in different time zones using JDate.

In addition to the date and time that a JDate object represents, a JDate object can also record a time zone in which we want to output formatted dates. We can set and get this value using the JDate::setOffset() and JDate::getOffset() methods. When we set an offset we are setting the number of hours offset from UTC in which we want to display the date and time. The example below sets the time zone to UTC+1.

$myDate->setOffset(1);

To retrieve this value we use the getter method.

$offset = $myDate->getOffset();

The value of the offset we will want to use will likely be defined by the web site’s time zone or the user’s time zone. The code snippet below shows how we can determine these time zones. Note that the web site’s time zone is defined in the global Joomla! web site configuration. And a user’s time zone is defined in the user’s parameters.

// site offset
$config =& JFactory::getConfig();
$siteOffset = $config->getValue('config.offset');
// user offset
$user =& JFactory::getUser();
$user->getParam('timezone');

It is worth remembering that, dependant on the context, the JUser object may represent an anonymous/guest user. In these instances it would not be suitable to attempt to use the user’s time zone. We can check if a user is a guest using the following code:

$isGuest = $user->get('guest');

Once we have set the offset we can use any of the five methods described in the previous section to get a string representation of the date and time. There are however some important differences between JDate::toFormat() and the other four methods. The JDate::toFormat() method always applies the offset.

// custom format with offset applied
$string = $myDate->toFormat('%Y-%m-%d %H:%M:%S');

The remaining four methods do not apply the offset unless explicitly requested to do so. We can tell these methods to apply the offset using the one and only parameter that all of these methods accept, $local which is by default false. The JDate::toUnix() and JDate::toMySQL() methods do not define the time zone offset in the returned values.

// D, d M Y H:i:s
// Tuesday, 06 October 2009 12:54:37+0000
$rfc2822 = $myDate->toRFC822();
// Tuesday, 06 October 2009 13:54:37 +0100
$rfc2822 = $myDate->toRFC822(true);

// Y-m-dTH:i:s
// 2009-10-06T12:54:37Z
$iso8601 = $myDate->toISO8601();
// 2009-10-06T13:54:37+01:00
$iso8601 = $myDate->toISO8601(true);

// Unix timestamp
// 1254833677
$unix = $myDate->toUnix();
// 1254837277
$unix = $myDate->toUnix(true);

// Y-m-d H:i:s
// 2009-10-06 12:54:37
$mysql = $myDate->toMySQL();
// 2009-10-06 13:54:37
$mysql = $myDate->toMySQL(true);

In this section we have seen that we can specify an offset from UTC for display purposes when using a JDate object. This is a relatively straight forward procedure; there is however an even easier way! We can use JHTML!

Outputting dates using JHTML

The static JHTML class provides ever useful ways of quickly generating HTML. Strictly speaking, the output that is generated when using JHTML to format dates is not HTML specific. This is important, because it means we can use this technique even when we are not generating HTML output.

To generate a formatted date in the web site’s time zone we use the JHTML::_() method and specify the type as date. This method requires two parameters, the type and the date and time we want to represent as a string. In the example below we output a basic date and time.

echo JHTML::_('date', $dateAndTime);

In its most basic form, this will output the date and time in the current locale date format DATE_FORMAT_LC1, for example for the locale en-GB (English - Great Britain) this is %A, %d %B %Y. As with the JDate::toFormat() method, we can specify an alternative format. For more information refer to the PHP function strftime() format documentation http://php.net/strftime. In the example below we output the time.

echo JHTML::_('date', $dateAndTime, '%H:%M');

It is possible to specify an alternative offset to the web site’s offset. In the example below we output the $dateAndTime time element only and we show this with an offset of 1 hour (UTC+1). This is not something that is attempted regularly.

echo JHTML::_('date', $dateAndTime, '%H:%M', 1);

Summary

In this article we have discussed how to store date and time values in the Joomla! database. We have seen how we can leverage JDate in order to work easily with UTC. And we have shown how to easily output dates using the JHTML type date. The following list summarises this further:

• Use the MySQL type DATETIME to store date and time values
• Instantiate new JDate objects using JFactory::getDate()
• Get MySQL friendly representations of JDate objects using JDate::toMySQL()
• Display dates and times in the web site’s time zone using JHTML::_('date', $dateAndTime)

Last of all, I want to leave you with a handy little note about date formats. The following table describes the locale specific date formats we can use. We use these in the same way as we would a normal string that we want to translate.

$format = JText::_('DATE_FORMAT_LC4');
echo JHTML::_('date', $dateAndTime, $format);

Locale Date Format	en-GB Example	en-GB Formatted Example
DATE_FORMAT_LC	%A, %d %B %Y	Tuesday, 06 October 2009
DATE_FORMAT_LC1	%A, %d %B %Y	Tuesday, 06 October 2009
DATE_FORMAT_LC2	%A, %d %B %Y %H:%M	Tuesday, 06 October 2009 14:15
DATE_FORMAT_LC3	%d %B %Y	06 October 2009
DATE_FORMAT_LC4	%d.%m.%y	06.10.09

Additional Reading

Unix time

http://en.wikipedia.org/wiki/Unix_time

Timestamp calculator

http://timestamp-calculator.com/

Time zone abbreviations

http://www.timeanddate.com/library/abbreviations/timezones/

PHP Date/Time functions

http://php.net/manual/ref.datetime.php

MySQL Date and Time Types

http://dev.mysql.com/doc/refman/5.4/en/date-and-time-types.html

JFactory::getDate()

http://api.joomla.org/Joomla-Framework/JFactory.html#getDate

Docs for class JDate

http://api.joomla.org/Joomla-Framework/Utilities/JDate.html

Docs for class JHTML

http://api.joomla.org/Joomla-Framework/HTML/JHTML.html#date

RFC 2822

http://tools.ietf.org/html/rfc2822

RFC 3339

http://tools.ietf.org/html/rfc3339

ISO 8601

http://en.wikipedia.org/wiki/ISO_8601