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.
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
TIMESTAMP. There are important and very significant differences between the two; we won’t spend too long looking into these differences.
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.
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.
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
ALTER TABLE `#__my_table` ADD COLUMN `some_date` DATETIME NOT NULL;
The field is specified as
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.
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.
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
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
YYYY-MM-DD HH:MM:SS. The table below describes the acceptable formats.
||Seconds since the Unix Epoch|
Name of day and UTC offset is optional.
||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||
||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
$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.
$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 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
$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);
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
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::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.
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
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
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);
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 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
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);
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
date. The following list summarises this further:
Use the MySQL type
DATETIMEto store date and time values
Get MySQL friendly representations of
Display dates and times in the web site’s time zone using
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|
||Tuesday, 06 October 2009|
||Tuesday, 06 October 2009|
||Tuesday, 06 October 2009 14:15|
||06 October 2009|
- Unix time
- Timestamp calculator
- Time zone abbreviations
- PHP Date/Time functions
- MySQL Date and Time Types
- Docs for class JDate
- Docs for class JHTML
- RFC 2822
- RFC 3339
- ISO 8601