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
Timestamp with timezone database error
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:
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)
Postgres timestamp invalid format...
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
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
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.
Maybe this was just one of those reasons why they switched.