JHTML date function
OK. I'm looking at the code and I think that the problem is the how the JHTML function is called. The timestamp.php code to which you pointed me is:
Code:
$tz_offset = $params->get('gmt_or_local', '0') == '0';
$data = JHTML::_('date', $data, JText::_($params->get('timestamp_format', 'DATE_FORMAT_LC2')), $tz_offset);
...the GMT or local setting switching between the boolean of true or false, but the JHTML date function in libraries/joomla/html/html.php describes the timezone parameter setting the input UTC date to be the user timezone ($tz=true) or the server timezone ($tz=false). Since the input date is not actually a UTC date, but rather already timezone shifted to the local date, either of the settings will shift it further.
At the moment, both the server time zone and all of the user timezone settings are, in fact, EDT (GMT-4) which makes both variants do the same thing, but neither of them are GMT.
When I change the code to:
Code:
$data = JHTML::_('date', $data, JText::_($params->get('timestamp_format', 'DATE_FORMAT_LC2')), null);
I get the value that was returned from the database which is correct for my needs; my local timezone.
To make the gmt_or_local parameter to work as described, 2 things must happen.
1) date needs to be stored in UTC
2) change the code to set $tz_offset=null for GMT or $tz_offset=true for the user local timezone.
NEW:
The timestamp plugin sets the column to be automatically set to the SQL builtin CURRENT_TIMESTAMP, which uses the timezone that is set for the database. So, the Fabrik timezone conversion for GMT or Local, needs to understand the timezone of the database. This can be obtained by the query:
Code:
mysql> select timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00'));
+----------------------------------------------------------------+
| timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')) |
+----------------------------------------------------------------+
| -04:00:00 |
+----------------------------------------------------------------+
From my naive point-of-view, perhaps the most expeditious way of solving this problem is to change the way that the database queries timestamp columns to use the convert_tz function to provide the data in UTC format.
Code:
mysql> select last_update_datetime2,convert_tz(last_update_datetime2,@@session.time_zone,'+00:00') from tom_test;
+-----------------------+----------------------------------------------------------------+
| last_update_datetime2 | convert_tz(last_update_datetime2,@@session.time_zone,'+00:00') |
+-----------------------+----------------------------------------------------------------+
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| 2012-08-28 19:43:00 | 2012-08-28 23:43:00 |
| 2012-08-30 13:00:50 | 2012-08-30 17:00:50 |
| 2012-08-28 23:52:48 | 2012-08-29 03:52:48 |
| 2012-08-29 00:08:53 | 2012-08-29 04:08:53 |
| 2012-08-30 13:20:20 | 2012-08-30 17:20:20 |
| 2012-08-30 11:19:55 | 2012-08-30 15:19:55 |
| 2012-08-30 13:56:50 | 2012-08-30 17:56:50 |
+-----------------------+----------------------------------------------------------------+
10 rows in set (0.00 sec)
NEW 2:
This code seems to work for my circumstances. WARNING: it assumes that the Joomla! TZ is set the same as the database timezone.
Code:
public function renderListData($data, &$thisRow)
{
$params = $this->getParams();
$config = JFactory::getConfig();
$date = JFactory::getDate($data); // incomming date in local TZ
$tz = new DateTimeZone($config->get('offset')); // get server TZ
$localdate = new DateTime($date,$tz); // create local date w/server TZ
$localdate->setTimezone(new DateTimeZone('UTC')); // change local date to UTC
$data = $localdate->format(JText::_($params->get('timestamp_format', 'DATE_FORMAT_LC2')));
$tz_offset = $params->get('gmt_or_local', '0') == '1' ? true : null;
$data = JHTML::_('date', $data, JText::_($params->get('timestamp_format', 'DATE_FORMAT_LC2')), $tz_offset);
return parent::renderListData($data, $thisRow);
}
