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: Always 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); timestamptz ------------------------ 1967-04-17 19:00:00-05 (1 row) Expected Results: template1=# select cast('1967-04-18' as timestamptz); 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 template1=# (On a prior Red Hat release with the same PostgreSQL version): template1=# select cast(cast('1967-04-18' as date) as timestamp); timestamptz ------------------------ 1967-04-18 00:00:00-05 (1 row) template1=# 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.
Created attachment 58049 [details] mktime bug testcase
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 week. Regards, Manuel.
Upstream code uses a side effect of mktime that went away in this release; upstream code will need to not use said side-effect.
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?
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.