Timestamp with timezone database error

[relbud]

Hi,

I am running a fresh install of timetrex 2.2.16 - installed from the zip file with the following specs:

postgresql -> 8.1.11
php -> 5.1.6
apache -> 2.2.3
CentOS 5 -> 2.6.18-92.1.13.el5 #1 SMP

Many operations fail with the following error:
postgres7 error: [-1: ERROR: invalid input syntax for type timestamp: "2008-11-17 00:00:00 Australia/Melbourne"

Here is a specific example - when choosing Timesheet->my timesheet

lots of requests... then...

(postgres7): select a.* from pay_period as a, pay_period_schedule as b, pay_period_schedule_user as c where a.pay_period_schedule_id = b.id AND a.pay_period_schedule_id = c.pay_period_schedule_id AND c.user_id = ? AND a.start_date <= ? AND a.end_date >= ? AND a.deleted=0 AND b.deleted=0 [ (user_id=>'1') (start_date=>'2008-11-17 00:00:00 Australia/Melbourne') (end_date=>'2008-11-17 00:00:00 Australia/Melbourne') ] -1: ERROR: invalid input syntax for type timestamp: "2008-11-17 00:00:00 Australia/Melbourne"

ADOConnection._Execute(
select a.*
from pay_period as a,
pay_period_schedule as b,
pay_period_schedule_user as c

where a..., Array[3]) % line 857, file: adodb.inc.php
ADOConnection.Execute(
select a.*
from pay_period as a,
pay_period_schedule as b,
pay_period_schedule_user as c

where a..., Array[3]) % line 540, file: PayPeriodListFactory.class.php
PayPeriodListFactory.getByUserIdAndEndDate(1, 1226840400) % line 282, file: ViewUserTimeSheet.php

--------------------------------------------------------------------------

I have noticed that the error says postgres7, but my timetrex.ini.php file refers to postgres8 as the database version. With a little snooping, I found this:

NOTE: The "postgres8" driver is remapped to "postgres7".
./classes/adodb/drivers/adodb-postgres8.inc.php

So I'm guessing this is not the problem. I have done a search on the forums and not found anything...

Could anyone shed any light on this problem?

Many thanks in advance,
Ariel

[shaunw]

This is entirely a PostgreSQL issue, most likely the version you are running which is relatively old does not support the timezone you have selected.

You will need to either choose a different timezone, or upgrade your version of PostgreSQL.

You can use the following query as a test:

postgres=> select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 8.3.3 on x86_64-mandriva-linux-gnu, compiled by GCC x86_64-mandriva-linux-gnu-gcc (GCC) 4.3.1
(1 row)

postgres=> select now() at time zone 'Australia/Melbourne';
timezone
----------------------------
2008-11-19 03:29:24.353156
(1 row)

[relbud]

Thank you very much for your prompt reply.

I executed those queries and the time request worked as follows:

timetrex=# select version();
PostgreSQL 8.1.11 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)

timetrex=# select now() at time zone 'Australia/Melbourne';
2008-11-19 09:51:20.915915

That surprised me too!

The select query from timetrex still failed...

Despite this, I upgraded the database and it works fine. First "bug" I've ever come across in postgres...

Thanks,
Ariel

[shaunw]

Actually, I'm not sure this is a PostgreSQL bug, I know older versions of PostgreSQL used the operating systems timezone database and they were running into all sorts of issues with those, (especially between Unix/Windows) so in the newer versions they started to maintain their own timezone database instead.

Maybe this was just one of those reasons why they switched.