Re: [libdbi-users] UNIX Timestamp - DATE to UTC conversion
On 19-Nov-08, at 8:42 AM, Ken Ramsay wrote: > > Thanks guys, > > MySQL defines the field as a DATETIME , so the return type is time_t. No; you are using an expression, not the column, so the result type is indeed BIGINT, as Markus surmised (his explanation was correct, as far as I can determine). > As > far as I could find out, there is no helper funtion to convert this > into > secs from 1 Jan 1970 , You indeed get seconds from UNIX epoch, as documented*, if you access the returned result field using 'dbi_result_get_longlong_idx()'. (Or if you want to retrieve by name, I suggest using an alias for maintainability/readability's sake.) --Toby * - http://dev.mysql.com/doc/refman/5.0/en/date-and-time- functions.html#function_unix-timestamp > so I got around it by using the difftime() call > and subtracting the returned value from 1/1/1970. difftime() > returns the > difference in seconds. > > Not sure it is the most elegant way to do it but it works ...;-} > > Cheers > > > > -Original Message- > From: Markus Hoenicka [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 18, 2008 5:38 PM > To: Ken Ramsay > Cc: libdbi-users@lists.sourceforge.net; [EMAIL PROTECTED] > Subject: [libdbi-users] UNIX Timestamp - DATE to UTC conversion > > Ken Ramsay writes: >> time = dbi_result_get_uint(result, >> "UNIX_TIMESTAMP(HIST_Timestamp)"); > > Without actually trying the code: one rough guess is that MySQL > does not > return the timestamp as an uint value but as something longer. Did you > try using dbi_result_get_field_type() and > dbi_result_get_field_attrib() to find out which type is returned? > > regards, > Markus > > -- > Markus Hoenicka > [EMAIL PROTECTED] > (Spam-protected email: replace the quadrupeds with "mhoenicka") > http://www.mhoenicka.de > > > This email was sent to you by Thomson Reuters, the global news and > information company. > Any views expressed in this message are those of the individual > sender, except where the sender specifically states them to be the > views of Thomson Reuters. > > > > -- > --- > This SF.Net email is sponsored by the Moblin Your Move Developer's > challenge > Build the coolest Linux based applications with Moblin SDK & win > great prizes > Grand prize is a trip for two to an Open Source event anywhere in > the world > http://moblin-contest.org/redirect.php?banner_id=100&url=/ > ___ > libdbi-users mailing list > libdbi-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/libdbi-users - This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] UNIX Timestamp - DATE to UTC conversion
Quoting Ken Ramsay <[EMAIL PROTECTED]>: > > Thanks guys, > > MySQL defines the field as a DATETIME , so the return type is time_t. As > far as I could find out, there is no helper funtion to convert this into > secs from 1 Jan 1970 , so I got around it by using the difftime() call > and subtracting the returned value from 1/1/1970. difftime() returns the > difference in seconds. > > Not sure it is the most elegant way to do it but it works ...;-} > Ah, I see. libdbi provides a dbi_result_get_datetime() function to retrieve these time_t values. Unless you have to do it in SQL, you could then post-process this value using gmtime() or any other suitable Unix time function. regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with "mhoenicka") http://www.mhoenicka.de - This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] UNIX Timestamp - DATE to UTC conversion
Thanks guys, MySQL defines the field as a DATETIME , so the return type is time_t. As far as I could find out, there is no helper funtion to convert this into secs from 1 Jan 1970 , so I got around it by using the difftime() call and subtracting the returned value from 1/1/1970. difftime() returns the difference in seconds. Not sure it is the most elegant way to do it but it works ...;-} Cheers -Original Message- From: Markus Hoenicka [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2008 5:38 PM To: Ken Ramsay Cc: libdbi-users@lists.sourceforge.net; [EMAIL PROTECTED] Subject: [libdbi-users] UNIX Timestamp - DATE to UTC conversion Ken Ramsay writes: > time = dbi_result_get_uint(result, > "UNIX_TIMESTAMP(HIST_Timestamp)"); Without actually trying the code: one rough guess is that MySQL does not return the timestamp as an uint value but as something longer. Did you try using dbi_result_get_field_type() and dbi_result_get_field_attrib() to find out which type is returned? regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with "mhoenicka") http://www.mhoenicka.de This email was sent to you by Thomson Reuters, the global news and information company. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of Thomson Reuters. - This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] UNIX Timestamp - DATE to UTC conversion
Ken
Shouldn't you have a time_t (unsigned long) for the
time return type (and a dbi_result_get_long or whatever
to extarct it)?
On Mon, 17 Nov 2008, Ken Ramsay wrote:
> Guys
>
> I am trying to get info from a mysql database that stores the date in a
> string format and return UTC format as well as a associated value. I can
> use the following select() statement OK to take care of that :
> mysql> select UNIX_TIMESTAMP(), fields> from ;
>
> This gives all the values in the table and the UTC timeformat
>
> So, next I built libdbi was able to run a simple db connection to test
> it all worked. I then moved on to try the above and got my values OK but
> I get '0''s for the UTC TIMESTAMP. Anyone any ideas why this doesn't
> work?
>
> Cheers
>
> Here's my src which compiles and runs OK but gives the partial output
> below.. Any ideas ( I have played with the printf statement already ;-)
> ?
>
>
> #include
> #include
>
> int main() {
> dbi_conn conn;
> dbi_result result;
> unsigned int updateLatency;
> unsigned int time;
> const char *name;
>
> dbi_initialize(NULL);
> conn = dbi_conn_new("mysql");
>
> dbi_conn_set_option(conn, "host", "localhost");
> dbi_conn_set_option(conn, "username", "sqluser");
> dbi_conn_set_option(conn, "password", "");
> dbi_conn_set_option(conn, "dbname", "test");
> dbi_conn_set_option(conn, "encoding", "UTF-8");
>
> if (dbi_conn_connect(conn) < 0) {
> printf("Could not connect. Please check the option settings\n");
> }
> else {
> result = dbi_conn_queryf(conn, "select
> UNIX_TIMESTAMP(HIST_Timestamp),HIST_Node,updateLatency from TABLE");
>
> if (result) {
> while (dbi_result_next_row(result)) {
> name = dbi_result_get_string(result, "HIST_Node");
> time = dbi_result_get_uint(result,
> "UNIX_TIMESTAMP(HIST_Timestamp)");
> updateLatency = dbi_result_get_uint(result, "updateLatency");
> printf("%s %d %d\n",name,updateLatency,time);
> }
> dbi_result_free(result);
> }
> dbi_conn_close(conn);
> }
>
> dbi_shutdown();
>
> return 0;
> }
>
> Sample Output:
> chimds05 34 0
> chimds05 12 0
> chimds05 13 0
> chimds05 20 0
> chimds05 10 0
> chimds05 13 0
>
>
>
> This email was sent to you by Thomson Reuters, the global news and
> information company.
> Any views expressed in this message are those of the individual sender,
> except where the sender specifically states them to be the views of Thomson
> Reuters.
>
>
--
J.J. Green, Dept. Applied Mathematics, Hicks Bld.,
University of Sheffield, UK. +44 (0114) 222 3742
http://sview01.wiredworkplace.net/pub/jjg
-
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
___
libdbi-users mailing list
libdbi-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/libdbi-users
