I'm working on a D6 site running on a postgres database (8.3.7) and I've ran into a small issue with a date browser view we are using.
The problem comes down to the interval offset that is added by the views_date_sql_field handler (includes/handler.inc:882-884) to account for the timezone. The problem is not noticed until the site timezone is set and the node create times fall into the timezone correction range. i.e. I'm in New York timezone -0400, so a node that gets created at 3am in the morning EST, appears to be created at 11pm the day before when run through the handler. Thus, running my date browser view with arguments of 2009/05/14 only returns the first two nodes from the table below, when it should return all three.

nid  node_created    with_interval       no_interval
152	1242335411	2009-05-14 13:10:11-04	2009-05-14 17:10:11-04
151	1242291661	2009-05-14 01:01:01-04	2009-05-14 05:01:01-04
150	1242284583	2009-05-13 23:03:03-04	2009-05-14 03:03:03-04

This is the query that runs on my simple test view and shows the problem.

SELECT node.nid AS nid,
   node.uid AS node_uid,
   node.type AS node_type,
   node_revisions.format AS node_revisions_format,
   node.created AS node_created,
   node.title AS node_title
 FROM node node 
 LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
 WHERE 
   (EXTRACT(YEAR FROM((node.created::ABSTIME + INTERVAL '-14400 SECONDS'))) = '2009') AND
   (EXTRACT(MONTH FROM((node.created::ABSTIME + INTERVAL '-14400 SECONDS'))) = '05')  AND
   (EXTRACT(DAY FROM((node.created::ABSTIME + INTERVAL '-14400 SECONDS'))) = '14')

Attached is a simple view that will show the issue on postgres.

The same view works as expected on mysql.

CommentFileSizeAuthor
#9 views-468370.patch1.57 KBemackn
#8 views-468370.patch653 bytesemackn
postgres-timezone-view.txt6.14 KBemackn
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

emackn’s picture

emackn CreditAttribution: emackn commented

Is it me or does no one else have this problem?

emackn’s picture

emackn CreditAttribution: emackn commented

Thought it could be the date module but after updating to latest release 6.x-2.2, problem still exists.

emackn’s picture

emackn CreditAttribution: emackn commented

I checked the database settings, and the postgres timezone is set as 'localtime', does it need to be set at GMT / UTC ?

KarenS’s picture

KarenS CreditAttribution: KarenS commented
Status: Active » Postponed (maintainer needs more info)

Views should be setting the timezone to UTC before the query. AFAIK that works fine if the query is not cached. Perhaps you're caching the view? See what happens if you turn off all caching and clear the caches (these are set on the Views 'Tools' tab).

You should see a query right before the Views query setting the datbase to UTC.

emackn’s picture

emackn CreditAttribution: emackn commented

Are you referring to this?
db_query("SET @@session.time_zone = '+00:00'");
from line 834 of includes/handler.inc.

That zero's out the timezone for mysql / mysqli, but for everything else (postgres), it pulls from 'date_default_timezone', the CMS setting.

And since postgres makes every effort to find your local timezone,

If timezone is not specified in postgresql.conf nor as a server command-line option, the server attempts to use the value of the TZ environment variable as the default time zone. If TZ is not defined or is not any of the time zone names known to PostgreSQL, the server attempts to determine the operating system's default time zone by checking the behavior of the C library function localtime(). The default time zone is selected as the closest match among PostgreSQL's known time zones. (These rules are also used to choose the default value of log_timezone, if it is not specified.)

the interval added is essentially over correcting

I did find this while looking up the particulars for timezones in postgres, http://www.postgresql.org/docs/8.3/static/datatype-datetime.htmlincludes...

The SQL command SET TIME ZONE sets the time zone for the session. This is an alternative spelling of SET TIMEZONE TO with a more SQL-spec-compatible syntax.

So maybe another conditional is needed to handle postgres timezones.

I can roll the patch if needed, at least as a starting point, since I have the environment already set up.

KarenS’s picture

KarenS CreditAttribution: KarenS commented

Ah, that is fixed in the Date module but not in Views. We need:

      if (($type == 'mysqli' || $type == 'mysql') && version_compare(db_version(), '4.1.3', '>=')) {
        db_query("SET @@session.time_zone = '$offset'");
      }
      elseif ($type == 'pgsql') {
        db_query("SET TIME ZONE INTERVAL '$offset' HOUR TO MINUTE");
      }

Someone needs to make a patch and test that, I don't have time now.

KarenS’s picture

KarenS CreditAttribution: KarenS commented

Missed part of that, I was doing a copy/paste from Date:

$offset = '+00:00';
emackn’s picture

emackn CreditAttribution: emackn commented
FileSize
views-468370.patch653 bytes

I think this will work.

emackn’s picture

emackn CreditAttribution: emackn commented
FileSize
views-468370.patch1.57 KB

re-rolled with Karen's suggestions.

emackn’s picture

emackn CreditAttribution: emackn commented
Status: Postponed (maintainer needs more info) » Needs review

Ok, tested on postgres and mysql installs, nothing blows up (heh) so I'm going to mark as needs review.

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented 
+  if (in_array($GLOBALS['db_type'], array('mysql', 'mysqli', 'pgsql'))) {

The way this is restructured, that if is redundant and I think can be removed completely.

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented
Status: Needs review » Fixed

Committed to 2.x and 3.x branches.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.