Thread: datetime to timestamp

I'm trying to export an access database to postgres 7.3, but get an error
that datatype datetime does not exist.  Noticed in the docs that 7.3 will
not longer support datetime data type.  Will the ODBC driver be converting
all datetime datatypes to timestamp in future versions?  I've included the
doc snippet and log snippet below.

Thanks,

Will


"To ensure an upgrade path from versions of PostgreSQL earlier than 7.0,
we recognize datetime (equivalent to timestamp) and timespan (equivalent
to interval). These types are now restricted to having an implicit
translation to timestamp and interval, and support for these will be
removed in the next release of PostgreSQL (likely named 7.3)."
-- http://www.postgresql.org/idocs/index.php?datatype-datetime.html


<log snippet>

query='CREATE TABLE "tblCurrentApplicants"("APPLCDATE"
varchar(10),"FOREIGN" char(10),"DOMESTIC" char(10),"LASTNAME"
varchar(50),"FIRSTNAME" varchar(50),"NAME" varchar(100),"Title"
varchar(20),"SEX" varchar(2),"IAP" char(2),"MATHED" char(2),"RATING"
varchar(50),"ADMITMATH" varchar(10),"ADMITGRAD" varchar(10),"GREsubject"
float8,"TOEFL" float8,"MSLET" char(10),"OFFER" varchar(25),"OFFERTYPE"
varchar(25),"DATEOFFER" varchar(10),"INITIALRESPONSE"
varchar(35),"RESPONDUE" varchar(10),"CITIZEN" varchar(50),"ETHNIC"
varchar(50),"BIRTHDATE" varchar(10),"DEGREINST" varchar(50),"INSTADDR"
varchar(50),"DEGREE" varchar(5),"DEGREDATE" varchar(10),"GPA"
varchar(10),"DEGREINST1" varchar(50),"INSTADDR1" varchar(50),"DEGREE1"
varchar(5),"DEGREDATE1" varchar(10),"GPA1" varchar(10),"INTEREST"
varchar(254),"VISA" varchar(50),"PRESADDR1" varchar(50),"PRESADDR2"
varchar(50),"PRESADDR3" varchar(50),"ADVISOR" varchar(50),"COMMENTS"
varchar(254),"Selected" char(254),"FINALRESPONSE" varchar(50),"ACTUARY"
char(50),"GREquantitative" int4,"GREanalytical" int4,"GREverbal"
int4,"Rejectletter" datetime,"email" varchar(254),"recdatet1"
datetime,"recauth1" varchar(254),"recdate2" datetime,"recauth2"
varchar(254),"recdate3" datetime,"recauth3" varchar(254),"purposedate"
datetime,"gredate" datetime,"toefldate" datetime)'
ERROR from backend during send_query: 'ERROR:  Type "datetime" does not
exist'
STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error while
executing the query'
                 ------------------------------------------------------------
                 hdbc=262500944, stmt=262538944, result=262538704
                 manual_result=0, prepare=0, internal=0
                 bindings=0, bindings_allocated=0
                 parameters=0, parameters_allocated=0
                 statement_type=4, statement='CREATE TABLE
"tblCurrentApplicants"("APPLCDATE" varchar(10),"FOREIGN"
char(10),"DOMESTIC" char(10),"LASTNAME"
varchar(50),"FIRSTNAME" varchar(50),"NAME"
varchar(100),"Title" varchar(20),"SEX" varchar(2),"IAP"
char(2),"MATHED" char(2),"RATING" varchar(50),"ADMITMATH"
varchar(10),"ADMITGRAD" varchar(10),"GREsubject"
float8,"TOEFL" float8,"MSLET" char(10),"OFFER"
varchar(25),"OFFERTYPE" varchar(25),"DATEOFFER"
varchar(10),"INITIALRESPONSE" varchar(35),"RESPONDUE"
varchar(10),"CITIZEN" varchar(50),"ETHNIC"
varchar(50),"BIRTHDATE" varchar(10),"DEGREINST"
varchar(50),"INSTADDR" varchar(50),"DEGREE"
varchar(5),"DEGREDATE" varchar(10),"GPA"
varchar(10),"DEGREINST1" varchar(50),"INSTADDR1"
varchar(50),"DEGREE1" varchar(5),"DEGREDATE1"
varchar(10),"GPA1" varchar(10),"INTEREST"
varchar(254),"VISA" varchar(50),"PRESADDR1"
varchar(50),"PRESADDR2" varchar(50),"PRESADDR3"
varchar(50),"ADVISOR" varchar(50),"COMMENTS"
varchar(254),"Selected" char(254),"FINALRESPONSE"
varchar(50),"ACTUARY" char(50),"GREquantitative"
int4,"GREanalytical" int4,"GREverbal" int4,"Rejectletter"
datetime,"email" varchar(254),"recdatet1"
datetime,"recauth1" varchar(254),"recdate2"
datetime,"recauth2" varchar(254),"recdate3"
datetime,"recauth3" varchar(254),"purposedate"
datetime,"gredate" datetime,"toefldate" datetime)'
                 stmt_with_params='CREATE TABLE
