Re: inserting into "date" field returns error (COPY/BINARY)

On Tue, Apr 23, 2013 at 2:54 AM, Konstantin Izmailov <pgfizm(at)gmail(dot)com> wrote:
> I have implemented a C++ procedure for inserting data using libpq and COPY
> WITH BINARY command. All field types are working as expected in PG9.2,
> except "date":
> ERROR: incorrect binary data format
> CONTEXT: COPY table, line 1, column date_xyz
>
> The passed value for "date" is a 64-bit integer, calculated by the same way
> as the value for "timestamp". Should they be different?
>
> Here is simplified fragment of code:
> typedef __int64 PGTimeStamp;
> ...
> pField->dataLength = htonl(sizeof(PGTimeStamp));
> DBTIMESTAMP ts = { 2012, 10, 5, 0, 0, 0, 0 };
> *(PGTimeStamp*)&pField->data = ConvertADOToPG(&ts);
> ...
> PGTimeStamp ConvertADOToPG(DBTIMESTAMP *ts)
> {
> double date = date2j(ts->year, ts->month, ts->day) -
> POSTGRES_EPOCH_JDATE;
> double time = time2t(ts->hour, ts->minute, ts->second, ts->fraction);
>
> double tmp = date * SECS_PER_DAY + time;
>
> PGTimeStamp rez;
> int* ptmp = (int*)&tmp;
> int* prez = (int*)&rez;
> prez[0] = htonl(ptmp[1]);
> prez[1] = htonl(ptmp[0]);
>
> return rez;
> }
>
> I'm definitely missing something, please help.
>
> P.S. Documentation mentions about possible compatibility issues. What are
> those?
> TY!

date is 4 byte integer. look at libpqtypes datetime.c here:
http://libpqtypes.esilo.com/browse_source.html?file=datetime.c

/* PGdate members required isbc, year, mon, mday */
int
pqt_put_date(PGtypeArgs *args)
{
int dval;
PGdate *date = va_arg(args->ap, PGdate *);

PUTNULLCHK(args, date);
CHECKDATEVALS(args, date);

dval = date2j(BC2YEAR(date->isbc, date->year), date->mon+1, date->mday)
- POSTGRES_EPOCH_JDATE;

pqt_buf_putint4(args->put.out, dval);
return 4;
}

merlin