[libdbi-devel] libDBI (or ) pgsql driver: question on "timestamp"

Discussion:

Holger Hetterich

2011-04-25 21:47:32 UTC

Hi,

In postgresql I store data of type "timestamp" such as:
"2011-04-17 12:58:51.074"

While the database can handle this format, and for example correctly
sorts while taking care of the milliseconds, it looks to me it's
impossible to retrieve this data via libDBI. What I found was the
get_datetime functions, which return a time_t. Any way to get the
information about the milliseconds, _or_ get a string or binary
representation of what is stored in the DB, so that I can convert it
back to a string on the client side?

Or maybe I oversee a useful function to get to this information...

Holger

Toby Thain

2011-04-25 23:50:31 UTC

Permalink

Post by Holger Hetterich
Hi,
"2011-04-17 12:58:51.074"
While the database can handle this format, and for example correctly
sorts while taking care of the milliseconds, it looks to me it's
impossible to retrieve this data via libDBI. What I found was the
get_datetime functions, which return a time_t. Any way to get the
information about the milliseconds, _or_ get a string or binary
representation of what is stored in the DB, so that I can convert it
back to a string on the client side?

Indeed, a function to retrieve this as a string is needed. It's really
an error for the library to treat it like a time_t; I think the MySQL
DATE/TIME types come back as strings (but don't quote me; it's a long
time since I used libdbi).

--Toby

Post by Holger Hetterich
Or maybe I oversee a useful function to get to this information...
Holger
------------------------------------------------------------------------------
WhatsUp Gold - Download Free Network Management Software
The most intuitive, comprehensive, and cost-effective network
management toolset available today. Delivers lowest initial
acquisition cost and overall TCO of any competing solution.
http://p.sf.net/sfu/whatsupgold-sd
_______________________________________________
libdbi-devel mailing list
https://lists.sourceforge.net/lists/listinfo/libdbi-devel

Holger Hetterich

2011-04-26 19:02:33 UTC

Permalink

Post by Toby Thain

libDBI is so cool, I think it's worth to extend libDBI with functions to
return DB data as a string representation in general.

I have time pressure on my current project, and getting the milliseconds
is not crucial, so I can forward now with a time_t. After that, I would
like to look deeper at libDBI and create patches to return any value as
a string representation. Would that be a good idea?

Holger

Post by Toby Thain
--Toby

------------------------------------------------------------------------------
WhatsUp Gold - Download Free Network Management Software
The most intuitive, comprehensive, and cost-effective network
management toolset available today. Delivers lowest initial
acquisition cost and overall TCO of any competing solution.
http://p.sf.net/sfu/whatsupgold-sd
_______________________________________________
libdbi-devel mailing list
https://lists.sourceforge.net/lists/listinfo/libdbi-devel

Toby Thain

2011-04-26 23:46:15 UTC

Permalink

Post by Holger Hetterich

Post by Toby Thain

libDBI is so cool, I think it's worth to extend libDBI with functions to
return DB data as a string representation in general.

On this list, I've proposed something similar in the past (a
"type-coercing" interface). It is even more important for integer types,
as you see from your other posting recently on the list. (I think there
should be an additional facility to fetch an integer without knowing the
precise storage type; this facilitates database and schema independence.)

In many cases strings are already returned for data types that have no
natural C representation (DATE/TIME as I mentioned, DECIMAL, ENUM, and
so on), so I consider your specific issue here more of a bug than a
missing function (since the time_t conversion makes no sense - but I
haven't checked the code to see what's going on).

--Toby

Post by Holger Hetterich
I have time pressure on my current project, and getting the milliseconds
is not crucial, so I can forward now with a time_t. After that, I would
like to look deeper at libDBI and create patches to return any value as
a string representation. Would that be a good idea?
Holger

Post by Toby Thain
--Toby

Markus Hoenicka

2011-04-27 07:20:14 UTC

Permalink

Hi,

seems I've missed the start of this thread, so please excuse me for
jumping in late. Please have a look at the current CVS revision. We do
have the following functions here:

/* get_as* functions */
long long dbi_result_get_as_longlong(dbi_result Result, const char
*fieldname);
long long dbi_result_get_as_longlong_idx(dbi_result Result, unsigned
int fieldidx);
char *dbi_result_get_as_string_copy(dbi_result Result, const char *fieldname);
char *dbi_result_get_as_string_copy_idx(dbi_result Result, unsigned
int fieldidx);

They do pretty much of what was asked for. I can't recall the details
atm, but the first two functions try hard to return an integer
representation of whatever was stored in the column, and the remaining
two functions return a string representation. You might give that a
try and see if you can get your TIMESTAMP data in a useful form. If
not, feel free to submit specific patches that implement the missing
functionality.

Once again, I have to apologize that I won't be able to take care of
the release process for another month or two due to time constraints.

regards,
Markus

--
Markus Hoenicka
http://www.mhoenicka.de
AQ score 38

Holger Hetterich

2011-05-02 14:05:48 UTC

Permalink

Post by Markus Hoenicka

Hi,
seems I've missed the start of this thread, so please excuse me for
jumping in late. Please have a look at the current CVS revision. We do
/* get_as* functions */
long long dbi_result_get_as_longlong(dbi_result Result, const char
*fieldname);
long long dbi_result_get_as_longlong_idx(dbi_result Result, unsigned
int fieldidx);
char *dbi_result_get_as_string_copy(dbi_result Result, const char *fieldname);
char *dbi_result_get_as_string_copy_idx(dbi_result Result, unsigned
int fieldidx);
They do pretty much of what was asked for. I can't recall the details
atm, but the first two functions try hard to return an integer
representation of whatever was stored in the column, and the remaining
two functions return a string representation. You might give that a
try and see if you can get your TIMESTAMP data in a useful form. If
not, feel free to submit specific patches that implement the missing
functionality.
Once again, I have to apologize that I won't be able to take care of
the release process for another month or two due to time constraints.

Thank you so much, Markus.

I have no time to check the svn code at the moment, but I'll sure do
after the pressure on my project is gone. And yes, it looks like these
are exactly the functions I require. I can, after my milestone, test
these functions for you. Anything else I can help to bring a new libDBI
release forward?

Holger

Post by Markus Hoenicka
regards,
Markus