
============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name		: Monica Kroger
Your email address	: krogerm@rocky.edu

Category		: runtime: back-end: SQL
Severity		: non-critical

Summary: Error with date_part when checking month

System Configuration
--------------------
  Operating System   : RedHat 5.2

  PostgreSQL version : 6.3

  Compiler used      : it came precompiled with RH5.2

Hardware:
---------
Linux localhost.localdomain 2.0.36 #7 Fri Feb 26 21:42:39 MST 1999 i586 unknown
P133, 64 Megs of RAM


Versions of other tools:
------------------------


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

Problem Description:
--------------------
When using the date_part function it seems to be off by a day.
It excludes the first of this month and includes the first of next month in the query.


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

Test Case:
----------
Given a table(ice) with a date(eventstart) in it when I use a select as follows:
select * from ice where date_part('month', "eventstart")='6';
it will return all of the dates in June except if they're on 06-01-1999
It will also return all of the dates on July 1.

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

Solution:
---------


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


Sun, 28 Feb 1999 11:38:41 -0500 (EST)
Unprivileged user <nobody>
Port Bug Report: Error with date_part when checking month
>   PostgreSQL version : 6.3
> Problem Description:
> --------------------
> When using the date_part function it seems to be off by a day.
> It excludes the first of this month and includes the first of next 
> month in the query.

postgres=> select date_part('month', '6-1-1999'::datetime);
date_part
---------
        6
(1 row)

It appears to be fixed in more recent versions (though I haven't
confirmed that I can reproduce your symptom on v6.3).

v6.4.2 is the current version, and has significant improvements in all
areas.

                   - Tom

Wed, 03 Mar 1999 03:26:38 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [PORTS] Port Bug Report: Error with date_part when checking month
> eventstart|date_part
> ----------+---------
> 06-15-1999|        6
> The data was entered in this same time zone.  It was entered as
> 'mm-dd-yyyy' from a PHP3 form using a standard SQL insert.

Ah, it is the date data type. I now recall that I fixed some time zone
conversion problems in this data type. Postgres v6.4.2, plus some
patches on the web site, will fix this problem, which was first
identified at the end of December. The upcoming v6.4.3 will have the
fixes already applied.

If you don't want to install and patch a new version of Postgres, try
using the datetime data type instead.

Sorry for the problems...

                    - Tom

Wed, 03 Mar 1999 15:28:10 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [PORTS] Port Bug Report: Error with date_part when checking month