In my site I use Drupal 5.1 on PostgreSQL 7.4 I give some errors on page example.com/calendar (and something like this on others calendar pages):

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: invalid input syntax for integer: "2007-02-01" in /data/www/htdocs.users/staff/cit/jc01/includes/database.pgsql.inc on line 125.
    * user warning: query: pager_query SELECT count(node.nid) FROM node node WHERE (node.status = '1') AND ((node.changed>='2007-02-01' AND node.changed<='2007-02-31')) in /data/www/htdocs.users/staff/cit/jc01/includes/database.pgsql.inc on line 144.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: invalid input syntax for integer: "2007-02-01" in /data/www/htdocs.users/staff/cit/jc01/includes/database.pgsql.inc on line 125.
    * user warning: query: pager_query SELECT node.nid, node.changed AS node_changed_changed, node.title AS node_title, node.changed AS node_changed, node.type FROM node node WHERE (node.status = '1') AND ((node.changed>='2007-02-01' AND node.changed<='2007-02-31')) ORDER BY node_changed_changed ASC LIMIT 99 OFFSET 0 in /data/www/htdocs.users/staff/cit/jc01/includes/database.pgsql.inc on line 144.

Question: realy MySQL support automatic conversion from 'yyyy-mm-dd' string into timestamp? :-O I have tried this query in my other site which is running on MySQL 4.1 and this type query run correct but count always 0 nodes...

Comments

karens’s picture

karens commented

Get the latest development snapshot and try again. There have been fixes to the SQL handling.

havran’s picture

havran commented

