[Tickets #8381] Horde Alarms and Kronolith Alerts Use Different Timezones for SQL Queries

[bugs at horde.org]

DO NOT REPLY TO THIS MESSAGE. THIS EMAIL ADDRESS IS NOT MONITORED.

Ticket URL: http://bugs.horde.org/ticket/8381
------------------------------------------------------------------------------
  Ticket             | 8381
  Created By         | rmcauley at ca.afilias.info
  Summary            | Horde Alarms and Kronolith Alerts Use Different
                     | Timezones for SQL Queries
  Queue              | Kronolith
  Version            | 2.3.1
  Type               | Bug
  State              | Unconfirmed
  Priority           | 2. Medium
  Milestone          |
  Patch              |
  Owners             |
------------------------------------------------------------------------------


rmcauley at ca.afilias.info (2009-06-26 14:47) wrote:

I've been continually hunting a bug where Kronolith event alarms do  
not have working emails sent.  My setup is:

- PostgreSQL 8.1
- PHP 5.1.6
- CentOS 5.2
- Apache 2.2
- Hardware clock and configured local timezone are UTC

The Horde default timezone is America/New_York, currently -4 UTC, and  
is the configured time zone on the account I am using to test this bug.

When Kronolith's SQL driver performs _listAlarms, on a test event with  
alarm I created, it returns the alarm using the local time zone of  
New_York.  (for example, 14:00)  Passing back up to the Horde Alarm  
_set function, the time stays the same: 14:00.  In the horde_alarms  
table in the database, the result is (again for emphasis) 14:00 when  
queried from a local psql command line.

What happens next is that the Horde frameworks' Alarm/sql.php, in the  
_list function, uses a $time->rfc3339DateTime() value to query for  
current alarms in the system local time zone, in my case, UTC.  The  
result is that the _list function is looking for alarms at 18:00,  
while the _set function stored the alarm as happening at 14:00.

To a human sitting at his desk, this is the same time once timezones  
are involved.  However, the field in the database is a timestamp  
without a timezone type, and the Horde logic and SQL queries do not  
take into account timezones.

As a result, no Kronolith email alerts are sent from my Horde install.

I'd be happy to contribute a patch if one of the Horde devs would  
provide guidance on what would be an acceptable solution.  Converting  
all alarms to use UTC time would, I think, be the fastest ideal  
solution.