Date: Tue, 27 Dec 2005 20:53:51 +0200
From: Volkan YAZICI <yazicivo@ttnet.net.tr>
To: "Mavinakuli, Prasanna (STSD)" <prasanna.b-m@hp.com>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: Convertion of date/time binary format to text format
Message-ID: <20051227185351.GA546@alamut>
References: 
 <E84C4F56CA80174D8834825116CF247F01BFA23D@bgeexc05.asiapacific.cpqcorp.net>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="KsGdsel6WgEHnImy"
Content-Disposition: inline
In-Reply-To: 
 <E84C4F56CA80174D8834825116CF247F01BFA23D@bgeexc05.asiapacific.cpqcorp.net>
User-Agent: Mutt/1.4.2.1i


--KsGdsel6WgEHnImy
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Dec 23 12:25, Mavinakuli, Prasanna (STSD) wrote:
> So how can I get the ASCII format/Text format from that binary
> format.
> 
> Please get me the steps to convert timestamp column which is of
> binary mode to TEXT/ASCII mode

I attached an example program which answers your question. Hope
this helps.

(I skipped byte order conversions in my example for simplicity. Don't
forget 'em in your own program for portability.)


Regards.

-- 
"We are the middle children of history, raised by television to believe
that someday we'll be millionaires and movie stars and rock stars, but
we won't. And we're just learning this fact," Tyler said. "So don't
fuck with us."

--KsGdsel6WgEHnImy
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="libpq_binary_date.c"

#include <stdio.h>
#include <stdlib.h>

#include "libpq-fe.h"

void
SmartQuit(PGconn *conn)
{
	int	stat = 0;
	
	if (PQstatus(conn) != CONNECTION_OK)
	{
		fprintf(stderr, "C> %s", PQerrorMessage(conn));
		stat = 1;
	}
	
	PQfinish(conn);
	exit(stat);
}

int
main(void)
{
	PGconn		*conn;
	PGresult	*res;
	char		*vcol0, *vcol1;
	int			 lcol0, lcol1;

	conn = PQconnectdb("dbname=test");
	if (PQstatus(conn) != CONNECTION_OK)
		SmartQuit(conn);
	
	PQclear(PQexec(conn,
				   "BEGIN;"
				   "CREATE TEMP TABLE t_tmp (col0 text, col1 date);"
				   "INSERT INTO t_tmp VALUES ('example text', now()::date)"));
	
	/* Give attention to ::text casting for date column. */
	res = PQexecParams(conn, "SELECT col0, col1::text FROM t_tmp", 0, NULL, NULL, NULL, NULL, 1);
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, "R> %s", PQresultErrorMessage(res));
		PQclear(res);
		SmartQuit(conn);
	}

	vcol0 = PQgetvalue(res, 0, 0);
	lcol0 = PQgetlength(res, 0, 0);
	vcol1 = PQgetvalue(res, 0, 1);
	lcol1 = PQgetlength(res, 0, 1);
	
	printf("(%d bytes) '%s'\n", lcol0, vcol0);
	printf("(%d bytes) '%s'\n", lcol1, vcol1);

	/*
	 * (12 bytes) 'example text'
	 * (10 bytes) '2005-12-27'
	 */

	PQclear(res);
	PQclear(PQexec(conn, "ROLLBACK"));
	PQfinish(conn);
	
	return 0;
}

--KsGdsel6WgEHnImy--