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

Search Discussions

  • Andrew Chernow at Aug 21, 2007 at 7:44 pm
    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
  • Bruce Momjian at Sep 14, 2007 at 3:54 am
    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. +
  • Andrew Chernow at Dec 9, 2007 at 3:17 pm
    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
  • Robert Treat at Dec 9, 2007 at 4:27 pm

    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
  • Andrew Chernow at Dec 9, 2007 at 4:54 pm
    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.
  • Robert Treat at Dec 9, 2007 at 5:26 pm

    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
  • Tom Lane at Dec 9, 2007 at 6:26 pm

    Andrew Chernow 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
  • Andrew Chernow at Dec 9, 2007 at 8:08 pm
    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
  • Michael Meskes at Dec 10, 2007 at 8:08 am

    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!
  • Andrew Chernow at Dec 10, 2007 at 12:39 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 21, '07 at 7:16p
activeDec 10, '07 at 12:39p
posts11
users6
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase