Forum OpenACS Q&A: Has anyone reported/fixed a bug with trunc(datetime,varchar) yet?

Collapse
1: Has anyone reported/fixed a bug with trunc(datetime,varchar) yet?
Posted by Bob Fuller on 03/13/01 01:43 AM
I noticed quite a while ago that some of the ad-calendar-widget.tcl procedures were treating April incorrectly (it comes up as March in a number of them).

I finally traced the funkiness to the code in calendar_get_info_from_db, and here's an example (from psql) of what's amiss: 

=# select trunc(to_date('2001-04-01'::date, 'YYYY-MM-DD'), 'Month');
         trunc
------------------------
 2001-03-01 00:00:00-08
(1 row)

=# select trunc(to_date('2001-04-02'::date, 'YYYY-MM-DD'), 'Month');
         trunc
------------------------
 2001-04-01 00:00:00-08
(1 row)
As you can see, one of the Open ACS (or postgres internal) conversion functions is treating April 1st incorrectly. (This may be limited to 2001 only, or it may be more far-reaching than this; I'm not sure which.)

Has anyone reported this bug, or figured out a fix? I'm afraid I don't know nearly enough about the Julian calendar to be able to devise a fix (or workaround).

Any help would be appreciated...

Collapse
2: Response to Has anyone reported/fixed a bug with trunc(datetime,varchar) yet? (response to 1)
Posted by Don Baccus on 03/14/01 02:50 PM
No, I hadn't noticed this.  The only site I personally run using the
calendar stuff is on PG 7.1, and your examples work there.  "trunc()"
is a PL/pgSQL routine we provide, but it simply calls the PG internal
"date_trunc" routine, at least in the current sources (check your
version of postgres.sql to make sure this is true if you're using the
3.2 release).
Collapse
3: Response to Has anyone reported/fixed a bug with trunc(datetime,varchar) yet? (response to 1)
Posted by Bob Fuller on 03/14/01 06:28 PM
The problem is with date_trunc, and I'm (obviously) not running 7.1. Here's the output from a different system (at work, not at home) that's likewise running 7.0.x: 
openacs=# select date_trunc('Month'::varchar, '2001-04-01'::date);
       date_trunc
------------------------
 2001-03-01 00:00:00-08
(1 row)
Would the easiest solution be, presumably, to upgrade to 7.1? If so, how stable is it, and how much work (dump/restore, for example) is required for the upgrade?
Collapse
4: Response to Has anyone reported/fixed a bug with trunc(datetime,varchar) yet? (response to 1)
Posted by Dan Wickstrom on 03/14/01 07:28 PM
If you're running pg 7.0.2, you can try 7.0.3.  Otherwise, your only real option is to upgrade to pg 7.1.

Doing a dump/restore is pretty easy, and it's required if upgrading from 7.0.x to 7.1.  As far as stability, pg 7.1 is getting close to its first release canidate, so based on past experience with postgres's release cycles, its probably pretty stable.