What's special about 1916-10-01 02:25:20? Odd jump in internal timestamptz representation - Mailing list pgsql-general

(forwarded from pgsql-interfaces because no response there; can
anybody tell me if I really have a bug, or am just a bit dim?)

Hello,

Below is a test C program, which fetches some timestamp literals and
prints their internal representation, which is the number of seconds
after 2000-01-01, stored as a double. I wrote this program on windows,
so the imports might look unfamiliar to unix users, but it shouldn't
take much effort to port, if you like. My server is 8.1 on Windows XP.

The output from this program is:

-2627158159.000000
-2627156080.000000
-2627156079.000000
-2627156079.000000

which corresponds to timestamps:
1916-10-01 02:25:20  with timezone
1916-10-01 02:25:20  sans timezone
1916-10-01 02:25:21  with timezone
1916-10-01 02:25:21  sans timezone

The first line of output puzzles me: why is '1916-10-01 02:25:20'
2627158159 seconds before 2000-01-01, while '1916-10-01 02:25:21' is
2627156080 before; a difference of 2080 seconds, or 34m:40s.

Is this correct? I don't think so, but there might be some subtlety of
timezone and date arithmetic which I've missed.

Thanks,
Alistair

----------------------------------------

#include <stdio.h>
#include <stdlib.h>
/* for ntohl/htonl
#include <winsock.h>
#include <sys/types.h>
*/
#include "libpq-fe.h"


static void exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}


void check_sql(PGconn *conn, PGresult *res, ExecStatusType expected)
{
    if (PQresultStatus(res) != expected)
    {
        fprintf(stderr, "SQL failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
}

void check_cmd(PGconn *conn, PGresult *res)
{
    check_sql(conn, res, PGRES_COMMAND_OK);
}

void check_qry(PGconn *conn, PGresult *res)
{
    check_sql(conn, res, PGRES_TUPLES_OK);
}

void revbytes2(int n, char *pfrom, char *pto)
{
    if (n == 0) return;
    *pto = *pfrom;
    revbytes2(--n, ++pfrom, --pto);
}

void revbytes(int n, void *pfrom, void *pto)
{
    revbytes2(n, (char*)pfrom, ((char*)pto)+n-1);
}


void printColOne(PGresult *res)
{
    double t, *tptr;
    tptr = (double *) PQgetvalue(res, 0, 0);
    revbytes(8, tptr, &t);
    /* t = ntohl(*tptr);  -- this doesn't work!? must be me... */
    printf("%f\n", t);
}


int main(int argc, char **argv)
{
    const char *conninfo;
    PGconn     *conn;
    PGresult   *res;
    double t, *tptr;

    /*
     * If the user supplies a parameter on the command line, use it as the
     * conninfo string; otherwise default to setting dbname=postgres and using
     * environment variables or defaults for all other connection parameters.
     */
    if (argc > 1)
        conninfo = argv[1];
    else
        conninfo = "user = postgres";

    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);

    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
        exit_nicely(conn);
    }

    res = PQexecParams(conn, "select timestamp with time zone
'1916-10-01 02:25:20'"
        , 0, NULL, NULL, NULL, NULL, 1 );
    check_qry(conn, res);
    printColOne(res);
    PQclear(res);

    res = PQexecParams(conn, "select timestamp without time zone
'1916-10-01 02:25:20'"
        , 0, NULL, NULL, NULL, NULL, 1 );
    check_qry(conn, res);
    printColOne(res);
    PQclear(res);

    res = PQexecParams(conn, "select timestamp with time zone
'1916-10-01 02:25:21'"
        , 0, NULL, NULL, NULL, NULL, 1 );
    check_qry(conn, res);
    printColOne(res);
    PQclear(res);

    res = PQexecParams(conn, "select timestamp without time zone
'1916-10-01 02:25:21'"
        , 0, NULL, NULL, NULL, NULL, 1 );
    check_qry(conn, res);
    printColOne(res);
    PQclear(res);

    /* close the connection to the database and cleanup */
    PQfinish(conn);

    return 0;
}