heja@sun1.ruf.uni-freiburg.de (Herbert Jaegle) (04/12/91)
All i wanted to do, is storing date and time with no offset, no dst, just using GMT. We are using INGRES, first Version 5.xx, now Version 6.2 on a HP9000 Series 300 with first HPUX 6.2, now HPUX 7.05. The starting point was in 1989. We created a database for weather data with two entries per hour. The Unix timezone was MEZ-1MESZ and the kernel timezone was incorrect set. One day we corrected this. From this point on we had problems to get consistent data. Until now! There was no documentation about handling dates in INGRES, but they looked at the kernel, ignoring the TZ variable. We corrected this. Then summer comes up. Timeoffset now was 2 hours. All data we stored now, had an offset from 2 hours to GMT, the old 1 hour. At the moment the system changed the timeoffset, 2 entries are lost because the date is unique. There was no way to select data from before summertime. All date were corrected with 2 hour offset. This was the second time we tried to get information, how INGRES handles dates. We got a new Version 5.xx which looked at TZ. We restored the data (it takes a long time to get it corrected) and decided to use a fixed offset for TZ, when storing or retrieving data from those databases. It was the frontend which looked at TZ. This was ok until 6.2 reached. While updating from 5 to 6, we got an offset from 1 hour into data. This was the third time we asked for information about date handling. (We got none :-(). Since its dst now, we have an addional problem, INGRES does not select correctly when selcting variables of type date. (See below). Now its the 4th time we ask for information on how INGRES handles dates. At this moment our database is unusable. Nobody knows if the frontend or the backend converts times, or both and how. To which Environment INGRES looks etc. We are waiting for information for over a year now, but it seems to be confidential. Thats ok as long as i get the time out i put in, but I don't! Herbert ---------------------------cut here--------------------------- TZ=MEZ-1MESZ + export TZ + echo MEZ-1MESZ MEZ-1MESZ + sql wimeda -umarcus select * from wetter where zeipu<'10-oct-89 12:00:00'; \p\g \q INGRES TERMINAL MONITOR -- Copyright (c) 1981, 1990 Ingres Corporation INGRES HP-UX Version 6.2/04p (hp3.us5/01) login Thu Apr 4 14:27:25 1991 INGRES/UNIX Version 6.2 continue * * /* SQL Startup File */ select * from wetter where zeipu<'10-oct-89 12:00:00'; Executing . . . +-------------------------+-----------+-----------+-----------+ |zeipu |temp_umg |g_0 |g_90 | +-------------------------+-----------+-----------+-----------+ |10-oct-1989 11:19:20 | 9.214| 151.170| 89.607| |10-oct-1989 11:21:20 | 9.218| 155.669| 89.363| |10-oct-1989 11:23:20 | 9.257| 174.028| 94.141| |10-oct-1989 11:25:20 | 9.323| 204.159| 117.164| |10-oct-1989 11:27:20 | 9.365| 202.361| 114.666| |10-oct-1989 11:29:20 | 9.459| 182.703| 93.127| |10-oct-1989 11:31:20 | 9.544| 160.262| 80.994| |10-oct-1989 11:33:20 | 9.660| 135.361| 73.039| |10-oct-1989 11:35:20 | 9.724| 119.181| 66.879| |10-oct-1989 11:39:20 | 9.756| 122.656| 63.900| |10-oct-1989 11:41:20 | 9.777| 109.437| 64.102| |10-oct-1989 11:43:20 | 9.792| 76.796| 49.859| |10-oct-1989 11:45:20 | 9.760| 64.148| 36.781| |10-oct-1989 11:47:20 | 9.766| 81.761| 33.566| |10-oct-1989 11:49:20 | 9.773| 105.187| 42.461| |10-oct-1989 11:51:20 | 9.761| 134.819| 53.578| |10-oct-1989 11:53:20 | 9.726| 120.742| 60.736| |10-oct-1989 11:55:21 | 9.713| 123.052| 50.363| |10-oct-1989 11:57:20 | 9.670| 120.755| 52.549| |10-oct-1989 11:59:21 | 9.570| 133.712| 54.881| +-------------------------+-----------+-----------+-----------+ (20 rows) continue * Your SQL statement(s) have been committed. INGRES Version 6.2/04p (hp3.us5/01) logout Thu Apr 4 14:27:28 1991 TZ=MEZ-1 + export TZ + echo MEZ-1 MEZ-1 + sql wimeda -umarcus select * from wetter where zeipu<'10-oct-89 12:00:00'; \p\g \q INGRES TERMINAL MONITOR -- Copyright (c) 1981, 1990 Ingres Corporation INGRES HP-UX Version 6.2/04p (hp3.us5/01) login Thu Apr 4 13:27:30 1991 INGRES/UNIX Version 6.2 continue * * /* SQL Startup File */ select * from wetter where zeipu<'10-oct-89 12:00:00'; Executing . . . +-------------------------+-----------+-----------+-----------+ |zeipu |temp_umg |g_0 |g_90 | +-------------------------+-----------+-----------+-----------+ |10-oct-1989 10:19:20 | 9.214| 151.170| 89.607| |10-oct-1989 10:21:20 | 9.218| 155.669| 89.363| |10-oct-1989 10:23:20 | 9.257| 174.028| 94.141| |10-oct-1989 10:25:20 | 9.323| 204.159| 117.164| |10-oct-1989 10:27:20 | 9.365| 202.361| 114.666| |10-oct-1989 10:29:20 | 9.459| 182.703| 93.127| |10-oct-1989 10:31:20 | 9.544| 160.262| 80.994| |10-oct-1989 10:33:20 | 9.660| 135.361| 73.039| |10-oct-1989 10:35:20 | 9.724| 119.181| 66.879| |10-oct-1989 10:39:20 | 9.756| 122.656| 63.900| |10-oct-1989 10:41:20 | 9.777| 109.437| 64.102| |10-oct-1989 10:43:20 | 9.792| 76.796| 49.859| |10-oct-1989 10:45:20 | 9.760| 64.148| 36.781| |10-oct-1989 10:47:20 | 9.766| 81.761| 33.566| |10-oct-1989 10:49:20 | 9.773| 105.187| 42.461| |10-oct-1989 10:51:20 | 9.761| 134.819| 53.578| |10-oct-1989 10:53:20 | 9.726| 120.742| 60.736| |10-oct-1989 10:55:21 | 9.713| 123.052| 50.363| |10-oct-1989 10:57:20 | 9.670| 120.755| 52.549| |10-oct-1989 10:59:21 | 9.570| 133.712| 54.881| +-------------------------+-----------+-----------+-----------+ (20 rows) continue * Your SQL statement(s) have been committed. INGRES Version 6.2/04p (hp3.us5/01) logout Thu Apr 4 13:27:31 1991 + sql wimeda -umarcus select * from wetter where zeipu<'10-oct-89 13:00:00'; \p\g \q INGRES TERMINAL MONITOR -- Copyright (c) 1981, 1990 Ingres Corporation INGRES HP-UX Version 6.2/04p (hp3.us5/01) login Thu Apr 4 13:27:33 1991 INGRES/UNIX Version 6.2 continue * * /* SQL Startup File */ select * from wetter where zeipu<'10-oct-89 13:00:00'; Executing . . . +-------------------------+-----------+-----------+-----------+ |zeipu |temp_umg |g_0 |g_90 | +-------------------------+-----------+-----------+-----------+ |10-oct-1989 10:19:20 | 9.214| 151.170| 89.607| |10-oct-1989 10:21:20 | 9.218| 155.669| 89.363| |10-oct-1989 10:23:20 | 9.257| 174.028| 94.141| |10-oct-1989 10:25:20 | 9.323| 204.159| 117.164| |10-oct-1989 10:27:20 | 9.365| 202.361| 114.666| |10-oct-1989 10:29:20 | 9.459| 182.703| 93.127| |10-oct-1989 10:31:20 | 9.544| 160.262| 80.994| |10-oct-1989 10:33:20 | 9.660| 135.361| 73.039| |10-oct-1989 10:35:20 | 9.724| 119.181| 66.879| |10-oct-1989 10:39:20 | 9.756| 122.656| 63.900| |10-oct-1989 10:41:20 | 9.777| 109.437| 64.102| |10-oct-1989 10:43:20 | 9.792| 76.796| 49.859| |10-oct-1989 10:45:20 | 9.760| 64.148| 36.781| |10-oct-1989 10:47:20 | 9.766| 81.761| 33.566| |10-oct-1989 10:49:20 | 9.773| 105.187| 42.461| |10-oct-1989 10:51:20 | 9.761| 134.819| 53.578| |10-oct-1989 10:53:20 | 9.726| 120.742| 60.736| |10-oct-1989 10:55:21 | 9.713| 123.052| 50.363| |10-oct-1989 10:57:20 | 9.670| 120.755| 52.549| |10-oct-1989 10:59:21 | 9.570| 133.712| 54.881| |10-oct-1989 11:01:19 | 9.531| 152.615| 68.857| |10-oct-1989 11:05:20 | 9.531| 190.123| 117.996| |10-oct-1989 11:07:20 | 9.580| 202.049| 141.234| |10-oct-1989 11:09:21 | 9.649| 201.200| 144.629| |10-oct-1989 11:11:20 | 9.718| 206.320| 147.020| |10-oct-1989 11:13:20 | 9.743| 210.649| 144.994| |10-oct-1989 11:15:20 | 9.785| 215.626| 142.477| |10-oct-1989 11:17:20 | 9.818| 225.400| 152.287| |10-oct-1989 11:19:20 | 9.813| 228.615| 160.035| |10-oct-1989 11:21:20 | 9.812| 235.028| 173.072| |10-oct-1989 11:23:20 | 9.887| 234.591| 178.209| |10-oct-1989 11:25:20 | 9.909| 228.600| 183.750| |10-oct-1989 11:27:20 | 9.913| 225.624| 194.568| |10-oct-1989 11:29:20 | 10.004| 260.867| 265.184| |10-oct-1989 11:31:20 | 10.032| 350.318| 394.107| |10-oct-1989 11:33:20 | 10.134| 325.933| 377.623| |10-oct-1989 11:35:20 | 10.240| 290.776| 323.768| |10-oct-1989 11:37:20 | 10.216| 502.568| 657.023| |10-oct-1989 11:39:20 | 10.302| 440.970| 596.473| |10-oct-1989 11:41:20 | 10.440| 568.774| 774.486| |10-oct-1989 11:43:20 | 10.613| 344.077| 434.465| |10-oct-1989 11:45:20 | 10.740| 396.131| 516.982| |10-oct-1989 11:47:20 | 10.846| 542.723| 738.318| |10-oct-1989 11:49:20 | 10.950| 642.000| 866.877| |10-oct-1989 11:51:20 | 11.166| 283.161| 278.588| |10-oct-1989 11:53:20 | 11.259| 243.116| 196.145| |10-oct-1989 11:55:20 | 11.293| 232.981| 143.201| |10-oct-1989 11:57:20 | 11.183| 214.237| 122.613| |10-oct-1989 11:59:20 | 11.044| 189.688| 102.439| +-------------------------+-----------+-----------+-----------+ (49 rows) continue * Your SQL statement(s) have been committed. INGRES Version 6.2/04p (hp3.us5/01) logout Thu Apr 4 13:27:35 1991 ---------------------------------------------------------------------------- Herbert Jaegle MAIL: Fraunhofer Institute for Solar Energy Systems DL7GAP Oltmannsstr. 22 D 7800 Freiburg, West Germany PHONE: +49 (761) 4014 126 FAX: +49 (761) 4014 200 EMAIL: heja@ise.fhg.de ---------------------------------------------------------------------------- -- ---------------------------------------------------------------------------- Herbert Jaegle MAIL: Fraunhofer Institute for Solar Energy Systems DL7GAP Oltmannsstr. 22 D 7800 Freiburg, West Germany
gnb@bby.oz.au (Gregory N. Bond) (04/16/91)
It's even worse than this.
Ingres (5.xxx) on Suns does all the date conversions in the backend
(so no spanning timezones, now!)
And it uses a positively prehistoric version of the C library for
linking the backend, so doesn't use the zoneinfo stuff at all. The
DST algorithms are compiled in and they are wrong. We had problems
shifting out of DST where everything else in the place switched except
the Ingres database. It pushed the whole system over for two weeks.
Braindead bloody system. Reimplements curses, badly. Reimplements
timezones, wrong. Reimplements rsh protocols, wrong (and stores the
passwords in a reversible encryption, too, so anyone who has had
access to ingres source can read the password of any ingres user in the
world).
If it didn't cost so much, we'd change.
--
Gregory Bond, Burdett Buckeridge & Young Ltd, Melbourne, Australia
Internet: gnb@melba.bby.oz.au non-MX: gnb%melba.bby.oz@uunet.uu.net
Uucp: {uunet,pyramid,ubc-cs,ukc,mcvax,prlb2,nttlab...}!munnari!melba.bby.oz!gnbntm@bksmel.oz.au (Neil McKinnon) (04/17/91)
In article <1991Apr16.082104.11000@melba.bby.oz.au>, gnb@bby.oz.au (Gregory N. Bond) writes: > It's even worse than this. > > Ingres (5.xxx) on Suns does all the date conversions in the backend > (so no spanning timezones, now!) > Ingres (6.x) has only been available on Suns for about 2 years. I Guess it is a bit too soon to upgrade :-) Neil McKinnon (ntm@bksmel.oz.au)