On Sun, Oct 31, 2004 at 05:55:23PM -0500, Tom Lane wrote:
[...]
What about letting the user decide on the behaviour through a config
option? I really missed this when the integer parsing changed.

The default could be to reject ambiguous input, allowing the user to
choose the assumed zone if he wants to, in a global and per-connection
basis.

--
Vinko Vrsalovic <el[|-@-|]vinko.cl>

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Mon, Nov 01, 2004 at 01:57:38PM -0300, Vinko Vrsalovic wrote:
It's not that simple. In this case the conversion will now produce a
different datatype, which means that perfectly valid pl/pgsql may now
be invalid. Columns defined unique will now have a different criteria
for uniqueness. Even how timestamps are stored will be different. By
switching a config option, you may have just invalidated your entire
database.

For the parsing integer issue it may have worked, but this is another
kettle of fish. I don't think you can do this as a simple switch, it
would have to set during the initdb and not allowed to be changed
afterwards. I don't know if that something that can be supported.

Hope this helps,
--
Martijn van Oosterhout <kleptog (AT) svana (DOT) org> http://svana.org/kleptog/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBhnujY5Twig3Ge+YRAlx4AKCqFWIQ6feWj3+N1CXvKr w7X741BgCglZUo
mIp5/C49e2XsTmTrKnGlK9c=
=7dnQ
-----END PGP SIGNATURE-----

On Mon, Nov 01, 2004 at 07:08:39PM +0100, Martijn van Oosterhout wrote:

I suspected it wasn't that easy. Anyhow, I strongly believe
that when no reasonable defaults can be deduced, the software
should give the user the ability to decide what he wants to do.

Of course technical (implementation, maintenance, etc.) issues are
highly relevant and if it can't reasonably be done, well, tough luck,
but I think (and I don't have a clue about the internals of
PostgreSQL, so take this with two grains of salt) a solution such
as the one you mention should be given consideration.

--
Vinko Vrsalovic <el[|-@-|]vinko.cl>

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Sunday 31 October 2004 11:44 am, Tom Lane wrote:
Would it help to add the PG locale TZ to the insert statement? For
example the following queries return the TZ as text.

select to_char(now(),'tz');
to_char
---------
pst

select to_char(now()-'3 days'::interval,'tz');
to_char
---------
pdt

So the following might fix this particular situation:
insert into sensor_readings_numeric (...) values (...,'2004-10-31
01:00:00 ' || to_char(now(),'tz'),...)

I realize that it assumes that the data is being inserted at the time
it was taken so a reading taken just before DST changes and inserted
just after will be incorrect but it may work for this particular app.

Of course the better solution is to have the application generate a
fully-qualified timestamp with time zone. Generating all the
timestamps in UTC and explicitly specifying that in the insert is
probably the easiest way to go. Your queries will still have your
local-appropriate TZ:

select '2004-10-31 00:00:00+00'::timestamptz;
timestamptz
------------------------
2004-10-30 17:00:00-07

select '2004-11-01 00:00:00+00'::timestamptz;
timestamptz
------------------------
2004-10-31 16:00:00-08


Cheers,
Steve


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings