Re: binary representation of datatypes

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