Thread: PGparam extension version 0.4
Version 0.4 of libpq param put and PGresult get functions. Added support for inet and cidr, couple bug fixes. If you compile the test file, make sure you link with the patched libpq.so. Andrew
This has been saved for the 8.4 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---------------------------------------------------------------------------
Andrew Chernow wrote:
> Version 0.4 of libpq param put and PGresult get functions.
>
> Added support for inet and cidr, couple bug fixes. If you compile the
> test file, make sure you link with the patched libpq.so.
>
> Andrew
[ application/x-compressed is not supported, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
I am trying to add support for timestamps in our proposed libpq PGparam patch. I ran into something I don't really understand. I wasn't sure if it was my libpq code that was wrong (converts a binary timestamp into a time_t or struct tm) so I tried it from psql. Server is using EST (8.3devel) x86_64 centos 5 TIMESTAMP WITHOUT TIME ZONE appears to be incorrectly adjusting the time. postgres=# create table t (ts timestamp); postgres=# insert into t values (now()); postgres=# select * from t; ts ---------------------------- 2007-12-09 08:00:00.056244 postgres=# select ts at time zone 'UTC' from t; timezone ------------------------------- 2007-12-09 03:00:00.056244-05 ====> Shouldn't this be 13:00 TIMESTAMP WITH TIME ZONE returns the result I would expect. postgres=# create table t (ts timestamp with time zone); postgres=# insert into t values (now()); postgres=# select * from t; ts ---------------------------- 2007-12-09 08:00:00.056244 postgres=# select ts at time zone 'UTC' from t; timezone ------------------------------- 2007-12-09 13:00:00.056244-05 Is this expected/desired behavior? If it is, how are timestamps stored internally for WITHOUT TIME ZONE types? The docs don't really say. They do discuss how WITH TIME ZONE stores things, but WITHOUT is a little blury. Maybe I am missing something simple. Andrew
On Sunday 09 December 2007 09:44, Andrew Chernow wrote:
> I am trying to add support for timestamps in our proposed libpq PGparam
> patch. I ran into something I don't really understand. I wasn't sure if it
> was my libpq code that was wrong (converts a binary timestamp into a time_t
> or struct tm) so I tried it from psql.
>
> Server is using EST (8.3devel) x86_64 centos 5
>
> TIMESTAMP WITHOUT TIME ZONE appears to be incorrectly adjusting the time.
>
> postgres=# create table t (ts timestamp);
> postgres=# insert into t values (now());
> postgres=# select * from t;
> ts
> ----------------------------
> 2007-12-09 08:00:00.056244
>
> postgres=# select ts at time zone 'UTC' from t;
> timezone
> -------------------------------
> 2007-12-09 03:00:00.056244-05 ====> Shouldn't this be 13:00
>
No. 8 AM UTC is 3 AM Eastern.
>
> TIMESTAMP WITH TIME ZONE returns the result I would expect.
>
> postgres=# create table t (ts timestamp with time zone);
> postgres=# insert into t values (now());
> postgres=# select * from t;
> ts
> ----------------------------
> 2007-12-09 08:00:00.056244
>
> postgres=# select ts at time zone 'UTC' from t;
> timezone
> -------------------------------
> 2007-12-09 13:00:00.056244-05
>
Correspondingly, 8 AM eastern is 1 PM UTC.
>
> Is this expected/desired behavior? If it is, how are timestamps stored
> internally for WITHOUT TIME ZONE types? The docs don't really say. They
> do discuss how WITH TIME ZONE stores things, but WITHOUT is a little blury.
> Maybe I am missing something simple.
>
When timestamptzs are converted to timestamp, there is no time adjust, you
simply lose the tz offset information:
pagila=# select now(), now()::timestamp;
-[ RECORD 1 ]----------------------
now | 2007-12-09 11:25:52.923612-05
now | 2007-12-09 11:25:52.923612
If you store without timezone, you lose the original timezone information, so
selecting out "with time zone" simply selects the stored time in the time
zone you selected. HTH.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Okay, thanks. So using WITHOUT TIME ZONE basically means, store the provided value as UTC. Meaning, 8AM EST NOW() is stored/treated as 8AM UTC. That explains why my libpq code was getting 3AM for "without time zone" values. I am using code from src/interfaces/ecpg/pgtypeslib/timestamp.c timestamp2tm(). That uses localtime() after converting the timestamp to an epoch value. I changed this code so that it calls gmtime() for TIMESTAMPOID and localtime() for TIMESTAMPTZOID. Now it works perfectly :) Need to figure out how to handle times outside of the system time_t range. Thanks again, Andrew Robert Treat wrote: > On Sunday 09 December 2007 09:44, Andrew Chernow wrote: >> I am trying to add support for timestamps in our proposed libpq PGparam >> patch. I ran into something I don't really understand. I wasn't sure if it >> was my libpq code that was wrong (converts a binary timestamp into a time_t >> or struct tm) so I tried it from psql. >> >> Server is using EST (8.3devel) x86_64 centos 5 >> >> TIMESTAMP WITHOUT TIME ZONE appears to be incorrectly adjusting the time. >> >> postgres=# create table t (ts timestamp); >> postgres=# insert into t values (now()); >> postgres=# select * from t; >> ts >> ---------------------------- >> 2007-12-09 08:00:00.056244 >> >> postgres=# select ts at time zone 'UTC' from t; >> timezone >> ------------------------------- >> 2007-12-09 03:00:00.056244-05 ====> Shouldn't this be 13:00 >> > > No. 8 AM UTC is 3 AM Eastern. > >> TIMESTAMP WITH TIME ZONE returns the result I would expect. >> >> postgres=# create table t (ts timestamp with time zone); >> postgres=# insert into t values (now()); >> postgres=# select * from t; >> ts >> ---------------------------- >> 2007-12-09 08:00:00.056244 >> >> postgres=# select ts at time zone 'UTC' from t; >> timezone >> ------------------------------- >> 2007-12-09 13:00:00.056244-05 >> > > Correspondingly, 8 AM eastern is 1 PM UTC. > >> Is this expected/desired behavior? If it is, how are timestamps stored >> internally for WITHOUT TIME ZONE types? The docs don't really say. They >> do discuss how WITH TIME ZONE stores things, but WITHOUT is a little blury. >> Maybe I am missing something simple. >> > > When timestamptzs are converted to timestamp, there is no time adjust, you > simply lose the tz offset information: > > pagila=# select now(), now()::timestamp; > -[ RECORD 1 ]---------------------- > now | 2007-12-09 11:25:52.923612-05 > now | 2007-12-09 11:25:52.923612 > > If you store without timezone, you lose the original timezone information, so > selecting out "with time zone" simply selects the stored time in the time > zone you selected. HTH. >
On Sunday 09 December 2007 11:54, Andrew Chernow wrote:
> Okay, thanks. So using WITHOUT TIME ZONE basically means, store the
> provided value as UTC. Meaning, 8AM EST NOW() is stored/treated as 8AM
> UTC.
>
Not quite. Using WITHOUT TIME ZONE means to not store any time zone
information. It appears as UTC only because you selected it out as UTC.
pagila=# select now(), now() at time zone 'PST' , now()::timestamp at time
zone 'PST';
-[ RECORD 1 ]---------------------------
now | 2007-12-09 12:25:19.240661-05
timezone | 2007-12-09 09:25:19.240661
timezone | 2007-12-09 15:25:19.240661-05
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Andrew Chernow <ac@esilo.com> writes:
> Okay, thanks. So using WITHOUT TIME ZONE basically means, store the provided
> value as UTC. Meaning, 8AM EST NOW() is stored/treated as 8AM UTC.
No, I think you are more confused now than you were before.
For both types, the underlying stored value is just a number-of-seconds
offset from 2000-01-01 00:00:00. The question is what's the reference
time really. For WITHOUT TIME ZONE, what you see is what you get: it's
just a date and time, and nobody is actually promising anything about
timezone considerations. For WITH TIME ZONE, the convention is that the
reference time is 2000-01-01 00:00:00 UTC, and therefore it is accurate
to say that the *stored value* is always expressed in UTC.
What confuses people is that for display purposes, a TIMESTAMP WITH TIME
ZONE value is rotated to your local timezone (as set by the timezone
variable) and printed with your local zone offset. But that's not
what's really stored.
Now, about AT TIME ZONE: that's a trickier operation than it looks.
When you start with a timestamp WITH time zone, the meaning is
"here's a UTC time, give me the equivalent local time in this time
zone". What comes out is a timestamp WITHOUT time zone, which means
it'll just be printed as-is.
When you start with a timestamp WITHOUT time zone, the meaning is
"here is a local time in this time zone, give me the equivalent
UTC time". What comes out is a timestamp WITH time zone, which as
we already saw is implicitly UTC inside the system, which is correct.
But you have to remember that that value will be rotated back to
your local zone for display. I think that extra conversion is what
was confusing you to start with.
Another point to keep in mind is that if the system is forced to
assume something about the timezone of a WITHOUT TIME ZONE value,
it will assume your local time zone setting. In particular this
happens during forced coercions between WITH and WITHOUT TIME ZONE.
So for example, in
regression=# select now(), now()::timestamp without time zone; now | now
-------------------------------+----------------------------2007-12-09 13:21:50.619644-05 | 2007-12-09 13:21:50.619644
(1 row)
the two values are in fact different numbers-of-seconds internally.
They print the same, but that's because in the first case the
timestamp-with-time-zone output routine rotated from UTC to my
local zone (EST) during printout. In the second case the same
5-hour offset was applied by the cast to without-time-zone, and
then the timestamp-without-time-zone output routine just printed
what it had without any magic.
regards, tom lane
got it. stored vs. displyed was confusing me. Andrew Tom Lane wrote: > Andrew Chernow <ac@esilo.com> writes: >> Okay, thanks. So using WITHOUT TIME ZONE basically means, store the provided >> value as UTC. Meaning, 8AM EST NOW() is stored/treated as 8AM UTC. > > No, I think you are more confused now than you were before. > > For both types, the underlying stored value is just a number-of-seconds > offset from 2000-01-01 00:00:00. The question is what's the reference > time really. For WITHOUT TIME ZONE, what you see is what you get: it's > just a date and time, and nobody is actually promising anything about > timezone considerations. For WITH TIME ZONE, the convention is that the > reference time is 2000-01-01 00:00:00 UTC, and therefore it is accurate > to say that the *stored value* is always expressed in UTC. > > What confuses people is that for display purposes, a TIMESTAMP WITH TIME > ZONE value is rotated to your local timezone (as set by the timezone > variable) and printed with your local zone offset. But that's not > what's really stored. > > Now, about AT TIME ZONE: that's a trickier operation than it looks. > When you start with a timestamp WITH time zone, the meaning is > "here's a UTC time, give me the equivalent local time in this time > zone". What comes out is a timestamp WITHOUT time zone, which means > it'll just be printed as-is. > When you start with a timestamp WITHOUT time zone, the meaning is > "here is a local time in this time zone, give me the equivalent > UTC time". What comes out is a timestamp WITH time zone, which as > we already saw is implicitly UTC inside the system, which is correct. > But you have to remember that that value will be rotated back to > your local zone for display. I think that extra conversion is what > was confusing you to start with. > > Another point to keep in mind is that if the system is forced to > assume something about the timezone of a WITHOUT TIME ZONE value, > it will assume your local time zone setting. In particular this > happens during forced coercions between WITH and WITHOUT TIME ZONE. > So for example, in > > regression=# select now(), now()::timestamp without time zone; > now | now > -------------------------------+---------------------------- > 2007-12-09 13:21:50.619644-05 | 2007-12-09 13:21:50.619644 > (1 row) > > the two values are in fact different numbers-of-seconds internally. > They print the same, but that's because in the first case the > timestamp-with-time-zone output routine rotated from UTC to my > local zone (EST) during printout. In the second case the same > 5-hour offset was applied by the cast to without-time-zone, and > then the timestamp-without-time-zone output routine just printed > what it had without any magic. > > regards, tom lane > >
On Sun, Dec 09, 2007 at 11:54:25AM -0500, Andrew Chernow wrote: > That explains why my libpq code was getting 3AM for "without time zone" > values. I am using code from src/interfaces/ecpg/pgtypeslib/timestamp.c > timestamp2tm(). That uses localtime() after converting the timestamp to an > epoch value. I changed this code so that it calls gmtime() for > TIMESTAMPOID and localtime() for TIMESTAMPTZOID. Now it works perfectly :) Does this mean pgtypeslib is buggy? This code has been taken from the backend ages ago, so some changes might have occured that I'm not aware of. Or was the code incorrectly used? Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org Go SF 49ers! Go VfL Borussia! Use Debian GNU/Linux! Use PostgreSQL!
>>Or was the code incorrectly used? Hard for me to say, but I think its about caller context. The way I am using it might be different ... hey the function was static ... copy & paster be warned! The code appears to be doing the same thing as the backend (with the exclusion of backend stuff like HasCTZSet and forced conversions). I plan to do an extensive test sometime today. So far, I am getting the correct timestamp conversions across the board. Andrew Michael Meskes wrote: > On Sun, Dec 09, 2007 at 11:54:25AM -0500, Andrew Chernow wrote: >> That explains why my libpq code was getting 3AM for "without time zone" >> values. I am using code from src/interfaces/ecpg/pgtypeslib/timestamp.c >> timestamp2tm(). That uses localtime() after converting the timestamp to an >> epoch value. I changed this code so that it calls gmtime() for >> TIMESTAMPOID and localtime() for TIMESTAMPTZOID. Now it works perfectly :) > > Does this mean pgtypeslib is buggy? This code has been taken from the > backend ages ago, so some changes might have occured that I'm not aware > of. Or was the code incorrectly used? > > Michael