I still have no luck. :( I have tried calendar 5.x-1.x-dev and calendar HEAD, i tried reinstall views and i have still same problem - no timestamp in queries but 2007-02-28 like dates...

karens’s picture

karens commented

Please try again with latest development snapshot. This is not a POSTGRES issue, the sql is wrong and should be fixed in latest code. If it is still a problem in latest code, I need to see an export of your view.

havran’s picture

havran commented
Version: 5.x-1.3 » 5.x-1.x-dev

I have installed 5.x.1.x-dev now.

Here is my view:

  $view = new stdClass();
  $view->name = 'testcalendar';
  $view->description = 'test calendar module';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'Test calendar view - Calendar';
  $view->page_header = '';
  $view->page_header_format = '1';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = '';
  $view->page_empty_format = '1';
  $view->page_type = 'calendar';
  $view->url = 'testcalendar';
  $view->use_pager = FALSE;
  $view->nodes_per_page = '0';
  $view->sort = array (
  );
  $view->argument = array (
    array (
      'type' => 'calendar_year',
      'argdefault' => '2',
      'title' => 'Year',
      'options' => '',
      'wildcard' => '',
      'wildcard_substitution' => '',
    ),
    array (
      'type' => 'calendar_month',
      'argdefault' => '2',
      'title' => 'Month',
      'options' => '',
      'wildcard' => '',
      'wildcard_substitution' => '',
    ),
    array (
      'type' => 'calendar_day',
      'argdefault' => '2',
      'title' => 'Day',
      'options' => '',
      'wildcard' => '',
      'wildcard_substitution' => '',
    ),
  );
  $view->field = array (
    array (
      'tablename' => 'node',
      'field' => 'title',
      'label' => 'Title',
      'handler' => 'views_handler_field_nodelink',
      'options' => 'link',
    ),
    array (
      'tablename' => 'node',
      'field' => 'changed',
      'label' => 'UpTime',
      'handler' => 'views_handler_field_date_small',
      'defaultsort' => 'ASC',
    ),
  );
  $view->filter = array (
  );
  $view->exposed_filter = array (
  );
  $view->requires = array(node);
  $views[$view->name] = $view;

Here is page with PostgreSQL errors: http://www.fem.uniag.sk/havran/testcalendar/2007/2

Thanks.

badrange’s picture

badrange commented

I have the same problem. Here is the view I have made. (hope I've installed the calendar in the correct way!)

$view = new stdClass();
$view->name = 'kalender';
$view->description = 'Calendar view of any date field';
$view->access = array ( );
$view->view_args_php = '';
$view->page = TRUE;
$view->page_title = 'Kalender';
$view->page_header = 'Kalender';
$view->page_header_format = '1';
$view->page_footer = '';
$view->page_footer_format = '1';
$view->page_empty = 'Ingenting å vise';
$view->page_empty_format = '1';
$view->page_type = 'calendar';
$view->url = 'kalender';
$view->use_pager = TRUE;
$view->nodes_per_page = '99';
$view->menu = TRUE;
$view->menu_title = '';
$view->menu_tab = TRUE;
$view->menu_tab_default = FALSE;
$view->menu_tab_weight = '0';
$view->block = TRUE;
$view->block_title = 'Kalender';
$view->block_header = '';
$view->block_header_format = '1';
$view->block_footer = '';
$view->block_footer_format = '1';
$view->block_empty = '';
$view->block_empty_format = '1';
$view->block_type = 'calendar';
$view->nodes_per_block = '99';
$view->block_more = TRUE;
$view->block_use_page_header = FALSE;
$view->block_use_page_footer = FALSE;
$view->block_use_page_empty = FALSE;
$view->sort = array ( array ( 'tablename' => 'node', 'field' => 'changed', 'sortorder' => 'ASC', 'options' => 'normal', ), );
$view->argument = array ( array ( 'type' => 'calendar_year', 'argdefault' => '2', 'title' => 'Year', 'options' => '', 'wildcard' => '', 'wildcard_substitution' => '', ), array ( 'type' => 'calendar_month', 'argdefault' => '2', 'title' => 'Month', 'options' => '', 'wildcard' => '', 'wildcard_substitution' => '', ), array ( 'type' => 'calendar_day', 'argdefault' => '2', 'title' => 'Day', 'options' => '', 'wildcard' => '', 'wildcard_substitution' => '', ), array ( 'type' => 'calendar_week', 'argdefault' => '2', 'title' => 'Week', 'options' => '', 'wildcard' => '', 'wildcard_substitution' => '', ), );
$view->field = array ( array ( 'tablename' => 'node', 'field' => 'title', 'label' => 'Title:', 'handler' => 'views_handler_field_nodelink', 'options' => 'link', ), array ( 'tablename' => 'node', 'field' => 'changed', 'label' => 'Updated:', 'handler' => 'views_handler_field_date_small', ), );
$view->filter = array ( array ( 'tablename' => 'node', 'field' => 'status', 'operator' => '=', 'options' => '', 'value' => '1', ), );
$view->exposed_filter = array ( );
$view->requires = array(node);
$views[$view->name] = $view;

I just upgraded calendar and view to the latest version from CVS.

Hope you can help me, this calendar seems to work just the way I need it to!

Kind regards, Bernt

karens’s picture

karens commented

Please double check that you have the very latest code. The latest version of the code cannnot possibly produce SQL that looks like this. You also need the very latest code from the Date module since the SQL is configured in date.inc, so pick up the cvs version or latest nighly development snapshot for both.

havran’s picture

havran commented

Thanx for little guidance. I found some possible mistakes in date module > date.inc > date_sql() function. First there is no $db_type 'postgres' but 'pgsql'. Second for compatibility with PostgreSQL older than version 8 (7.4 is still in use) we need another approach into unix timestamp (in postgresql terminology 'epoch' or 'unixtime'). I now experimenting with code...

Some examples:

-- there is no function TIMESTAMP in postgresql but there is TO_TIMESTAMP(double precission) (for PostgreSQL 8 & >)
-- for get unix timestamp (epoch) from postgresql timestamp >
SELECT EXTRACT('epoch' FROM NOW()) AS unixtime;

-- for get postgresql timestamp from unix timestamp (epoch)
-- - with local timezone (actual time)
SELECT TIMESTAMPTZ 'epoch' + EXTRACT('epoch' FROM NOW()) * INTERVAL '1 second';

-- - without local timezone (timezone 0)
SELECT TIMESTAMP 'epoch' + EXTRACT('epoch' FROM NOW()) * INTERVAL '1 second';
havran’s picture

havran commented
Title: PostgreSQL support qnd question » PostgreSQL support and question

I move this into http://drupal.org/node/125338... its date module related...

karens’s picture

karens commented
Project: Calendar » Date

Moving

karens’s picture

karens commented
Status: Active » Closed (duplicate)

We'll focus on the postgres fixes in http://drupal.org/node/125338, so marking this a duplicate.