Re: [BUGS] is this my date problem
On Wed, Oct 01, 2003 at 11:38:55PM -0400, Tom Lane wrote:
> Theodore Petrosky <[EMAIL PROTECTED]> writes:
> > I should have sent this... it is very interesting..
> > agencysacks=# select to_timestamp('01 October 2003
> > 00:01', 'DD Month HH24:MI');
> > to_timestamp
> > -
> > 0003-10-01 00:01:00
> > (1 row)
>
> Oh ... duh ... you should have said
>
> regression=# select to_timestamp('01 October 2003 00:01', 'DD FMMonth HH24:MI');
> to_timestamp
>
> 2003-10-01 00:01:00-04
> (1 row)
>
> There's been repeated discussion about whether our to_timestamp code
> should be more forgiving of input that does not match the given format,
> but right at the moment it's pretty unforgiving.
>
> BTW, have you considered the likelihood that you shouldn't be using
> to_timestamp at all? The timestamp datatype input converter gets this
> right:
>
> regression=# select '01 October 2003 00:01'::timestamp;
> timestamp
> -
> 2003-10-01 00:01:00
> (1 row)
>
> ISTM that to_timestamp is intended for cases where you want to be rigid
> about the data format. If you think that the input data is
> self-explanatory then try just casting it to timestamp.
The to_timestamp() do nothing with date/time and use internal
tm2timestamp() routine only. I don't think that check all date/time
ranges in to_timestamp() is good idea if it's already implemented at
the another place in our code.
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [BUGS] is this my date problem
So really if I had started coding this in a month
other than September I would have seen the problem
sooner...
Thanks for the help
Ted
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> Theodore Petrosky <[EMAIL PROTECTED]> writes:
> > I should have sent this... it is very
> interesting..
> > agencysacks=# select to_timestamp('01 October 2003
> > 00:01', 'DD Month HH24:MI');
> > to_timestamp
> > -
> > 0003-10-01 00:01:00
> > (1 row)
>
> Oh ... duh ... you should have said
>
> regression=# select to_timestamp('01 October 2003
> 00:01', 'DD FMMonth HH24:MI');
> to_timestamp
>
> 2003-10-01 00:01:00-04
> (1 row)
>
> There's been repeated discussion about whether our
> to_timestamp code
> should be more forgiving of input that does not
> match the given format,
> but right at the moment it's pretty unforgiving.
>
> BTW, have you considered the likelihood that you
> shouldn't be using
> to_timestamp at all? The timestamp datatype input
> converter gets this
> right:
>
> regression=# select '01 October 2003
> 00:01'::timestamp;
> timestamp
> -
> 2003-10-01 00:01:00
> (1 row)
>
> ISTM that to_timestamp is intended for cases where
> you want to be rigid
> about the data format. If you think that the input
> data is
> self-explanatory then try just casting it to
> timestamp.
>
> regards, tom lane
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [BUGS] is this my date problem
On Wed, Oct 01, 2003 at 07:57:18PM -0700, Theodore Petrosky wrote:
> here is the actual query:
>
> agencysacks=# SELECT jobnumseq, (SELECT cname FROM
> clientinfo ci WHERE ci.acode = j.clientid) as client,
> shrtdesc, to_char(proofduedate, 'Dy FMMon DD,
> HH12 am') FROM jobs j WHERE proofduedate BETWEEN
> to_timestamp('01 October 2003 00:01', 'DD Month
> HH24:MI') AND to_timestamp ('01 October 2003 23:59',
> 'DD Month HH24:MI') ORDER BY client,
>...
> I am trying to create a 'today' type query. between
> october 1, 2003 00:01 am and october 1, 2003 23:59
If you want a "today" type query, why are you using between? This
should work, and be a whole lot more reliable:
SELECT jobnumseq, (SELECT cname
FROM clientinfo ci
WHERE ci.acode = j.clientid) as client,
shrtdesc,
to_char(proofduedate, 'Dy FMMon DD, HH12 am')
FROM jobs j
WHERE date(proofduedate) = '2003-10-01')
ORDER BY client,
or if you want to derive "today" automatically this should work:
SELECT jobnumseq, (SELECT cname
FROM clientinfo ci
WHERE ci.acode = j.clientid) as client,
shrtdesc,
to_char(proofduedate, 'Dy FMMon DD, HH12 am')
FROM jobs j
WHERE date(proofduedate) = date(now()))
ORDER BY client,
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[BUGS] 7.3.4: memory leak in fe-exec.c:279 (realloc)
Hi,
I have found a memory leak in PostgreSQL 7.3.4,
src/interfaces/libpq/fe-exec.c line 279:
buffer = realloc(buffer, buflen);
if (buffer == NULL)
return NULL;
The realloc manpage says:
"If realloc() fails the original block is left untouched - it is
not freed or moved."
i.e. "buffer" is not freed if there is not enough memory for
realloc. The code should be like this:
temp_buffer = realloc(buffer, buflen);
if (temp_buffer == NULL) {
free(buffer);
return NULL;
}
buffer = temp_buffer;
Regards,
Max Kellermann
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [BUGS] is this my date problem
Karel Zak <[EMAIL PROTECTED]> writes:
> The to_timestamp() do nothing with date/time and use internal
> tm2timestamp() routine only. I don't think that check all date/time
> ranges in to_timestamp() is good idea if it's already implemented at
> the another place in our code.
But it evidently *isn't* checked. As of CVS tip:
regression=# select to_timestamp('44 October 2003', 'DD FMMonth ');
to_timestamp
2003-11-13 00:00:00-05
(1 row)
The regular timestamp input converter certainly has the checks:
regression=# select '44 October 2003'::timestamp;
ERROR: invalid input syntax for type timestamp: "44 October 2003"
but evidently those checks are not in whatever code path to_timestamp
is calling.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [BUGS] is this my date problem
On Thu, Oct 02, 2003 at 10:04:39AM -0400, Tom Lane wrote:
> Karel Zak <[EMAIL PROTECTED]> writes:
> > The to_timestamp() do nothing with date/time and use internal
> > tm2timestamp() routine only. I don't think that check all date/time
> > ranges in to_timestamp() is good idea if it's already implemented at
> > the another place in our code.
>
> But it evidently *isn't* checked. As of CVS tip:
>
> regression=# select to_timestamp('44 October 2003', 'DD FMMonth ');
> to_timestamp
>
> 2003-11-13 00:00:00-05
> (1 row)
>
> The regular timestamp input converter certainly has the checks:
>
> regression=# select '44 October 2003'::timestamp;
> ERROR: invalid input syntax for type timestamp: "44 October 2003"
Sure, because it check date/time parsers and there isn't common way
how check it if you don't use there parses.
OK, I will add 'tm' struct checker to my TODO for 7.5
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] 7.3.4: memory leak in fe-exec.c:279 (realloc)
Max Kellermann <[EMAIL PROTECTED]> writes: > I have found a memory leak in PostgreSQL 7.3.4, > src/interfaces/libpq/fe-exec.c line 279: This appears to have been dealt with already (although realistically, it would be an extremely brain-dead realloc that could ever fail here, since the block is being shrunk not grown). regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
