Bug 65227 - Unexpected timestamp results.
Summary: Unexpected timestamp results.
Alias: None
Product: Red Hat Linux
Classification: Retired
Component: postgresql
Version: 7.3
Hardware: i686
OS: Linux
Target Milestone: ---
Assignee: Trond Eivind Glomsrxd
QA Contact: David Lawrence
Depends On:
TreeView+ depends on / blocked
Reported: 2002-05-20 17:54 UTC by Lamar Owen
Modified: 2007-04-18 16:42 UTC (History)
2 users (show)

Clone Of:
Last Closed: 2002-05-21 00:08:09 UTC

Attachments (Terms of Use)
mktime bug testcase (442 bytes, text/plain)
2002-05-21 00:07 UTC, Manuel Sugawara
no flags Details

Description Lamar Owen 2002-05-20 17:54:41 UTC
From Bugzilla Helper: 
User-Agent: Mozilla/5.0 (compatible; Konqueror/3.0.0-10; Linux) 
Description of problem: 
The timestamp operations fail on certain dates. 
Version-Release number of selected component (if applicable):  
How reproducible: 
Steps to Reproduce: 
1.Start a postmaster 
2.su to user postgres. 
3.Execute psql template1 
4.Run the query: select cast('1967-04-18' as timestamptz); 
Actual Results:  template1=# select cast('1967-04-18' as timestamptz); 
 1967-04-17 19:00:00-05 
(1 row) 
Expected Results:  template1=# select cast('1967-04-18' as timestamptz); 
 1967-04-18 00:00:00-05 
(1 row) 
(above is the results returned by the same version of PostgreSQL (7.2.1) on an 
earlier Red Hat release). 
Additional info: 
Other queries that trigger this problem are in the regression test suite that 
is not distributed by Red Hat. 
Another query: 
(On RH 7.3): 
template1=# select cast(cast('1967-04-18' as date) as timestamp); 
ERROR:  Unable to convert date to tm 
(On a prior Red Hat release with the same PostgreSQL version): 
template1=# select cast(cast('1967-04-18' as date) as timestamp); 
 1967-04-18 00:00:00-05 
(1 row) 
The prior release of Red Hat that I used was Red Hat 6.2 on SPARC; however, 
other reports indicate that the results I got on RH6.2 SPARC are returned the 
same on Red Hat 7.2 Intel. 
This may be a glibc bug as the same version of PostgreSQL acts differently on 
different glibc versions. 
This bug could cause major problems for those using PostgreSQL to store 
timestamp data.  I consider a change in the date of a query to be a 'loss of 
data' issue -- thus warranting HIGH priority.

Comment 1 Manuel Sugawara 2002-05-21 00:07:10 UTC
Created attachment 58049 [details]
mktime bug testcase

Comment 2 Manuel Sugawara 2002-05-21 00:08:04 UTC
Seems like mktime(3) is having problems with dates before the
epoch. Attached is the a program to test this. The glibc source is now
downloading I will try to hunt down this bug but not until the next


Comment 3 Lamar Owen 2002-05-21 03:21:35 UTC
 Upstream code uses a side effect of mktime that went away in this release; 
upstream code will need to not use said side-effect.

Comment 4 Need Real Name 2002-06-10 00:20:19 UTC
Not sure why this one is closed -- it's a show-stopper for putting redhat 7.3 +
postgresql systems into production.  When it finally hits the news sites, it'll
become a bigger deal, I imagine.

Perhaps a quick fix would be a mktime wrapper?

Comment 5 Trond Eivind Glomsrxd 2002-06-10 14:59:10 UTC
It's not a problem for the dates stated valid in the standards (everything after
epoch). There is also no problem before 1902 or so, where postgresql assumes
everything as UTC. This should probably be done for other dates before epoch as
well, as the standard clearly states mktime can't be used then.

Note You need to log in before you can comment on or make changes to this bug.