Working with dates in Joomla!

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

Date range limitations

If you intend to store date and time values outside of the range December 13th 1901 20:45:54 to January 19th 2038 03:14:07 you may find the techniques discussed in this article to be unsuitable.

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 20 and 232 - 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 MySQL TIMESTAMP data type stores what is commonly known as a Unix timestamp. For an in-depth discussion about the Unix timestamp refer to http://en.wikipedia.org/wiki/Unix_time, where you can discover more about the origins of modern day Unix timestamps and the limitations and problems associated with them.

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.

Y2K beware!

MySQL is capable of parsing a number of different date and time formats. If we use a 2 digit year value, we should be aware of how MySQL interprets these. Values 70 to 99 are treated as 1970 to 1999, and values 00 to 69 are treated as 2000 to 2069. All in all, it is best to avoid using 2 digits to express year values.

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.

If we execute JFactory::getDate(), as shown in the example above, once at the start of our script and once at the end, the time represented in each instance will be identical. This is because of the serialized caching used by JFactory::getDate().

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.

Timestamps are inherently ambiguous. This is because of the way in which timestamps were originally defined. For more information refer to http://en.wikipedia.org/wiki/Unix_time#History.

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 offset we are discussing in this section is completely separate to the offset we can specify when creating a new JDate object.

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');

Web site time zone is generally enough

We generally don’t bother to use the user’s defined time zone. Indeed there are no examples in the core Joomla! components where the user’s time zone is used in preference to the web site’s time zone.

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);

The JDate::toRFC822() method will always append +0000 to the end of the string. This is a bug; it should append a string that describes the offset, for example +0100. For more information refer to http://forum.joomla.org/viewtopic.php?f=199&t=447820.

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);

We cannot pass JDate objects when using the JHTML type date. The value of the date and time must be expressed as a string and must be in one of the formats described in the section The JDate class.

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)

Date range limitations

JDate uses Unix timestamps and relies on some of the PHP date and time functions. For these reasons you may experience problems when dealing with dates and times outside of the ranges January 1st 1970 00:00:00 to January 19th 2038 03:14:07 (unsigned 32bit integer) and December 13th 1901 20:45:54 to January 19th 2038 03:14:07 (signed 32bit integer). For more information refer to http://php.net/strtotime#function.strtotime.notes.

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
Tweet about this on TwitterShare on FacebookShare on LinkedInShare on Google+Share on RedditEmail this to someone
Posted in Joomla!