Michael Meskes wrote: > On Tue, Oct 21, 2008 at 10:21:38AM +0200, Matthieu Imbert wrote: >> I would like to be able to get results from SQL commands directly in a >> binary format, instead of a textual one. Actually, I want to be able to >> get timestamps with their full precision (microsecond). > > Are you sure you cannot get those in textual mode? If so I wonder why I got > some numbers in a quick test: > > ... > [NO_PID]: ecpg_execute on line 37: query: select * from date_test where d = > $1 ; with 1 parameter(s) on connection regress1 > [NO_PID]: sqlca: code: 0, state: 00000 > [NO_PID]: ecpg_execute on line 37: using PQexecParams > [NO_PID]: sqlca: code: 0, state: 00000 > [NO_PID]: free_params on line 37: parameter 1 = 1966-01-17 > [NO_PID]: sqlca: code: 0, state: 00000 > [NO_PID]: ecpg_execute on line 37: correctly got 1 tuples with 2 fields > [NO_PID]: sqlca: code: 0, state: 00000 > [NO_PID]: ecpg_get_data on line 37: RESULT: 1966-01-17 offset: -1; array: yes > [NO_PID]: sqlca: code: 0, state: 00000 > [NO_PID]: ecpg_get_data on line 37: RESULT: 2000-07-12 17:34:29.140787 > offset: -1; array: yes > [NO_PID]: sqlca: code: 0, state: 00000 > ... > > What do I miss here? > > Michael
Yes microseconds are available in textual mode but i do want to use binary mode. Let me explain why: - my data will be time series. So typical requests will return lots of timestamped data (mainly floats or int). - after extraction i need to have all timestamps stored in format convenient for calculations. I can accommodate different formats (for example: number of microseconds since 2000-01-01, or a structure similar to pg_tm (but with microsecond precision), or a time-format similar to one defined in rfc1305), but definitely storing timestamps as text is a no go for me. so i have two choices: scenario 1 - parse the textual representation of all results of requests to the database and convert textual timestamps to a binary format that i choose among those ones (number of microseconds since 2000-01-01, or a structure similar to pg_tm (but with microsecond precision), or a time-format similar to one defined in rfc1305, or something else) or scenario 2 - directly use pgsql binary timestamp format. I think the latter is far more efficient. I'm new to postgresql, but from what i understand, here are the conversions involved in both scenarios (hopping that my ascii art won't be garbled by your mail clients ;-) : scenario 1: .---------. .----------. .---------. .----------. .--------------. .----------. .---------. |timestamp| |pgsql | |timestamp| |pgsql | |timestamp | |my | |my | |storage |->|internal |->|storage |->|network |->|as |->|timestamp |->|timestamp| |in | |to | |in | |to | |textual | |conversion| |format | |database | |network | |network | |textual | |representation| |routines | | | |backend | |conversion| | | |conversion| | | | | | | | | |function | | | |function | | | | | | | '---------' '----------' '---------' '----------' '--------------' '----------' '---------' scenario 2: .---------. .----------. .---------. .----------. .---------. |timestamp| |pgsql | |timestamp| |pgsql | |timestamp| |storage |->|internal |->|storage |->|network |->|official | |in | |to | |in | |to | |format | |database | |network | |network | |offical | | | |backend | |conversion| | | |conversion| | | | | |function | | | |function | | | '---------' '----------' '---------' '----------' '---------' if i'm correct, it seems obvious that the second scenario is more efficient (and less ugly). In scenario 2, when talking about timestamp 'official' format, i mean timestamp expressed as number of microseconds since 2000-01-01. But of course, it only deserves this name 'official' if it is guaranteed to stay the same across postgresql versions and platforms -- Matthieu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
