[BUGS] Output of date_part('quarter', date)
Hello,
I tried to use function date_part('quarter', date) in query and I have
got wrong results - for first quarter I've got all rows from 01.01 to
31.03
for second - from from 01.04 to 31.07 (!!!)
for third - from from 01.08 to 30.11 (!!!)
for forth - from from 01.12 to 31.12 (!!!)
---
PostgreSQL 7.0.2 on i386-pc-bsdi4.0.1, compiled by gcc 2.7.2.1
---
Queries:
billing=> select InDate from FirmICO where date_part('year', indate)=2000 and
date_part('quarter', indate)=1 ORDER BY InDate;
indate
2000-01-06 16:05:04+06
2000-01-06 16:06:50+06
2000-01-10 12:19:34+06
2000-01-10 12:20:07+06
2000-01-11 11:18:08+06
2000-01-11 14:41:40+06
2000-01-12 10:04:05+06
2000-01-12 15:33:07+06
2000-01-13 11:32:11+06
2000-01-14 10:27:57+06
2000-01-17 13:48:44+06
2000-01-17 15:39:30+06
2000-01-18 17:35:10+06
2000-01-18 18:04:14+06
2000-01-19 11:08:55+06
2000-01-19 11:09:29+06
2000-01-20 10:13:34+06
2000-01-20 12:28:59+06
2000-01-21 10:43:20+06
2000-01-24 15:16:09+06
2000-01-25 11:26:51+06
2000-01-26 08:56:56+06
2000-01-26 12:07:17+06
2000-01-27 11:07:26+06
2000-01-27 14:18:51+06
2000-01-28 11:02:30+06
2000-01-28 17:08:50+06
2000-01-29 17:41:54+06
2000-01-31 10:13:31+06
2000-01-31 16:36:38+06
2000-01-31 16:39:16+06
2000-02-01 11:39:13+06
2000-02-02 10:34:28+06
2000-02-03 11:40:45+06
2000-02-04 10:52:18+06
2000-02-07 10:48:39+06
2000-02-07 16:36:19+06
2000-02-07 16:55:50+06
2000-02-07 16:57:45+06
2000-02-08 10:31:06+06
2000-02-09 11:52:13+06
2000-02-10 10:57:24+06
2000-02-10 13:21:28+06
2000-02-11 11:36:22+06
2000-02-14 12:27:07+06
2000-02-15 10:57:01+06
2000-02-15 10:57:40+06
2000-02-15 17:38:40+06
2000-02-16 12:00:27+06
2000-02-17 10:47:41+06
2000-02-17 14:23:55+06
2000-02-18 10:56:09+06
2000-02-21 10:49:42+06
2000-02-22 11:03:45+06
2000-02-22 17:39:58+06
2000-02-23 10:49:47+06
2000-02-23 10:51:13+06
2000-02-24 12:49:57+06
2000-02-25 10:36:28+06
2000-02-28 11:45:46+06
2000-02-29 09:43:17+06
2000-02-29 11:04:24+06
2000-02-29 11:12:53+06
2000-02-29 17:39:29+06
2000-03-02 11:06:36+06
2000-03-02 13:19:07+06
2000-03-03 13:09:31+06
2000-03-06 10:56:27+06
2000-03-06 16:29:30+06
2000-03-07 12:04:32+06
2000-03-07 17:02:46+06
2000-03-09 10:53:37+06
2000-03-09 11:29:59+06
2000-03-10 10:35:56+06
2000-03-13 10:50:45+06
2000-03-14 17:34:12+06
2000-03-14 17:36:11+06
2000-03-15 11:46:16+06
2000-03-16 11:41:51+06
2000-03-17 11:19:46+06
2000-03-20 10:00:12+06
2000-03-21 10:23:10+06
2000-03-22 10:10:34+06
2000-03-23 11:27:53+06
2000-03-23 15:37:24+06
2000-03-24 11:07:02+06
2000-03-24 17:32:22+06
2000-03-25 16:04:41+06
2000-03-27 12:06:45+07
2000-03-28 10:17:26+07
2000-03-29 11:53:05+07
2000-03-29 12:16:18+07
2000-03-30 11:45:04+07
2000-03-31 12:37:34+07
2000-03-31 17:51:58+07
2000-03-31 19:03:39+07
(96 rows)
billing=> select InDate from FirmICO where date_part('year', indate)=2000 and
date_part('quarter', indate)=2 ORDER BY InDate;
indate
2000-04-03 11:06:56+07
2000-04-04 13:30:11+07
2000-04-05 11:58:54+07
2000-04-05 13:09:53+07
2000-04-05 14:36:16+07
2000-04-05 17:45:07+07
2000-04-06 11:03:51+07
2000-04-07 10:47:30+07
2000-04-10 11:26:41+07
2000-04-11 13:01:13+07
2000-04-12 15:42:38+07
2000-04-12 15:44:08+07
2000-04-13 16:59:37+07
2000-04-14 11:00:21+07
2000-04-14 17:37:51+07
2000-04-17 13:23:31+07
2000-04-18 12:50:59+07
2000-04-18 12:51:48+07
2000-04-19 11:24:10+07
2000-04-20 10:35:03+07
2000-04-20 16:10:58+07
2000-04-21 11:28:44+07
2000-04-21 11:29:12+07
2000-04-24 11:08:42+07
2000-04-25 11:35:01+07
2000-04-26 11:22:12+07
2000-04-26 17:30:06+07
2000-04-27 11:36:57+07
2000-04-28 11:28:44+07
2000-04-28 18:29:45+07
2000-04-28 19:11:10+07
2000-04-29 16:00:08+07
2000-05-04 10:12:29+07
2000-05-05 11:41:29+07
2000-05-05 16:29:43+07
2000-05-06 16:29:14+07
2000-05-06 16:30:10+07
2000-05-10 17:55:20+07
2000-05-11 10:36:16+07
2000-05-11 11:59:54+07
2000-05-12 15:38:40+07
2000-05-15 10:54:19+07
2000-05-15 12:58:14+07
2000-05-16 11:22:38+07
2000-05-16 14:43:42+07
2000-05-17 12:02:38+07
2000-05-17 14:22:53+07
2000-05-17 14:26:46+07
2000-05-17 17:32:37+07
2000-05-18 11:00:01+07
2000-05-19 10:44:44+07
2000-05-22 11:27:16+07
2000-05-23 11:37:59+07
2000-05-24 11:15:54+07
2000-05-24 17:37:25+07
2000-05-25 11:32:16+07
2000-05-26 10:53:36+07
2000-05-29 12:16:38+07
2000-05-30 10:02:51+07
2000-05-31 10:46:53+07
2000-05-31 17:13:29+07
2000-05-31 17:42:50+07
2000-05-31 18:24:12+07
2000-06-02 12:55:58+07
2000-06-05 15:57:18+07
2000-06-06 12:16:40+07
2000-06-07 09:39:52+07
2000-06-07 15:31:01+07
2000-06-07 18:06:51+07
2000-06-08 11:06:16+07
2000-06-08 15:28:32+07
2000-06-09 10:26:15+07
2000-06-13 12:05:38+07
2000-06-14 11:02:55+07
2000-06-14 11:16:29+07
2000-06-14 11:31:46+07
2000-06-14 17:51:11+07
2000-06-15 10:46:36+07
2000-06-15 11:06:34+07
2000-06-16 11:40:32+07
2
[BUGS] impossible to create a table having the same name of a sequence object
jose ([EMAIL PROTECTED]) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description impossible to create a table having the same name of a sequence object Long Description prova=# \d List of relations Name | Type | Owner --+--+-- a| sequence | postgres comuni | table| postgres distributors | table| postgres films| table| postgres tablename| table| postgres (5 rows) prova=# create table a(id int); ERROR: Relation 'a' already exists prova=# Sample Code No file was uploaded with this report
Re: [BUGS] Output of date_part('quarter', date)
> I tried to use function date_part('quarter', date) in query and I have
> got wrong results...
Yeesh! Don't know what I was thinking at the time, but it sure is wrong.
If you have PostgreSQL built from source, then you can fix this by
editing src/backend/utils/timestamp.c on or around line 2056. Change a
single line, just under the "case DTK_QUARTER:" from
result = (tm->tm_mon / 4) + 1;
to
result = ((tm->tm_mon - 1) / 3) + 1;
and you should start getting the right answer. Will be fixed in the next
release.
- Thomas
Re: [BUGS] impossible to create a table having the same name of a sequence object
[EMAIL PROTECTED] writes: > impossible to create a table having the same name of a sequence object This is not a bug. A sequence is a table, of a sort, and so there's just one namespace for both. regards, tom lane
Re: [BUGS] update and tcl/tk
Bruno LEVEQUE <[EMAIL PROTECTED]> wrote: > [ via libpgtcl, the given query updates no rows ] > If I update directly > set datestyle to 'postgres, european'; update cheque set ok='x' where num_cpte=8 and >date='09-11-2000' and nb_cheq=4919351 and lib like 'gynéco' and ok='' and deb=200.00 >and cred=0.0 > It's well. I initially guessed that libpgtcl was causing a character set translation problem in the LIKE string, but examination of -d5 dump output provided by Bruno shows that that theory was all wet. The real difference is in the date = '09-11-2000' clause. The tcl/tk trace has a date constant that corresponds to 2000-09-11, the psql trace a constant corresponding to 2000-11-09. The former corresponds to the way that the input '09-11-2000' would be parsed in the default ISO datestyle, whereas the latter corresponds to the way it'd be parsed in Postgres/European datestyle. But, you say, the Tcl script *is* issuing a SET DATESTYLE! Well, what it's actually doing is issuing two queries in a single query string: query: set datestyle to 'postgres, european'; update cheque set ok='' where num_cpte=8 and date='09-11-2000' and nb_cheq=4919351 and lib like 'gynéco' and ok='x' and deb=200.00 and cred=0.0 It turns out that by the time the SET command is executed, the system has already parsed the whole querystring and turned it into an internal parsetree --- including reduction of the date constant to internal form. So the date constant is interpreted in the initial default datestyle, which is ISO. In the psql case, even though you enter what seems to be exactly the same thing, psql breaks up the line at the first semicolon and transmits the SET and the UPDATE in separate query cycles. So the problem does not show up under psql. This rather surprising behavior should be gone in 7.1, because of some reorganization of the query processing pipeline that was done for unrelated reasons. In the meantime, the workaround is to issue the SET in a separate command string before you send any queries that depend on the SET to have been done before they are parsed. regards, tom lane
Re: [BUGS] dump of functions does not handle backslashes correctly
"Robert B. Easter" <[EMAIL PROTECTED]> writes: > Is this a pg_dump bug or is there there some way to do this right? It's a pg_dump bug --- pg_dump knows how to quote special characters in string literals, but failed to do so for function bodies (and a number of other places too). I've committed a fix. regards, tom lane
Re: [BUGS] Output of date_part('quarter', date)
> I tried to use function date_part('quarter', date) in query and I have
> got wrong results - for first quarter I've got all rows from 01.01 to
> 31.03
> for second - from from 01.04 to 31.07 (!!!)
> for third - from from 01.08 to 30.11 (!!!)
> for forth - from from 01.12 to 31.12 (!!!)
> ---
> PostgreSQL 7.0.2 on i386-pc-bsdi4.0.1, compiled by gcc 2.7.2.1
> ---
until bug will fix, in 7.0.2 you can use:
... WHERE to_char(field, ' Q') = '2000 1'
(it's - may be - faster than 2x date_part())
Karel