"tblCurrentApplicants"("APPLCDATE" varchar(10),"FOREIGN"
char(10),"DOMESTIC" char(10),"LASTNAME"
varchar(50),"FIRSTNAME" varchar(50),"NAME"
varchar(100),"Title" varchar(20),"SEX" varchar(2),"IAP"
char(2),"MATHED" char(2),"RATING" varchar(50),"ADMITMATH"
varchar(10),"ADMITGRAD" varchar(10),"GREsubject"
float8,"TOEFL" float8,"MSLET" char(10),"OFFER"
varchar(25),"OFFERTYPE" varchar(25),"DATEOFFER"
varchar(10),"INITIALRESPONSE" varchar(35),"RESPONDUE"
varchar(10),"CITIZEN" varchar(50),"ETHNIC"
varchar(50),"BIRTHDATE" varchar(10),"DEGREINST"
varchar(50),"INSTADDR" varchar(50),"DEGREE"
varchar(5),"DEGREDATE" varchar(10),"GPA"
varchar(10),"DEGREINST1" varchar(50),"INSTADDR1"
varchar(50),"DEGREE1" varchar(5),"DEGREDATE1"
varchar(10),"GPA1" varchar(10),"INTEREST"
varchar(254),"VISA" varchar(50),"PRESADDR1"
varchar(50),"PRESADDR2" varchar(50),"PRESADDR3"
varchar(50),"ADVISOR" varchar(50),"COMMENTS"
varchar(254),"Selected" char(254),"FINALRESPONSE"
varchar(50),"ACTUARY" char(50),"GREquantitative"
int4,"GREanalytical" int4,"GREverbal" int4,"Rejectletter"
datetime,"email" varchar(254),"recdatet1"
datetime,"recauth1" varchar(254),"recdate2"
datetime,"recauth2" varchar(254),"recdate3"
datetime,"recauth3" varchar(254),"purposedate"
datetime,"gredate" datetime,"toefldate" datetime)'
                 data_at_exec=-1, current_exec_param=-1, put_data=0
                 currTuple=-1, current_col=-1, lobj_fd=-1
                 maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
scroll_concurrency=1
                 cursor_name='SQL_CUR0FA606C0'
                 ----------------QResult Info -------------------------------
                 fields=262538912, manual_tuples=0, backend_tuples=0,
tupleField=0, conn=0
                 fetch_count=0, fcount=0, num_fields=0, cursor='(NULL)'
                 message='(NULL)', command='(NULL)', notice='(NULL)'
                 status=7, inTuples=0
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR:  Type
"datetime" does not exist'

</log snippet>

Sorry for the

"William S. Bear" wrote:
>
> I'm trying to export an access database to postgres 7.3,
> but get an error that datatype datetime does not exist.
> Noticed in the docs that 7.3 will not longer support
> datetime data type.  Will the ODBC driver be converting
> all datetime datatypes to timestamp in future versions?

Which version of psqlodbc driver are you using ?
The current driver converts datetime type to timestamp.

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/

The driver does not convert a statment like:

CREATE TABLE FOO (DTAAA datetime)
to
CREATE TABLE FOO (DTAAA timestamp)

Postgre 7.3 remove the datetime type. Use:

 CREATE DOMAIN datetime AS timestamp

in your database (and in template1) to solve this.


Regards,

Alexandre



> Sorry for the
>
> "William S. Bear" wrote:
>>
>> I'm trying to export an access database to postgres 7.3,
>> but get an error that datatype datetime does not exist.
>> Noticed in the docs that 7.3 will not longer support
>> datetime data type.  Will the ODBC driver be converting
>> all datetime datatypes to timestamp in future versions?
>
> Which version of psqlodbc driver are you using ?
> The current driver converts datetime type to timestamp.
>
> regards,
> Hiroshi Inoue
>     http://w2422.nsk.ne.jp/~inoue/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

alexandre :: aldeia digital wrote:
>
> The driver does not convert a statment like:
>
> CREATE TABLE FOO (DTAAA datetime)
> to
> CREATE TABLE FOO (DTAAA timestamp)

Yes.

> Postgre 7.3 remove the datetime type. Use:
>
>  CREATE DOMAIN datetime AS timestamp
>
> in your database (and in template1) to solve this.

But what William is referring to is *datetime* type
in Access.

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/