Re: [libdbi-users] UNIX Timestamp - DATE to UTC conversion

2008-11-19 Thread Toby Thain

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=100url=/
 ___
 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=100url=/
___
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

2008-11-18 Thread J.J.Green
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(Timestamp string),data
 fields from table;

 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 stdio.h
 #include /usr/local/include/dbi/dbi.h

 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=100url=/
___
libdbi-users mailing list
libdbi-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/libdbi-users