Message-ID: <4A127038.3010103@4js.com>
Date: Tue, 19 May 2009 10:39:20 +0200
From: Sebastien FLAESCH <sf@4js.com>
Organization: Four J's Development Tools
User-Agent: Thunderbird 2.0.0.9 (X11/20071031)
MIME-Version: 1.0
To: pgsql-general@postgresql.org
CC: mmoncure@gmail.com
Subject: INTERVAL SECOND limited to 59 seconds?
Content-Type: multipart/mixed; boundary="------------070100050409010207050907"

This is a multi-part message in MIME format.
--------------070100050409010207050907
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hello,

Can someone explain this:

test1=> create table t1 ( k int, i interval second );
CREATE TABLE
test1=> insert into t1 values ( 1, '-67 seconds' );
INSERT 0 1
test1=> insert into t1 values ( 2, '999 seconds' );
INSERT 0 1
test1=> select * from t1;
  k |     i
---+-----------
  1 | -00:00:07
  2 | 00:00:39
(2 rows)

I would expect that an INTERVAL SECOND can store more that 59 seconds.

Same question for INTERVAL MINUTE TO SECOND (but here we get an overflow error):

test1=> create table t2 ( k int, i interval minute to second );
CREATE TABLE
test1=> insert into t2 values ( 2, '9999:59' );
ERROR:  interval field value out of range: "9999:59"
LINE 1: insert into t2 values ( 2, '9999:59' );
                                    ^
test1=> insert into t2 values ( 2, '999:59' );
ERROR:  interval field value out of range: "999:59"
LINE 1: insert into t2 values ( 2, '999:59' );
                                    ^
test1=> insert into t2 values ( 2, '99:59' );
ERROR:  interval field value out of range: "99:59"
LINE 1: insert into t2 values ( 2, '99:59' );
                                    ^
test1=> insert into t2 values ( 1, '59:59' );
INSERT 0 1

test1=> insert into t2 values ( 2, '-123:59' );
INSERT 0 1

test1=> select * from t2;
  k |     i
---+-----------
  1 | 00:59:59
  2 | -00:59:00
(2 rows)


It's ok when using DAYs:

test1=> create table t3 ( k int, i interval day to second );
CREATE TABLE
test1=> insert into t3 values ( 1, '-9999 18:59:59' );
INSERT 0 1
test1=> insert into t3 values ( 1, '9999999 18:59:59' );
INSERT 0 1
test1=> select * from t3;
  k |           i
---+-----------------------
  1 | -9999 days +18:59:59
  1 | 9999999 days 18:59:59
(2 rows)


Thanks a lot!
Seb


--------------070100050409010207050907
Content-Type: text/plain;
 name="libpqtest1.c"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="libpqtest1.c"

/*
Version:    8.4.beta1
Created by: sf@4js.com

Problem with INTERVAL input format
----------------------------------

After executing this program, 2 rows are present in the table.
Only the first has the expected values...

Why does the second insert fail to insert "123 11" in INTERVAL DAY TO HOUR?
Diagnostic info:
  SQL State: 22007
  Message  : invalid input syntax for type interval: " 123 11"

Why does the third row show "00:00:00" in first INTERVAL YEAR column?

[sf@fox problems]$ psql test1 -U pgsuser
psql (8.4beta1)
Type "help" for help.

test1=> select * from t1;
 k |      i1      |        i2
---+--------------+-------------------
 1 | -12345 years | 123 days 11:00:00
 3 | 00:00:00     | 123 days 11:00:00
(2 rows)

When inserting rows with psql, the format used by the C program are supported:

test1=> insert into t1 values ( 4, '-12345', '123 11' );
INSERT 0 1
test1=> select * from t1 where k=4;
 k |      i1      |        i2
---+--------------+-------------------
 4 | -12345 years | 123 days 11:00:00
(1 row)

So what am I doing wrong here?

*/

#include <stdio.h>
#include <libpq-fe.h>

static int checkResult(PGresult * r)
{
    if (r == NULL)
        return 0;
    switch (PQresultStatus(r)) {
    case PGRES_COMMAND_OK:
    case PGRES_TUPLES_OK:
        return 1;
    default:
        return 0;
    }
}

static void getErrorInfo(PGresult * r)
{
    if (r == NULL)
       return;
    fprintf(stderr, "Diagnostic info:\n");
    fprintf(stderr, "  SQL State: %s\n", PQresultErrorField(r, PG_DIAG_SQLSTATE));
    fprintf(stderr, "  Message  : %s\n", PQresultErrorField(r, PG_DIAG_MESSAGE_PRIMARY));
}

int main(int argc, char **argv)
{
    PGresult *r;
    PGconn *c;
    Oid paramTypes[10];
    const char *paramValues[10];

    fprintf(stdout,"++ Connecting...\n");
    c = PQconnectdb("dbname='test1' user='pgsuser' password='fourjs'");
    if (c == NULL) {
        fprintf(stderr,">> Could not connect.\n");
        exit(1);
    }

    fprintf(stdout,"++ Creating table t1 ...\n");
    r = PQexec(c, "DROP TABLE t1");
    PQclear(r);
    r = PQexec(c, "CREATE TABLE t1 ( k INT, i1 INTERVAL YEAR, i2 INTERVAL DAY TO HOUR)");
    if (!checkResult(r)) {
        fprintf(stderr,">> Could not create table 1.\n");
        getErrorInfo(r);
        exit(1);
    }
    PQclear(r);

    fprintf(stdout,"++ Preparing INSERT ...\n");
    paramTypes[0] = 23;     /* INT4 */
    paramTypes[1] = 1186;   /* INTERVAL */
    paramTypes[2] = 1186;   /* INTERVAL */
    r = PQprepare(c, "s1",
                  "INSERT INTO t1 VALUES ( $1, $2, $3 )",
                  3, (const Oid *) paramTypes);
    if (!checkResult(r)) {
        fprintf(stderr,">> Could not prepare stmt 1.\n");
        getErrorInfo(r);
        exit(1);
    }
    PQclear(r);

    /* This is working */
    fprintf(stdout,"++ Executing INSERT (1) ...\n");
    paramValues[0] = "1";
    paramValues[1] = "-12345 years";
    paramValues[2] = " 123 11:00";
    r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
    if (!checkResult(r)) {
        fprintf(stderr,">> Could not exec stmt 1.\n");
        getErrorInfo(r);
        exit(1);
    }
    PQclear(r);

    /* This is NOT working */
    fprintf(stdout,"++ Executing INSERT (2) ...\n");
    paramValues[0] = "2";
    paramValues[1] = "-12345";
    paramValues[2] = " 123 11";
    r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
    if (!checkResult(r)) {
        fprintf(stderr,">> Could not exec stmt 2.\n");
        getErrorInfo(r);
        /*exit(1);*/
    }
    PQclear(r);

    /* This is NOT working */
    fprintf(stdout,"++ Executing INSERT (3) ...\n");
    paramValues[0] = "3";
    paramValues[1] = "-12345";
    paramValues[2] = " 123 11:00";
    r = PQexecPrepared(c, "s1", 3, paramValues, NULL, NULL, 0);
    if (!checkResult(r)) {
        fprintf(stderr,">> Could not exec stmt 3.\n");
        getErrorInfo(r);
        exit(1);
    }
    PQclear(r);

    PQfinish(c);
}


--------------070100050409010207050907--