Thread
-
BUG #1332: wrong results from age function
PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2004-11-29T11:37:50Z
The following bug has been logged online: Bug reference: 1332 Logged by: Robert Grabowski Email address: grabba@env.pl PostgreSQL version: 7.4.6 Operating system: Linux Description: wrong results from age function Details: select age('2004-02-01'::date, '2004-01-01'::date); age ------- 1 mon (1 row) select age('2004-03-01'::date, '2004-02-01'::date); age --------- 29 days (1 row) I think, it should be "1 mon". -
Re: BUG #1332: wrong results from age function
Bruno Wolff III <bruno@wolff.to> — 2004-12-01T08:45:19Z
On Mon, Nov 29, 2004 at 11:37:50 +0000, PostgreSQL Bugs List <pgsql-bugs@postgresql.org> wrote: > > The following bug has been logged online: > > Bug reference: 1332 > Logged by: Robert Grabowski > > Email address: grabba@env.pl > > PostgreSQL version: 7.4.6 > > Operating system: Linux > > Description: wrong results from age function > > Details: > > select age('2004-02-01'::date, '2004-01-01'::date); > age > ------- > 1 mon > (1 row) > > select age('2004-03-01'::date, '2004-02-01'::date); > age > --------- > 29 days > (1 row) > > I think, it should be "1 mon". I get "1 mon" when I try this: Welcome to psql 7.4.6, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit area=> select age('2004-03-01'::date, '2004-02-01'::date); age ------- 1 mon (1 row) I am actually using a version post 7.4.6 from cvs. I have also built postgres using integer date time values. -
Re: BUG #1332: wrong results from age function
Tom Lane <tgl@sss.pgh.pa.us> — 2004-12-01T15:28:58Z
Bruno Wolff III <bruno@wolff.to> writes: >> I think, it should be "1 mon". > I get "1 mon" when I try this: So do I. It might be timezone dependent though ... Robert, what timezone setting are you using? regards, tom lane
-
Re: BUG #1332: wrong results from age function
Robert Grabowski <grabba@env.pl> — 2004-12-01T15:52:52Z
Tom Lane wrote: > Bruno Wolff III <bruno@wolff.to> writes: > >>>I think, it should be "1 mon". > > >>I get "1 mon" when I try this: > > > So do I. It might be timezone dependent though ... Robert, what > timezone setting are you using? > > regards, tom lane > > Hi! It is some informactions from my PostgreSQL instalaction. Can it help you? Robert $ psql -U root -d tmpl Welcome to psql 7.4.6, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit tmpl=# select version(); version --------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 20040412 (Gentoo Linux 3.3.3-r6, ssp-3.3.2-2, pie-8.7.6) (1 row) tmpl=# show TimeZone; TimeZone ---------- unknown (1 row) tmpl=# select now(); now ------------------------------- 2004-12-01 16:47:46.522403+01 (1 row) tmpl=# show DateStyle; DateStyle ----------- ISO, MDY (1 row) tmpl=# show lc_time; lc_time --------- C (1 row) tmpl=# show australian_timezones; australian_timezones ---------------------- off (1 row) tmpl=# -
Re: BUG #1332: wrong results from age function
Tom Lane <tgl@sss.pgh.pa.us> — 2004-12-01T16:21:53Z
Robert Grabowski <grabba@env.pl> writes: > Tom Lane wrote: >> So do I. It might be timezone dependent though ... Robert, what >> timezone setting are you using? > tmpl=# show TimeZone; > TimeZone > ---------- > unknown > (1 row) That's not real helpful :-( ... but guessing that you are in Europe/Prague zone, I tried regression=# set TimeZone TO 'Europe/Prague'; SET regression=# select age('2004-03-01'::date, '2004-02-01'::date); age --------- 29 days (1 row) So it is a timezone-related issue. Furthermore, this still works: regression=# select age('2004-03-01'::timestamp, '2004-02-01'::timestamp); age ------- 1 mon (1 row) age() only comes in timestamp and timestamptz flavors, so when you use "date" inputs the timestamptz flavor is preferred. Tracing through the source code, I see that timestamp_age and timestamptz_age are coded exactly the same, meaning that in the timestamptz case the inputs are converted to GMT time, so what the code is looking at is effectively age('2004-02-29 23:00'::timestamp, '2004-01-31 23:00'::timestamp); I think returning "29 days" for that is not unreasonable. The bug is instead that we shouldn't be doing the arithmetic in GMT zone. Seems it would be better to break down both times in the local time zone. The reason we don't see the funny behavior in zones west of Greenwich is that, say, age('2004-03-01 05:00'::timestamp, '2004-02-01 05:00'::timestamp); still gives the desired result. But it would doubtless be better if the computation were being done as age('2004-03-01 00:00'::timestamp, '2004-02-01 00:00'::timestamp); which means we need to use a local-time-aware breakdown. One question is whether, if the two timestamps have different GMT offsets (due to a DST transition between), we should factor that into the age result or not. My feeling is "not" --- you don't want to see "1 month 1 hour" as the result even if in some sense it would be correct. So the proposed fix is to add tz and tzn parameters to the timestamp2tm() calls in timestamptz_age() (so that the breakdown is done in local time) but then ignore the tz values while doing the subtraction. Comments? regards, tom lane